按动态日期范围自动分组的SQL报表查询需求

需求说明 #

编写SQL查询生成时间范围报表，需根据数据的日期跨度自动切换分组维度：

• 日期跨度小于1个月：按天分组（同一天的记录合并）
• 日期跨度小于1年：按月分组（同一月的记录合并）
• 日期跨度大于1年：按年分组（同一年的记录合并）
  同时筛选出指定时间范围内的数据。

通用SQL查询语句 #

WITH date_range AS (
    SELECT
        MIN(createdAt) AS start_date,
        MAX(createdAt) AS end_date
    FROM ViewSell
    WHERE branchId = ? -- 替换为目标门店ID
)
SELECT
    branchId,
    ROUND(SUM(totalPrice), 2) AS sumTotalPrice,
    CASE
        WHEN DATEDIFF(day, dr.start_date, dr.end_date) < 30 THEN CAST(createdAt AS DATE)
        WHEN TIMESTAMPDIFF(year, dr.start_date, dr.end_date) < 1 THEN DATE_FORMAT(createdAt, '%Y-%m-01')
        ELSE DATE_FORMAT(createdAt, '%Y-01-01')
    END AS timeFrame
FROM ViewSell
CROSS JOIN date_range dr
WHERE branchId = ? -- 与上方替换为相同的门店ID
  AND createdAt BETWEEN dr.start_date AND dr.end_date
GROUP BY
    branchId,
    CASE
        WHEN DATEDIFF(day, dr.start_date, dr.end_date) < 30 THEN CAST(createdAt AS DATE)
        WHEN TIMESTAMPDIFF(year, dr.start_date, dr.end_date) < 1 THEN DATE_FORMAT(createdAt, '%Y-%m-01')
        ELSE DATE_FORMAT(createdAt, '%Y-01-01')
    END
ORDER BY timeFrame;

查询逻辑说明 #

1. 计算日期跨度：通过CTE date_range 获取当前门店数据的最早和最晚日期，确定分组维度的判断依据
2. 动态分组：使用CASE语句根据日期跨度选择分组规则：
   • 跨度<30天：将时间戳转为纯日期格式，按天聚合
   • 跨度≥30天且<1年：格式化为当月第一天，按月聚合
   • 跨度≥1年：格式化为当年第一天，按年聚合
3. 数据筛选与聚合：筛选指定门店的时间范围内数据，汇总总价并按时间维度排序

示例验证 #

示例1：日期跨度小于1个月 #

原查询

SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 1
ORDER BY
  createdAt ASC

原结果

期望结果

示例2：日期跨度小于1年 #

原查询

SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 4
ORDER BY
  createdAt ASC

原结果

期望结果

示例3：日期跨度大于1年 #

原查询

SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 2
ORDER BY
  createdAt ASC

原结果

期望结果

内容的提问来源于stack exchange，提问作者Rawand