如何从含管道分隔字符串的表生成含起止日期的新表

核心逻辑 #

每个a:active是一段状态的起始，紧随其后的s:suspend或d:deactive是这段状态的结束；如果只有单个a，则仅保留起始日期。实现步骤分为：拆分字符串、提取日期与状态、配对起止日期。

PostgreSQL 实现代码 #

WITH split_data AS (
    SELECT 
        id,
        elem,
        substr(elem, 1, 6) AS date_str,
        substr(elem, 7, 1) AS status,
        -- 获取同一id下的下一个状态日期
        lead(substr(elem, 1, 6)) OVER (PARTITION BY id ORDER BY substr(elem, 1, 6)) AS next_date_str
    FROM your_table,
         -- 按|拆分value为多行
         unnest(string_to_array(value, '|')) AS elem
)
SELECT 
    id,
    -- 转换日期格式为DD.MM.YYYY
    to_char(to_date(date_str, 'YYMMDD'), 'DD.MM.YYYY') AS start_date,
    CASE 
        WHEN next_date_str IS NOT NULL THEN to_char(to_date(next_date_str, 'YYMMDD'), 'DD.MM.YYYY') 
        ELSE NULL 
    END AS end_date
FROM split_data
-- 只保留起始状态的行
WHERE status = 'a'
ORDER BY id, start_date;

MySQL 8.0+ 实现代码 #

WITH split_data AS (
    SELECT 
        t.id,
        j.elem,
        SUBSTRING(j.elem, 1, 6) AS date_str,
        SUBSTRING(j.elem, 7) AS status,
        -- 获取同一id下的下一个状态日期
        LEAD(SUBSTRING(j.elem, 1, 6)) OVER (PARTITION BY t.id ORDER BY SUBSTRING(j.elem, 1, 6)) AS next_date_str
    FROM your_table t
    -- 用JSON_TABLE将管道分隔字符串拆分为多行
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.value, '|', '","'), '"]'),
        '$[*]' COLUMNS (elem VARCHAR(20) PATH '$')
    ) j
)
SELECT 
    id,
    -- 转换日期格式为DD.MM.YYYY
    DATE_FORMAT(STR_TO_DATE(date_str, '%y%m%d'), '%d.%m.%Y') AS start_date,
    CASE 
        WHEN next_date_str IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(next_date_str, '%y%m%d'), '%d.%m.%Y')
        ELSE NULL 
    END AS end_date
FROM split_data
-- 只保留起始状态的行
WHERE status = 'a'
ORDER BY id, start_date;

结果验证 #

运行上述代码后，会得到你期望的表结构：

id | start_date | end_date
 1  | 25.03.2022 | 08.06.2022
 2  | 25.03.2022 | 25.03.2022
 2  | 22.08.2022 | 22.08.2022 
 3  | 25.03.2022 | NULL
 4  | 25.03.2022 | 29.03.2022

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