通过门店销售明细表用SQL得到每月每个门店的销冠和按月的同比环比数据
假设我在Snowflake里有销售表，包含ID主键、门店ID、日期、销售员姓名和销售额，需要统计出每个月所有门店和各门店销售额最高的人，不一定是一个人，以及他所在的门店ID和月总销售额。
统计每个月份下，各门店内销售额最高的销售员（可能多人并列），并显示其在该门店的月总销售额。
分步说明：
-
按门店+月份+销售员汇总销售额
计算每个销售员在每个门店的月总销售额。 -
确定各门店内的最高销售额
按月份和门店分组，找到每个门店当月的最高销售额。 -
关联最高销售额的销售员
匹配每个门店中达到最高销售额的销售员（允许多个并列）。
SQL 代码：
WITH 门店销售员明细 AS (SELECTDATE_TRUNC('MONTH', 日期) AS 月份,门店ID,销售员姓名,SUM(销售额) AS 月总销售额FROM 销售表GROUP BY 月份, 门店ID, 销售员姓名
),
各门店最高销售额 AS (SELECT月份,门店ID,MAX(月总销售额) AS 最高销售额FROM 门店销售员明细GROUP BY 月份, 门店ID
)
SELECTsm.月份,sm.门店ID,sm.销售员姓名,sm.月总销售额
FROM 门店销售员明细 sm
JOIN 各门店最高销售额 mhON sm.月份 = mh.月份AND sm.门店ID = mh.门店IDAND sm.月总销售额 = mh.最高销售额
ORDER BY sm.月份, sm.门店ID;
输出结果：
| 月份 | 门店ID | 销售员姓名 | 月总销售额 |
|---|---|---|---|
| 2023-01-01 | 001 | 张三 | 50000 |
| 2023-01-01 | 002 | 李四 | 48000 |
| 2023-01-01 | 002 | 王五 | 48000 |
| 2023-02-01 | 001 | 赵六 | 52000 |
关键点解释：
-
门店销售员明细
按月份 + 门店 + 销售员汇总销售额，确保每个销售员在单店的表现独立统计。 -
各门店最高销售额
通过GROUP BY 月份, 门店ID找到每个门店当月的最高销售额阈值。 -
最终匹配
通过JOIN将明细与最高销售额关联，精确筛选出每个门店的优胜者（含并列情况）。
扩展说明：
- 并列处理：若多个销售员在同一门店的销售额相同且为最高，结果中将全部列出。
- 性能优化：Snowflake的列式存储和自动分区优化可高效处理此类聚合查询。
- 动态调整：如需排除零销售额门店，可在明细中增加
HAVING SUM(销售额) > 0。
假设我在Snowflake里有销售表，包含ID主键、门店ID、日期、销售员姓名和销售额，需要统计出按月统计的同比和环比数据，当前月如果不是月底的话，同比或环比数据需要取得上个月或者去年1日到对应的日期的总销售额值。
解决方案：
按月统计销售额的同比（去年同期）和环比（上月同期）数据，动态处理未完整月份（如当前月未结束时，同比环比均取相同天数范围）。
分步说明：
-
计算每个月的实际日期范围
动态确定每个月的起始日期和截止日期（若当前月未结束，取到当前日）。 -
生成同比环比对比日期范围
根据当前月实际天数，生成上月和去年同期的对应日期范围（自动处理闰年等边界）。 -
关联数据计算销售额
将当前月、上月、去年同期三个范围的销售额关联到同一行。
SQL 代码：
WITH
-- 1. 获取所有存在的月份
所有月份 AS (SELECT DISTINCT DATE_TRUNC('MONTH', 日期) AS 月份FROM 销售表
),-- 2. 计算每个月的实际起止日期（若为当前月则截止到今日）
月份日期范围 AS (SELECT月份,月份 AS 当月起始日期,LEAST(DATEADD(DAY, -1, DATEADD(MONTH, 1, 月份)), -- 自然月最后一天CURRENT_DATE() -- 若为当前月则截止到今日) AS 当月截止日期FROM 所有月份
),-- 3. 计算每个月的实际天数（用于生成同比环比范围）
月份天数 AS (SELECT月份,当月起始日期,当月截止日期,DATEDIFF(DAY, 当月起始日期, 当月截止日期) + 1 AS 当月天数FROM 月份日期范围
),-- 4. 生成上月同期范围（自动处理天数溢出）
上月范围 AS (SELECT月份,DATEADD(MONTH, -1, 当月起始日期) AS 上月起始日期,LEAST(DATEADD(DAY, 当月天数 -1, DATEADD(MONTH, -1, 当月起始日期)), -- 上月起始 + 相同天数DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, -1, 当月起始日期))) -- 上月自然最后一天) AS 上月截止日期FROM 月份天数
),-- 5. 生成去年同期范围（自动处理闰年）
去年范围 AS (SELECT月份,DATEADD(YEAR, -1, 当月起始日期) AS 去年起始日期,LEAST(DATEADD(DAY, 当月天数 -1, DATEADD(YEAR, -1, 当月起始日期)), -- 去年起始 + 相同天数DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(YEAR, -1, 当月起始日期))) -- 去年自然最后一天) AS 去年截止日期FROM 月份天数
),-- 6. 计算各范围销售额
当月销售额 AS (SELECTm.月份,SUM(销售额) AS 当月销售额FROM 月份日期范围 mJOIN 销售表 s ON s.日期 BETWEEN m.当月起始日期 AND m.当月截止日期GROUP BY m.月份
),
上月销售额 AS (SELECTp.月份,SUM(销售额) AS 上月销售额FROM 上月范围 pJOIN 销售表 s ON s.日期 BETWEEN p.上月起始日期 AND p.上月截止日期GROUP BY p.月份
),
去年销售额 AS (SELECTy.月份,SUM(销售额) AS 去年销售额FROM 去年范围 yJOIN 销售表 s ON s.日期 BETWEEN y.去年起始日期 AND y.去年截止日期GROUP BY y.月份
)-- 7. 最终关联结果
SELECTcurr.月份,curr.当月销售额,prev.上月销售额,prev.上月销售额 / NULLIF(curr.当月销售额, 0) -1 AS 环比增长率, -- 可选：计算增长率last_year.去年销售额,last_year.去年销售额 / NULLIF(curr.当月销售额, 0) -1 AS 同比增长率 -- 可选：计算增长率
FROM 当月销售额 curr
LEFT JOIN 上月销售额 prev ON curr.月份 = prev.月份
LEFT JOIN 去年销售额 last_year ON curr.月份 = last_year.月份
ORDER BY curr.月份;
输出示例：
| 月份 | 当月销售额 | 上月销售额 | 环比增长率 | 去年销售额 | 同比增长率 |
|---|---|---|---|---|---|
| 2023-03-01 | 150000 | 140000 | 0.0714 | 120000 | 0.25 |
| 2023-04-01 | 80000 | 90000 | -0.1111 | 70000 | 0.1429 |
关键逻辑说明：
-
动态日期范围
- 若当前月未结束（如今天是3月15日），则统计 3月1日~3月15日 的数据。
- 环比取 2月1日~2月15日，同比取 去年3月1日~3月15日。
-
闰年与月末处理
- 当对比日期超出目标月实际天数时（如：当前月结束于31日，但目标月只有30日），自动截断到目标月最后一天。
-
增长率计算（可选）
- 使用
NULLIF避免除零错误，确保分母不为零时计算增长率。
- 使用
扩展建议：
- 性能优化：Snowflake的自动分区和集群键（如按
日期分区）可加速范围查询。 - 空值处理：使用
COALESCE(上月销售额, 0)等函数处理无对比数据的情况。