求助：基于行级数据生成时间线的SQL实现方法

原始数据集 #

核心思路 #

你需要按REQ_NUM分组，根据事件发生的时间顺序，将每个Posted事件与后续第一个Expired/Unposted事件配对。推荐使用窗口函数（适合SQL 8.0+版本）或自连接查询来实现，避免简单MIN/MAX分组导致的配对错误。

方法1：使用LEAD窗口函数（推荐） #

适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库，逻辑清晰且性能较好：

WITH ordered_events AS (
    SELECT 
        REQ_NUM,
        DATE,
        EVENT,
        USER_ENTR,
        -- 按REQ_NUM分组、日期升序，获取下一个事件的日期和类型
        LEAD(DATE) OVER (PARTITION BY REQ_NUM ORDER BY DATE) AS next_event_date,
        LEAD(EVENT) OVER (PARTITION BY REQ_NUM ORDER BY DATE) AS next_event_type
    FROM your_table_name
)
SELECT 
    REQ_NUM,
    DATE AS START_DT,
    next_event_date AS END_DT,
    USER_ENTR,
    -- 计算发布时长（天数，不同数据库语法可能有差异）
    DATEDIFF(next_event_date, DATE) AS duration_days
FROM ordered_events
WHERE EVENT = 'Posted'
  AND next_event_type IN ('Expired', 'Unposted');

代码解释：

1. ordered_events公共表表达式：给每条事件标记出同一REQ_NUM下的下一个事件信息。
2. 主查询筛选出所有Posted事件，仅保留后续事件为终止类型的记录，直接配对生成周期数据。

方法2：自连接查询（兼容低版本SQL） #

如果你的数据库不支持窗口函数（如MySQL 5.x），可以用自连接实现：

SELECT 
    p.REQ_NUM,
    p.DATE AS START_DT,
    MIN(e.DATE) AS END_DT,
    p.USER_ENTR,
    DATEDIFF(MIN(e.DATE), p.DATE) AS duration_days
FROM your_table_name p
JOIN your_table_name e 
    ON p.REQ_NUM = e.REQ_NUM
    AND e.DATE > p.DATE
    AND e.EVENT IN ('Expired', 'Unposted')
WHERE p.EVENT = 'Posted'
GROUP BY p.REQ_NUM, p.DATE, p.USER_ENTR
ORDER BY p.DATE;

代码解释：

1. 将表自连接，把Posted事件表p关联到同一REQ_NUM下、日期更晚的终止事件表e。
2. 用MIN(e.DATE)取Posted之后最早的终止事件日期，确保配对的是最近的终止事件。

最终结果示例 #

执行上述代码后会得到以下结构化的发布周期数据，可直接用于计算时长和趋势：

注意事项 #

• 如果存在Posted后无终止事件的情况（如仍在发布中），可将JOIN改为LEFT JOIN，此时END_DT会为NULL，可根据需求用当前日期填充。
• 不同数据库的日期计算函数有差异：PostgreSQL用DATE_PART('day', next_event_date - DATE)，Oracle用TRUNC(next_event_date) - TRUNC(DATE)，需对应调整。

内容的提问来源于stack exchange，提问作者Jose Torres-Jasso