如何在SQL中跨分区获取下值并推导作者机构任职周期

核心思路 #

要高效推导任职周期，核心是先简化重复数据，再基于作者的所有出版时间节点，确定每个机构对应的时间区间：

1. 先去重同一作者-机构-出版日期的重复记录，减少计算量
2. 提取每个作者的所有唯一出版日期，用窗口函数获取每个日期的下一个时间节点
3. 对每个作者的每个机构，取最早出版日期作为任职起始，再找到该机构最后一次出版后的第一个其他时间节点作为结束（无后续节点则用当前日期）

实现SQL #

WITH deduplicated_affiliations AS (
    -- 去重：同一作者同一机构同一日期的重复记录
    SELECT DISTINCT
        author_id,
        institution,
        publication_date
    FROM affiliations
),
author_all_unique_dates AS (
    -- 提取每个作者的所有唯一出版日期，并获取每个日期的下一个时间节点
    SELECT
        author_id,
        publication_date,
        LEAD(publication_date) OVER (PARTITION BY author_id ORDER BY publication_date) AS next_publication_date
    FROM (
        SELECT DISTINCT author_id, publication_date
        FROM deduplicated_affiliations
    ) t
),
author_institution_periods AS (
    -- 计算每个作者-机构的最早/最晚出版日期
    SELECT
        author_id,
        institution,
        MIN(publication_date) AS start_date,
        MAX(publication_date) AS last_pub_date
    FROM deduplicated_affiliations
    GROUP BY author_id, institution
)
-- 关联得到最终的任职周期：找到最后出版日期后的第一个时间节点，无则用当前日期
SELECT
    aip.author_id,
    aip.institution,
    aip.start_date,
    COALESCE(
        (SELECT MIN(aud.next_publication_date)
         FROM author_all_unique_dates aud
         WHERE aud.author_id = aip.author_id
           AND aud.publication_date >= aip.last_pub_date),
        CURRENT_DATE
    ) AS end_date
FROM author_institution_periods aip
ORDER BY aip.author_id, aip.start_date;

性能优化说明 #

• 去重步骤大幅减少后续窗口函数和聚合的计算数据量，适配百万级数据集
• 窗口函数LEAD仅按作者分区排序，计算成本低，且可利用(author_id, publication_date)索引加速
• 最终关联用子查询取最小后续日期，配合索引能快速定位，避免全表扫描

内容的提问来源于stack exchange，提问作者d.hatch75