SQL窗口函数OVER

构造实验数据

假设有以下5天的订单量数据

窗口函数查询

SELECT  pt AS 日期
        ,order_cnt AS 订单量
        ,SUM(order_cnt)OVER () AS 累计
        ,SUM(order_cnt)OVER (ORDER BY pt) AS 按日升序累加
        ,AVG(order_cnt)OVER () AS 累计平均
        ,AVG(order_cnt)OVER (ORDER BY pt) AS 按日排序累计平均
        ,AVG(order_cnt)OVER (ORDER BY pt ROWS 2 PRECEDING ) AS 前3天移动平均
        ,ROW_NUMBER()OVER (ORDER BY pt) AS 序号
        ,RANK()OVER (ORDER BY order_cnt DESC ) AS 降序排名_跳跃
        ,DENSE_RANK()OVER (ORDER BY order_cnt DESC ) AS 降序排名_连续
        ,NTILE(2) OVER(ORDER BY pt) AS 数据分区_两个 
        ,NTILE(3) OVER(ORDER BY pt) AS 数据分区_3个 
FROM    tb

查询结果

统计每天不同类目的排名

假设源数据如下

sql代码

SELECT  order_dt AS 日期
        ,category_name AS 品类名称
        ,order_cnt AS 订单量
        ,RANK()OVER (PARTITION BY order_dt ORDER BY order_cnt DESC ) AS 每日类目排名
FROM    tb

查询结果