数据分析师的SQL功底该学到什么程度?

运维 数据库运维 数据分析
常有朋友问,数据分析师的SQL功底该学到什么程度。今天就先谈谈 T-SQL 中的 Window Function.

[[416110]]

本文转载自微信公众号「有关SQL」,作者Lenis。转载本文请联系有关SQL公众号。

常有朋友问,数据分析师的SQL功底该学到什么程度。今天就先谈谈 T-SQL 中的 Window Function.

Window Function 包含了 4 个大类。分别是:

  • 1 - Rank Function
  • 2 - Aggregate Function
  • 3 - Offset Function
  • 4 - Distribution Function.

1 - Rank Function 平常用到最多

  • 1.1 Rank() Over()
  • 1.2 Row_Number() Over()
  • 1.3 Dense_Rank() Over()
  • 1.4 NTILE(N) Over()

这四个函数,要注意的地方有两点:

a. Rank() Over() 与 Row_Number() Over() :

两者唯一的区别,就在于Row_Number() Over() 真正实现了相同条件的两条或者多条记录是用唯一值来区别的

b. Rank() Over() 与 Dense_Rank() Over() :

这两者的区别,在于他们对位于相同排名之后的名次,是接着相同排名的连续数(Rank) 还是相隔 N 个相同记录个数之后的连续数(Dense_Rank)。

所以 Rank 出来的结果都是连续数字,而 Dense_Rank 出来的结果有可能有跳格数。

c. 除了有用法上的区别外,顺带说说分页的实现:

第一种,我们平常用 Row_Number() 加 Top (N) 来实现 :

  1. select top(100) * 
  2.  
  3.      from ( select  
  4.  
  5.                          OrderId 
  6.  
  7.                      ,    OrderMonth 
  8.  
  9.                       ,    OrderAmount 
  10.  
  11.                       ,    Row_Number() Over(                                    
  12.  
  13.                                  OrderBy OrderAmount DESC
  14.  
  15.                              AS Amt_Order 
  16.  
  17.                   from FctSales) tmp 
  18.  
  19.       Where Amt_Order between 2000 and 3000 

第二种,SQL Server 2012 之后的新功能:

  1. Select    OrderId 
  2.  
  3.             ,    OrderMonth 
  4.  
  5.             ,    OrderAmount 
  6.  
  7.    From FctSales 
  8.  
  9.   Order by OrderAmount Desc 
  10.  
  11.  
  12.  
  13.   OffSet 2000 ROWS 
  14.  
  15.   Fetch Next 100 ROWS Only  

按照量的大小倒序排,取第 2000 条后的记录中前 100 条。

2 - Aggregate Function. 聚合数据

  • 2.1 - Sum() Over()
  • 2.2 - Count() Over()
  • 2.3 - AVG() Over()
  • 2.4 - MIN() Over()
  • 2.5 - MAX() Over()

在使用 Aggregation 函数的时候,唯一要注意的地方就是 Order 子句。

  1. function_name(<arguments>) Over( 
  2.  
  3.     [ <window partition clause>] 
  4.  
  5.     [ <window Order clause> 
  6.  
  7.             [ <window frame clause>] 
  8.  
  9.     ]) 

Over::

  1. Over( 
  2.  
  3.             [    <PARTITION BY clause>    ] 
  4.  
  5.             [    <ORDER BY clause>          ] 
  6.  
  7.             [    <ROW or RANGE clause>  ] 
  8.  
  9.     ) 

::窗口中的窗口

  1. ROWS | RANGE 
  2.  
  3.     BETWEEN  
  4.  
  5.         UNBOUNDED PRECDEDING  | 
  6.  
  7.          <N>  PRECEDING     | 
  8.  
  9.           <N>  FOLLOWING   | 
  10.  
  11.             CURRENT ROW 
  12.  
  13.      AND 
  14.  
  15.             UNBOUNDED FOLLOWING  | 
  16.  
  17.              <N> PRECEDING  | 
  18.  
  19.              <N> FOLLOWING  | 
  20.  
  21.              CURRENT ROW 

举一个例子:

  1. select custid 
  2.  
  3.    , ordermonth 
  4.  
  5.    , ordervolume 
  6.  
  7.     , sum(ordervolume)  
  8.  
  9.           over(    partition by custid 
  10.  
  11.                 order by ordermonth asc 
  12.  
  13.                 rows between     
  14.  
  15.                          unbounded preceding 
  16.  
  17.                 and    current row) 
  18.  
  19.         as cumulatedVolume 
  20.  
  21.  from FctSales 

统计了截止到目前为止,每一天的累计总量。

3 - Offset Function:定位记录

  • 3.1 Lead()
  • 3.2 LAG()
  • 3.3 First_Value()
  • 3.4 Last_Value()
  • 3.5 Nth_Value()

这一类比较好理解,根据当前的记录,获取前后 N 条数据。

4 - Distribution Function: 分布函数

  • 4.1- PERCENT_RANK()
  • 4.2 - CUME_DIST()
  • 4.3 - PERCENT_COUNT()-
  • 4.4 - PERCENT_DISC()

 

这一类应用,到目前为止,未用过。适用于财会类的统计。

 

责任编辑:武晓燕 来源: 有关SQL
相关推荐

2017-04-11 09:08:02

数据分析Python

2018-07-19 19:50:48

大数据数据分析师网站分析

2015-08-18 13:26:05

数据分析

2016-10-21 14:41:22

数据分析师大数据

2012-08-08 09:00:29

数据分析师

2012-08-07 17:32:25

数据分析师

2019-09-16 11:37:07

大数据数据分析工具

2013-07-29 15:58:28

大数据数据分析

2017-02-13 19:25:24

2015-04-03 11:19:21

大数据大数据分析师

2020-05-12 10:44:19

数据分析师薪资数据

2023-07-08 23:05:01

数据分析运营

2021-03-26 07:37:34

数据分析工具技能

2018-01-28 15:40:20

数据分析师职业规划数据分析

2015-08-17 09:39:40

大数据

2016-01-26 10:33:23

大数据分析工具数据分析师

2011-05-30 09:55:14

数据分析师

2016-02-23 09:59:14

数据分析数据分析师数据管理

2020-07-20 07:00:00

数据分析师数据分析大数据

2015-08-06 14:02:31

数据分析
点赞
收藏

51CTO技术栈公众号