sql中函数的使用 sum avg min max rank

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