云数据库 RDS 版
解析SQL Server 2012常用的分析函数
更新时间：
访问次数：1530
分析函数CUME_DIST
微软的定义：
计算某个值在SQL Server 2012中的一组值内的累积分布。CUME_DIST计算某指定值在一组值中的相对位置。对于行r，假定采用升序，r的CUME_DIST是值低于或等于r的值的行数除以在分区或查询结果集中求出的行数。
函数解析：
执行如下代码，构造一组数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <ol class="linenums"> <li class="L0"><span class="pln">DECLARE</span></li> <li class="L1"><span class="pln"> </span><span class="lit">@analytic</span><span class="pln"> TABLE</span><span class="pun">(</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">)</span><span class="pln"> </span><span class="pun">,</span></li> <li class="L3"><span class="pln"> dept varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">),</span></li> <li class="L4"><span class="pln"> salary money</span></li> <li class="L5"><span class="pln"> </span><span class="pun">)</span></li> <li class="L6"><span class="pln">INSERT INTO </span><span class="lit">@analytic</span><span class="pln"> </span></li> <li class="L7"><span class="pln"> VALUES</span></li> <li class="L8"><span class="pun">--</span><span class="pln">bd</span></li> <li class="L9"><span class="pun">(</span><span class="str">'andy01'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">),</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy02'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy03'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy04'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy05'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">),</span></li> <li class="L4"><span class="pun">--</span><span class="pln">ca</span></li> <li class="L5"><span class="pun">(</span><span class="str">'andy06'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">20000</span><span class="pun">),</span></li> <li class="L6"><span class="pun">(</span><span class="str">'andy07'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">),</span></li> <li class="L7"><span class="pun">(</span><span class="str">'andy08'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">),</span></li> <li class="L8"><span class="pun">(</span><span class="str">'andy09'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">),</span></li> <li class="L9"><span class="pun">(</span><span class="str">'andy10'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">),</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy11'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy12'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy13'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy14'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy15'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">)</span></li> <li class="L5"></li> <li class="L6"><span class="pln">SELECT </span></li> <li class="L7"><span class="pln"> dept</span><span class="pun">,</span><span class="pln">name </span><span class="pun">,</span><span class="pln">salary</span><span class="pun">,</span></li> <li class="L8"><span class="pln"> CUME_DIST</span><span class="pun">()</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">PARTITION BY dept ORDER BY salary</span><span class="pun">)</span><span class="pln"> AS cume_dist_ </span></li> <li class="L9"><span class="pln">FROM </span><span class="lit">@analytic</span></li> <li class="L0"><span class="pln">ORDER BY dept</span><span class="pun">,</span><span class="pln">salary DESC</span></li></ol> |
返回结果如下：
示例解析：
按照dept分组，根据salary逻辑排序，针对每一个分组里的每一个值，计算在该分组下等于或者小于自己的salary的分布的百分比。举个例子，bd部门的andy02，salary为12000，那么等于或者小于这个12000的有4条，总共5条记录，因此那么CUME_DIST()=4/5=0.8。 同理，其它也是这样计算。
分析函数LAST_VALUE
微软的定义：
返回SQL Server 2012中有序值集中的最后一个值。
函数解析：
执行如下代码，构造一组数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <ol class="linenums"> <li class="L0"><span class="pln">DECLARE</span></li> <li class="L1"><span class="pln"> </span><span class="lit">@analytic</span><span class="pln"> TABLE</span><span class="pun">(</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">)</span><span class="pln"> </span><span class="pun">,</span></li> <li class="L3"><span class="pln"> dept varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">),</span></li> <li class="L4"><span class="pln"> salary money </span><span class="pun">,</span></li> <li class="L5"><span class="pln"> hiredate date</span></li> <li class="L6"><span class="pln"> </span><span class="pun">)</span></li> <li class="L7"><span class="pln">INSERT INTO </span><span class="lit">@analytic</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> VALUES</span></li> <li class="L9"><span class="pun">--</span><span class="pln">bd</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy01'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2002-01-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy02'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy03'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-02-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy04'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2005-05-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy05'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">--</span><span class="pln">ca</span></li> <li class="L6"><span class="pun">(</span><span class="str">'andy06'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">20000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L7"><span class="pun">(</span><span class="str">'andy07'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-02-09'</span><span class="pun">),</span></li> <li class="L8"><span class="pun">(</span><span class="str">'andy08'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-03-09'</span><span class="pun">),</span></li> <li class="L9"><span class="pun">(</span><span class="str">'andy09'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2004-01-09'</span><span class="pun">),</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy10'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy11'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2002-09-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy12'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2003-07-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy13'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-08-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy14'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-11-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">(</span><span class="str">'andy15'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">)</span></li> <li class="L6"></li> <li class="L7"><span class="pln">SELECT </span></li> <li class="L8"><span class="pln"> dept</span><span class="pun">,</span><span class="pln">name </span><span class="pun">,</span><span class="pln">salary</span><span class="pun">,</span><span class="pln">hiredate</span><span class="pun">,</span></li> <li class="L9"><span class="pln"> LAST_VALUE</span><span class="pun">(</span><span class="pln">hiredate</span><span class="pun">)</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">PARTITION BY dept ORDER BY salary</span><span class="pun">)</span><span class="pln"> AS last_value_ </span></li> <li class="L0"><span class="pln">FROM </span><span class="lit">@analytic</span></li></ol> |
返回结果如下：
示例解析：
按照OVER子句中ORDER BY根据salary排序，取salary最后行的hiredate值作为最后的LAST VALUE，重点在于当salary有相同的值时，需要取根据salary排序后的最后一条记录作为其他的LAST VALUE。
分析函数FIRST_VALUE
微软的定义：
返回SQL Server 2012中有序值集中的第一个值。
函数解析：
从微软的定义来看，FIRST_VALUE似乎跟LAST_VALUE是相反的含义，但实际并非如此。
执行如下代码，构造一组数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <ol class="linenums"> <li class="L0"><span class="pln">DECLARE</span></li> <li class="L1"><span class="pln"> </span><span class="lit">@analytic</span><span class="pln"> TABLE</span><span class="pun">(</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">)</span><span class="pln"> </span><span class="pun">,</span></li> <li class="L3"><span class="pln"> dept varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">),</span></li> <li class="L4"><span class="pln"> salary money </span><span class="pun">,</span></li> <li class="L5"><span class="pln"> hiredate date</span></li> <li class="L6"><span class="pln"> </span><span class="pun">)</span></li> <li class="L7"><span class="pln">INSERT INTO </span><span class="lit">@analytic</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> VALUES</span></li> <li class="L9"><span class="pun">--</span><span class="pln">bd</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy01'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2002-01-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy02'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy03'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-02-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy04'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2005-05-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy05'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">--</span><span class="pln">ca</span></li> <li class="L6"><span class="pun">(</span><span class="str">'andy06'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">20000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L7"><span class="pun">(</span><span class="str">'andy07'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-02-09'</span><span class="pun">),</span></li> <li class="L8"><span class="pun">(</span><span class="str">'andy08'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-03-09'</span><span class="pun">),</span></li> <li class="L9"><span class="pun">(</span><span class="str">'andy09'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2004-01-09'</span><span class="pun">),</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy10'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy11'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2002-09-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy12'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2003-07-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy13'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-08-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy14'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-11-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">(</span><span class="str">'andy15'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">)</span></li> <li class="L6"></li> <li class="L7"><span class="pln">SELECT </span></li> <li class="L8"><span class="pln"> dept</span><span class="pun">,</span><span class="pln">name </span><span class="pun">,</span><span class="pln">salary</span><span class="pun">,</span><span class="pln">hiredate</span><span class="pun">,</span></li> <li class="L9"><span class="pln"> FIRST_VALUE</span><span class="pun">(</span><span class="pln">name</span><span class="pun">)</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">PARTITION BY dept ORDER BY salary</span><span class="pun">)</span><span class="pln"> AS first_value_ </span></li> <li class="L0"><span class="pln">FROM </span><span class="lit">@analytic</span></li></ol> |
返回结果如下：
示例分析：
显然，这个与LAST_VALUE并不是相反的含义。OVER子句根据ORDER BY来排序，按dept分组来确定这个分组的第一个值，而不是根据salary的值来确定的，所以与LAST_VALUE是不一样的。将FIRST_VALUE(name)修改为FIRST_VALUE(hiredate)后，对比看得更清楚，这个很有蒙蔽性。
分析函数LEAD
微软的定义：
访问相同结果集的后续行中的数据，而不使用SQL Server 2012中的自联接。LEAD以当前行之后的给定物理偏移量来提供对行的访问。在SELECT语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。
函数解析：
执行如下代码，构造一组数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | <ol class="linenums"> <li class="L0"><span class="pln">DECLARE</span></li> <li class="L1"><span class="pln"> </span><span class="lit">@analytic</span><span class="pln"> TABLE</span><span class="pun">(</span><span class="pln"> </span></li> <li class="L2"><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">)</span><span class="pln"> </span><span class="pun">,</span></li> <li class="L3"><span class="pln"> dept varchar</span><span class="pun">(</span><span class="lit">35</span><span class="pun">),</span></li> <li class="L4"><span class="pln"> salary money </span><span class="pun">,</span></li> <li class="L5"><span class="pln"> hiredate date</span></li> <li class="L6"><span class="pln"> </span><span class="pun">)</span></li> <li class="L7"><span class="pln">INSERT INTO </span><span class="lit">@analytic</span><span class="pln"> </span></li> <li class="L8"><span class="pln"> VALUES</span></li> <li class="L9"><span class="pun">--</span><span class="pln">bd</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy01'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2002-01-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy02'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy03'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-02-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy04'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2005-05-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy05'</span><span class="pun">,</span><span class="str">'bd'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">--</span><span class="pln">ca</span></li> <li class="L6"><span class="pun">(</span><span class="str">'andy06'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">20000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">),</span></li> <li class="L7"><span class="pun">(</span><span class="str">'andy07'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-02-09'</span><span class="pun">),</span></li> <li class="L8"><span class="pun">(</span><span class="str">'andy08'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">18000</span><span class="pun">,</span><span class="str">'2005-03-09'</span><span class="pun">),</span></li> <li class="L9"><span class="pun">(</span><span class="str">'andy09'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">15000</span><span class="pun">,</span><span class="str">'2004-01-09'</span><span class="pun">),</span></li> <li class="L0"><span class="pun">(</span><span class="str">'andy10'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2003-06-09'</span><span class="pun">),</span></li> <li class="L1"><span class="pun">(</span><span class="str">'andy11'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">12000</span><span class="pun">,</span><span class="str">'2002-09-09'</span><span class="pun">),</span></li> <li class="L2"><span class="pun">(</span><span class="str">'andy12'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">10000</span><span class="pun">,</span><span class="str">'2003-07-09'</span><span class="pun">),</span></li> <li class="L3"><span class="pun">(</span><span class="str">'andy13'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-08-09'</span><span class="pun">),</span></li> <li class="L4"><span class="pun">(</span><span class="str">'andy14'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-11-09'</span><span class="pun">),</span></li> <li class="L5"><span class="pun">(</span><span class="str">'andy15'</span><span class="pun">,</span><span class="str">'ca'</span><span class="pun">,</span><span class="lit">8000</span><span class="pun">,</span><span class="str">'2003-01-09'</span><span class="pun">)</span></li> <li class="L6"></li> <li class="L7"><span class="pln">SELECT </span></li> <li class="L8"><span class="pln"> dept</span><span class="pun">,</span><span class="pln">name</span><span class="pun">,</span><span class="pln">hiredate</span><span class="pun">,</span><span class="pln">salary</span><span class="pun">,</span></li> <li class="L9"><span class="pln"> LEAD</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">0</span><span class="pun">)</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">PARTITION BY dept ORDER BY salary</span><span class="pun">)</span><span class="pln"> AS lead_</span><span class="pun">,</span></li> <li class="L0"><span class="pln"> </span><span class="pun">(</span><span class="pln">LEAD</span><span class="pun">(</span><span class="pln">salary</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">0</span><span class="pun">)</span><span class="pln"> OVER</span><span class="pun">(</span><span class="pln">PARTITION BY dept ORDER BY salary</span><span class="pun">)-</span><span class="pln">salary</span><span class="pun">)</span><span class="pln"> AS diff_salary </span></li> <li class="L1"><span class="pln">FROM </span><span class="lit">@analytic</span></li></ol> |
返回结果如下：
示例分析：
按照dept分区，根据salary排序，比较当前记录和后一条记录（偏移量为1）的salary值的差值，这个非常实用。