如何按emp_id实现some_date的Concat rollup值，满足活跃期规则？

原表数据 #

表包含emp_id和some_date两列，数据如下：

emp_id,some_date
1,2002-11-23
1,2006-06-09
1,2009-05-05
1,2013-06-06
2,1978-07-05
2,1980-04-15
2,1984-08-31

期望输出 #

按员工分组生成各阶段的累积日期拼接结果，格式如下（已修正原输出中的笔误）：

1 2002-11-23
1 2002-11-23,2006-06-09
1 2002-11-23,2006-06-09,2009-05-05
1 2002-11-23,2006-06-09,2009-05-05,2013-06-06
2 1978-07-05
2 1978-07-05,1980-04-15
2 1978-07-05,1980-04-15,1984-08-31

核心规则 #

• 按emp_id分组，对每个员工的some_date按时间升序排序
• 生成累积拼接的日期串：每一行包含当前及之前所有日期
• 活跃期匹配：当指定查询时间落在某两个连续日期的区间时，返回对应的累积行。例如：
  • 查询时间在2006-06-09至2009-05-04之间，返回员工1的第二行
  • 查询时间在2009-05-05至2013-06-05之间，返回员工1的第三行

实现方案 #

PostgreSQL 版本 #

步骤1：生成所有累积拼接行及对应时间区间

WITH ordered_dates AS (
    SELECT
        emp_id,
        some_date,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY some_date) AS rn,
        LEAD(some_date) OVER (PARTITION BY emp_id ORDER BY some_date) AS next_date
    FROM your_table_name
),
cumulative_dates AS (
    SELECT
        emp_id,
        rn,
        some_date,
        next_date,
        STRING_AGG(some_date, ',' ORDER BY some_date) OVER (PARTITION BY emp_id ORDER BY some_date) AS date_rollup
    FROM ordered_dates
)
SELECT
    emp_id,
    date_rollup,
    CASE
        WHEN next_date IS NULL THEN (some_date, 'infinity'::date)
        ELSE (some_date, next_date - INTERVAL '1 day')::daterange
    END AS active_range
FROM cumulative_dates
ORDER BY emp_id, rn;

步骤2：根据查询时间匹配对应行

以查询2008-01-01的结果为例：

WITH ordered_dates AS (
    SELECT
        emp_id,
        some_date,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY some_date) AS rn,
        LEAD(some_date) OVER (PARTITION BY emp_id ORDER BY some_date) AS next_date
    FROM your_table_name
),
cumulative_dates AS (
    SELECT
        emp_id,
        rn,
        some_date,
        next_date,
        STRING_AGG(some_date, ',' ORDER BY some_date) OVER (PARTITION BY emp_id ORDER BY some_date) AS date_rollup
    FROM ordered_dates
)
SELECT
    emp_id || ' ' || date_rollup AS result
FROM cumulative_dates
WHERE '2008-01-01'::date <@ 
    CASE
        WHEN next_date IS NULL THEN (some_date, 'infinity'::date)
        ELSE (some_date, next_date - INTERVAL '1 day')::daterange
    END
ORDER BY emp_id;

MySQL 8.0+ 版本 #

WITH ordered_dates AS (
    SELECT
        emp_id,
        some_date,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY some_date) AS rn,
        LEAD(some_date) OVER (PARTITION BY emp_id ORDER BY some_date) AS next_date
    FROM your_table_name
),
cumulative_dates AS (
    SELECT
        o1.emp_id,
        o1.rn,
        o1.some_date,
        o1.next_date,
        GROUP_CONCAT(o2.some_date ORDER BY o2.some_date SEPARATOR ',') AS date_rollup
    FROM ordered_dates o1
    JOIN ordered_dates o2 ON o1.emp_id = o2.emp_id AND o2.rn <= o1.rn
    GROUP BY o1.emp_id, o1.rn, o1.some_date, o1.next_date
)
SELECT
    CONCAT(emp_id, ' ', date_rollup) AS result
FROM cumulative_dates
WHERE '2008-01-01' BETWEEN some_date AND COALESCE(DATE_SUB(next_date, INTERVAL 1 DAY), '9999-12-31')
ORDER BY emp_id, rn;

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