1.源数据如下
2.sum avg max min
SELECT
date_str,
date_year,
date_month,
curr_month_num,
SUM ( curr_month_num ) OVER ( PARTITION BY date_year ) AS sum_num,
AVG ( curr_month_num ) OVER ( PARTITION BY date_year ) AS avg_num,
MAX ( curr_month_num ) OVER ( PARTITION BY date_year ) AS max_num,
MIN ( curr_month_num ) OVER ( PARTITION BY date_year ) AS min_num
FROM
owr_view
结果如下
可以看到 over后面只有partition by 这一个条件 所以 全年都是一样的
有时可能需要每月变化 动态的 需要如下的sql
SELECT
date_str,
date_year,
date_month,
curr_month_num,
SUM ( curr_month_num ) OVER
( PARTITION BY date_year order by date_month ASc ) AS sum_num,
AVG ( curr_month_num ) OVER
( PARTITION BY date_year order by date_month ASc) AS avg_num,
MAX ( curr_month_num ) OVER
( PARTITION BY date_year order by date_month ASc) AS max_num,
MIN ( curr_month_num ) OVER
( PARTITION BY date_year order by date_month ASc) AS min_num
FROM
owr_view
结果如下
2.排序 rank dense_rank row_nu
SELECT
date_str,
date_year,
date_month,
curr_month_num,
RANK ( ) OVER
( PARTITION BY date_year ORDER BY curr_month_num DESC ) AS ranking,
DENSE_RANK ( ) OVER
( PARTITION BY date_year ORDER BY curr_month_num DESC ) AS DENSERANK,
ROW_NUMBER ( ) OVER
( PARTITION BY date_year ORDER BY curr_month_num DESC ) AS ROWNUMBER
FROM
owr_view
结果如下
注意区别
rank() 相同值的同样排名 会占用下一排名 最后一名小于 等于 总数
dense_rank() 相同值的同样排名 不会占用下一排名 最后一名可能小于总数
row_number() 顾名思义 就是向后排 相同值的看造化 最后一名 等于 总数
3.得到前几名
SELECT
*
FROM
(
SELECT date_str, date_year, date_month, curr_month_num,
RANK ( ) OVER
( PARTITION BY date_year ORDER BY curr_month_num DESC ) AS ranking
FROM owr_view
) T
WHERE
T.ranking <5
这就是对前面的扩展 如果经常用 T最好写成视图
thank you, Bye