点击蓝色“有关SQL”关注我哟
加个“星标”,天天与10000人一起快乐成长
常有朋友问,数据分析师的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() :
这两者的区别,在于他们对位于相同排名之后的名次,是接着相同排名的连续数(Dense_Rank)还是相隔N个相同记录个数之后的连续数(Rank)。
所以Dense_Rank出来的结果都是连续数字,而非Dense_Rank出来的结果有可能有跳格数。
c. 除了有用法上的区别外,顺带说说分页的实现:
第一种,我们平常用 Row_Number() 加 Top (N) 来实现 :
selecttop(100)*from(selectOrderId,OrderMonth,OrderAmount,Row_Number()Over(OrderByOrderAmountDESC)ASAmt_OrderfromFctSales)tmpWhereAmt_Orderbetween2000and3000
第二种,SQL Server 之后的新功能:
SelectOrderId,OrderMonth,OrderAmountFromFctSalesOrderbyOrderAmountDescOffSet2000ROWSFetchNext100ROWSOnly
按照量的大小倒序排,取第 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 子句。
function_name(<arguments>)Over([<windowpartitionclause>][<windowOrderclause>[<windowframeclause>]])
Over::
Over([<PARTITIONBYclause>][<ORDERBYclause>][<ROWorRANGEclause>])
::窗口中的窗口
ROWS|RANGEBETWEENUNBOUNDEDPRECDEDING|<N>PRECEDING|<N>FOLLOWING|CURRENTROWANDUNBOUNDEDFOLLOWING|<N>PRECEDING|<N>FOLLOWING|CURRENTROW
举一个例子:
selectcustid,ordermonth,ordervolume,sum(ordervolume)over(partitionbycustidorderbyordermonthascrowsbetweenunboundedprecedingandcurrentrow)ascumulatedVolumefromFctSales
统计了截止到目前为止,每一天的累计总量。
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 面试题,刷掉 494 名候选人
我在面试数据库工程师候选人时,常问的一些题
零基础 SQL 数据库小白,从入门到精通的学习路线与书单