如何将间隔1天的Date分组，聚合New Ink(ml)与Ink Used(ml)数据？

问题背景 #

需要按Paper Code、Ink Code对New Ink(ml)和Ink Used(ml)求和，但同一Paper和Ink下，间隔仅1天的日期需归为同一组聚合。

核心思路 #

通过窗口函数识别同一Paper Code+Ink Code下的连续日期块：

1. 用LAG()函数获取上一行的日期，判断当前日期与上一行的间隔是否超过1天；
2. 对"新组标记"累计求和，生成每个连续日期块的唯一ID；
3. 按Paper Code、Ink Code、组ID分组求和。

通用SQL实现（适用于PostgreSQL、BigQuery、MySQL 8.0+） #

假设你的主表名为ink_usage，字段对应Date、PaperCode、InkCode、NewInk、InkUsed：

WITH grouped_dates AS (
  SELECT
    Date,
    PaperCode,
    InkCode,
    NewInk,
    InkUsed,
    -- 标记当前行是否属于新的日期组
    CASE
      -- 组内第一行，标记为新组
      WHEN LAG(Date) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date) IS NULL THEN 1
      -- 与上一行日期间隔>1天，标记为新组
      WHEN DATE_DIFF(Date, LAG(Date) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date), DAY) > 1 THEN 1
      ELSE 0
    END AS is_new_group
  FROM ink_usage
),
group_ids AS (
  SELECT
    *,
    -- 累计生成每个连续日期块的唯一ID
    SUM(is_new_group) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date) AS group_id
  FROM grouped_dates
)
SELECT
  -- 显示组内的日期范围，也可以只取MIN(Date)作为组代表日期
  CONCAT(MIN(Date), ' to ', MAX(Date)) AS `Date Range`,
  PaperCode AS `Paper Code`,
  InkCode AS `Ink Code`,
  SUM(NewInk) AS `Total New Ink(ml)`,
  SUM(InkUsed) AS `Total Ink Used(ml)`
FROM group_ids
GROUP BY PaperCode, InkCode, group_id
ORDER BY PaperCode, InkCode, MIN(Date);

MySQL 兼容调整 #

如果使用MySQL，将DATE_DIFF替换为DATEDIFF即可：

WITH grouped_dates AS (
  SELECT
    Date,
    PaperCode,
    InkCode,
    NewInk,
    InkUsed,
    CASE
      WHEN LAG(Date) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date) IS NULL THEN 1
      WHEN DATEDIFF(Date, LAG(Date) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date)) > 1 THEN 1
      ELSE 0
    END AS is_new_group
  FROM ink_usage
),
group_ids AS (
  SELECT
    *,
    SUM(is_new_group) OVER (PARTITION BY PaperCode, InkCode ORDER BY Date) AS group_id
  FROM grouped_dates
)
SELECT
  CONCAT(MIN(Date), ' to ', MAX(Date)) AS `Date Range`,
  PaperCode AS `Paper Code`,
  InkCode AS `Ink Code`,
  SUM(NewInk) AS `Total New Ink(ml)`,
  SUM(InkUsed) AS `Total Ink Used(ml)`
FROM group_ids
GROUP BY PaperCode, InkCode, group_id
ORDER BY PaperCode, InkCode, MIN(Date);

示例验证 #

假设主表数据：

执行SQL后得到预期结果：

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