如何处理单列中的多时间区间？动画剧集数据处理求助

先明确拆分逻辑的核心依据 #

要不要把多时间区间拆成单独行，完全看你的后续分析需求：

• 若需要按单个播出周期分析（比如对比停播复播前后的评分变化），必须拆分，每个时间区间对应一行，评分等其他字段原样复制；
• 若只关心剧集的整体播出跨度（忽略中间停播），直接提取所有时间区间里最早的起始年和最晚的结束年即可。

情况1：拆分多时间区间为单独行 #

MySQL 实现（递归CTE拆分逗号分隔的区间）

WITH RECURSIVE split_years AS (
    -- 提取第一个时间区间
    SELECT 
        id, -- 假设表有唯一主键id，用于关联其他字段
        title,
        rating, -- 替换为你的评分相关字段
        SUBSTRING_INDEX(`Year`, ',', 1) AS period,
        TRIM(SUBSTRING(`Year`, LENGTH(SUBSTRING_INDEX(`Year`, ',', 1)) + 2)) AS remaining_years
    FROM animated_tv_series
    WHERE `Year` IS NOT NULL AND `Year` != ''
    UNION ALL
    -- 递归拆分剩余的时间区间
    SELECT 
        id,
        title,
        rating,
        SUBSTRING_INDEX(remaining_years, ',', 1) AS period,
        TRIM(SUBSTRING(remaining_years, LENGTH(SUBSTRING_INDEX(remaining_years, ',', 1)) + 2)) AS remaining_years
    FROM split_years
    WHERE remaining_years != ''
)
-- 从拆分后的区间中提取起止年份
SELECT 
    id,
    title,
    rating,
    SUBSTRING_INDEX(period, '-', 1) AS start_year,
    SUBSTRING_INDEX(period, '-', -1) AS end_year
FROM split_years;

PostgreSQL 实现（更简洁的字符串拆分函数）

SELECT 
    id,
    title,
    rating,
    SPLIT_PART(period, '-', 1) AS start_year,
    SPLIT_PART(period, '-', 2) AS end_year
FROM animated_tv_series,
     UNNEST(STRING_TO_ARRAY(`Year`, ', ')) AS period;

情况2：合并为整体播出跨度（取最早起始年+最晚结束年） #

以MySQL为例：

SELECT 
    id,
    title,
    rating,
    -- 提取所有起始年份中的最小值
    MIN(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(`Year`, ',', numbers.n), '-', 1) AS UNSIGNED)) AS overall_start_year,
    -- 提取所有结束年份中的最大值
    MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(`Year`, ',', numbers.n), '-', -1) AS UNSIGNED)) AS overall_end_year
FROM animated_tv_series
-- 生成数字覆盖最大区间数（这里假设最多2个，若有更多可继续添加UNION ALL SELECT n）
JOIN (SELECT 1 AS n UNION ALL SELECT 2) AS numbers
ON numbers.n <= LENGTH(`Year`) - LENGTH(REPLACE(`Year`, ',', '')) + 1
GROUP BY id, title, rating;

内容的提问来源于stack exchange，提问作者n.ceren