SQL SERVER开窗函数DEMO

1-统计版本数量

 SELECT COUNT ( T1.id ) OVER () AS total, T1.* FROM DIM_EDITION_THREE T1 

2-统计不同类型版本的数量

SELECT COUNT
    ( T1.id ) OVER (PARTITION BY T1.fn_status) AS total,
    T1.* 
FROM
    DIM_EDITION_THREE T1
    ORDER BY T1.fn_status

3-统计不同年份不同年的数量

SELECT 
    COUNT(T1.id) OVER (PARTITION BY write_year,years) as total,T1.* 
FROM
    DIM_TIME_THREE T1
    ORDER BY write_year,years

4-按年份累计个数

SELECT COUNT
    ( T1.num ) OVER ( PARTITION BY write_year ORDER BY write_year,LEN(num),num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total,
    T1.* 
FROM
    DIM_TIME_THREE T1 
ORDER BY
    write_year,LEN(num),num

5-相邻三条数据累加

SELECT COUNT
    ( T1.num ) OVER ( PARTITION BY write_year ORDER BY write_year, LEN( num ), num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total,
    COUNT ( T1.num ) OVER ( PARTITION BY write_year ORDER BY write_year, LEN( num ), num ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS total1,
    T1.* 
FROM
    DIM_TIME_THREE T1 
ORDER BY
    write_year,
    LEN( num ),
    num

 

6-相邻数据获取

SELECT
  LAG(T1.write_period,1) OVER (PARTITION BY T1.write_year order by write_year,
    LEN( num ),
    num) as '上一个时间维度',
    LEAD(T1.write_period,1) OVER (PARTITION BY T1.write_year order by write_year,
    LEN( num ),
    num) as '下一个时间维度',
    T1.* 
FROM
    DIM_TIME_THREE T1 
ORDER BY
    write_year,
    LEN( num ),
    num

7-排名

SELECT
  RANK() OVER (PARTITION BY T1.write_year order by write_year,
    LEN( num ),
    num) as '排名',
    T1.* 
FROM
    DIM_TIME_THREE T1 
ORDER BY
    write_year,
    LEN( num ),
    num