开窗函数_3


开窗函数_再训练_3
稍后贴出运行结果便于观察对比功效,后期再学学分析函数

CUME_DIST(t-sql)
FIRST_VALUE(t-sql)、LAST_VALUE(t-sql)
PERCENTILE_CONT(t-sql)、PERCENTILE_DISC(t-sql)、PERCENT_RANK(t-sql)
LEAD()、LAGE(t-sql)

1/** 创建表 */

create table test_orer_partition_by
(
    prov_name varchar(20),
    city_name varchar(50),
    val_cnt int
) ;
2/** 插入测试数据 */
insert into test_orer_partition_by values ('GS', 'LZ', 234) ,
     ('GS', 'BY', 34) ,
     ('GS', 'DX', 4) ,
    ('GS', 'LN', 67) ,
     ('GS', 'GN', 67) ,
    ('GS', 'QY', 67) ,
     ('SX', 'XA', 34) ,
     ('SX', 'YA', 85) ,
    ('SX', 'BJ', 254) ,
    ('SX', 'HZ', 99) ,
     ('SX', 'XY', 100),
    ('SX', 'SL', 23) ;

SELECT * FROM test_orer_partition_by

-----------------------------------------------------
3、排序(row_number()、rank()、dense_rank(),NTILE(n)  )
3.1 /** row_number() over(partition by prov_name ORDER BY val_cnt) */
SELECT prov_name,city_name,val_cnt,
              row_number() over(order by val_cnt) as RowID                ------>未分区,排序

FROM test_orer_partition_by
-----------------------------------------------
SELECT prov_name,city_name,val_cnt,
              row_number() over(partition by prov_name order by val_cnt ) as RowID    ------>先分区,排序

FROM test_orer_partition_by
----------------------------------------------
3.2 /** rank() over(partition by prov_name ORDER BY val_cnt) */
SELECT prov_name,city_name,val_cnt,
              rank() over(partition by prov_name order by val_cnt ) as RankID

FROM test_orer_partition_by
----------------------------------------------
3.3 /** dense_rank() over(partition by prov_name ORDER BY val_cnt) */
select prov_name, city_name, val_cnt,
dense_rank() over(partition by prov_name ORDER BY val_cnt) AS Dense_RankID
from test_orer_partition_by ;
----------------------------------------------
3.4 /** NTILE() over(partition by prov_name ORDER BY val_cnt) */
select prov_name, city_name, val_cnt,
NTILE(5) over(partition by prov_name ORDER BY val_cnt) AS NTILEID
from test_orer_partition_by ;

-----------------------------------------------
select prov_name, city_name, val_cnt,
NTILE(5) over(ORDER BY val_cnt) AS NTILEID
from test_orer_partition_by ;
----------------------------------------------
4、聚合函数(sumavgcountmaxmin、first_value、last_value)
/** sum() over([partition by prov_name [ORDER BY val_cnt]]) */
有order by;按照排序连续累加;
无order by,计算partition by后的和;
over()中没有partition by,计算所有数据总和
select prov_name, city_name, val_cnt,
         sum(val_cnt) over(partition by prov_name ORDER BY val_cnt) AS rst
from test_orer_partition_by ;
----------------------------------------------
select prov_name, city_name, val_cnt,
sum(val_cnt) over(partition by prov_name) AS rst
from test_orer_partition_by ;
----------------------------------------------
select prov_name, city_name, val_cnt,
sum(val_cnt) over() AS rst
from test_orer_partition_by ;
----------------------------------------------
5、函数(lag、lead)
/** lead() over([partition by prov_name [ORDER BY val_cnt]]) */
lag、lead有三个参数,第一个是表达式或字段,第二个是偏移量,第三个是为控制赋值
select prov_name, city_name, val_cnt,
lead(val_cnt, 1) over(partition BY prov_name ORDER BY val_cnt) AS rst
from test_orer_partition_by ;
----------------------------------------------
select prov_name, city_name, val_cnt,
    lead(val_cnt, 2) over(partition BY prov_name ORDER BY val_cnt) AS rst
from test_orer_partition_by ;
----------------------------------------------

----------------------------------------------

----------------------------------------------

运行结果: SELECT * FROM test_orer_partition_by     --参照数据

---------------------------------------------------------------------------------------------------------------

3.1、排序row_number(),运行结果:

  

3.2,  3.3    RANK()、DENSE_RANK(),运行结果
     

-----------------------------------------------------------------------------------------------------------------
3.4    NTILE(n) OVER()                 
       OVER()参数中有分区,排序                             OVER()参数中无分区,有排序
    
----------------------------------------------------------------------------------------------------------------
4. 聚合  ,    运行结果
              
----------------------------------------------------------------------------------------------------------
5.  lead()  ,   lag()
      
---------------------------------------------------------------------------------------------------------------
LEAD
访问相同结果集的后续行中的数据,而不使用 SQL Server 2012 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。

语法:LEAD ( scalar_expression [ ,offset ] , [ default ] )     OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression,要返回的值基于指定的偏移量。 这是一个返回单个(标量)值的任何类型的表达式。scalar_expression 不能为分析函数
offset默认值为1, offset 可以是列、子查询或其他求值为正整数的表达式,或者可隐式转换为bigint。offset 不能是负数值或分析函数。
default默认值为NULL, offset 可以是列、子查询或其他求值为正整数的表达式,或者可隐式转换为bigint。offset不能是负数值或分析函数。


LAG
访问相同结果集的先前行中的数据,而不使用 SQL Server 2012 中的自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。

-------------------------------------------
Feb 4,2021补充,   其实一直以来对上面的那个Lag自己似乎没弄懂,当然也是没有多看,现在还没安排时间看这个,
但在学习《Minitab统计分析方法及应用_工信出版社_李志辉_李欣_主编》4.10.3章节:计算P值/F分布,涉及的线性代数的概念偏多,里面的概念也得看看,

 

 于是在Minitab的帮助文件中看到了此处有个Lag,此处先标注一下

 

posted @ 2020-05-29 12:39  CDPJ  阅读(242)  评论(0编辑  收藏  举报