      现在有这样一个需求：一张表sales中记录了公司每个业务销售的每月销售数据，来分析下2个案例。

1.sales表结构

| month  | user_id | amount |
+--------+---------+--------+
| 201601 | 1       |    500 |
| 201601 | 2       |    300 |
| 201601 | 3       |    100 |
| 201602 | 1       |   1000 |
| 201602 | 2       |    800 |
| 201603 | 2       |   1000 |
| 201603 | 3       |    500 |
| 201604 | 1       |   1000 |

2.需求一：求每个月的销售额及累计销售额

开窗函数实现：

SELECT 
    month,
    SUM(amount) AS cur_month_amount,
    SUM(SUM(amount)) OVER (ORDER BY month) 
FROM sales 
GROUP BY month ORDER BY month;

或者

SELECT
    month,
    SUM(amount) OVER(PARTITION BY month) AS cur_month_amount,
    SUM(amount) OVER(ORDER BY month) AS sum_amount
FROM sales;

3.需求二：每个月的销售冠军和销售额

开窗函数实现：

SELECT
    month,
    user_id,
    MAX(SUM(amount)) OVER(PARTITION BY month, user_id) AS cur_month_top_amount
FROM sales;

或者

SELECT
    month,
    user_id,
    amount
FROM (
    SELECT
        month,
        user_id,
        amount,
        ROW_NUMBER() OVER(PARTITION BY month ORDER BY amount DESC) AS rank
    FROM sales
) a
where rank = 1;

How time flies.
书山有路勤为径，学海无涯苦作舟。

欢迎关注微信公众号：【程序员写书】

喜欢宠物的朋友可以关注：【电巴克宠物Pets】

一起学习，一起进步。