PostgreSQL将事件数据转换为时间序列的SQL查询需求

现有事件表结构如下：

CODE  | start      | end
A     | 2023-03-03 |2023-03-04
A     | 2023-03-07 |2023-03-09 
B     | 2023-03-03 |2023-03-06
C     | 2023-03-02 | "null"

字段说明：

• code：事件编码
• start：事件激活时间
• end：事件恢复时间，可为空（表示事件尚未恢复）

需要编写PostgreSQL可执行的SQL查询，将指定事件编码（如示例中的A）的事件数据转换为时间序列，输出格式如下：

2023-03-03 1 
2023-03-04 0 
2023-03-07 1
2023-03-09 0

注：1代表事件激活，0代表事件恢复，查询需支持按事件编码筛选（类似SELECT timestamp, value FROM timeseries WHERE code = ?）。

以下是满足需求的PostgreSQL SQL语句：

WITH event_changes AS (
    -- 提取激活事件（标记为1）
    SELECT code, start AS event_time, 1 AS value
    FROM your_table_name
    WHERE code = ?
    UNION ALL
    -- 提取恢复事件（标记为0，仅处理非空的end值）
    SELECT code, end AS event_time, 0 AS value
    FROM your_table_name
    WHERE code = ?
      AND end IS NOT NULL
      AND end != 'null' -- 处理表中字符串形式的"null"，若实际存储为SQL NULL可删除此条件
)
SELECT event_time AS timestamp, value
FROM event_changes
ORDER BY event_time;

逻辑说明 #

1. 用CTEevent_changes合并两类事件：
   • 从原表中提取start时间作为激活事件，对应值1；
   • 从原表中提取有效（非空且不是字符串"null"）的end时间作为恢复事件，对应值0。
2. 最后将合并后的事件按时间排序，输出要求的timestamp和value字段，通过?占位符即可指定要筛选的事件编码。

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