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