Athena(Presto) SQL实现日期差到离散时间间隔序列的转换

可以实现，核心逻辑是先按实体分组排序事件、计算相邻事件日期差、按大单位优先规则拆分间隔、最后合并事件和间隔标记生成序列，完整可运行代码如下：

WITH sorted_events AS (
    SELECT
        event,
        entity,
        CAST(date AS DATE) AS event_date,
        LAG(CAST(date AS DATE)) OVER (PARTITION BY entity ORDER BY CAST(date AS DATE), event) AS prev_date
    FROM your_event_table -- 替换为实际表名
),
calc_year_diff AS (
    SELECT
        *,
        COALESCE(TIMESTAMP_DIFF(event_date, prev_date, 'year'), 0) AS year_diff,
        DATE_ADD('year', COALESCE(TIMESTAMP_DIFF(event_date, prev_date, 'year'), 0), prev_date) AS after_year_sub
    FROM sorted_events
),
calc_month_diff AS (
    SELECT
        *,
        COALESCE(TIMESTAMP_DIFF(event_date, after_year_sub, 'month'), 0) AS month_diff,
        DATE_ADD('month', COALESCE(TIMESTAMP_DIFF(event_date, after_year_sub, 'month'), 0), after_year_sub) AS after_month_sub
    FROM calc_year_diff
),
calc_week_day_diff AS (
    SELECT
        *,
        COALESCE(TIMESTAMP_DIFF(event_date, after_month_sub, 'day') / 7, 0) AS week_diff,
        COALESCE(TIMESTAMP_DIFF(event_date, after_month_sub, 'day') % 7, 0) AS day_diff
    FROM calc_month_diff
),
build_sequence_array AS (
    SELECT
        entity,
        event_date,
        CASE
            WHEN prev_date IS NULL THEN ARRAY[event]
            ELSE CONCAT(
                ARRAY_FILL('nextyear', ARRAY[year_diff]),
                ARRAY_FILL('nextmonth', ARRAY[month_diff]),
                ARRAY_FILL('nextweek', ARRAY[week_diff]),
                ARRAY_FILL('nextday', ARRAY[day_diff]),
                ARRAY[event]
            )
        END AS seq_items
    FROM calc_week_day_diff
),
flatten_sequence AS (
    SELECT
        entity,
        item AS event,
        event_date,
        item_pos
    FROM build_sequence_array
    CROSS JOIN UNNEST(seq_items) WITH ORDINALITY AS t(item, item_pos)
)
SELECT
    event,
    entity,
    ROW_NUMBER() OVER (PARTITION BY entity ORDER BY event_date, item_pos) AS seq
FROM flatten_sequence
ORDER BY entity, seq

逻辑说明 #

• 同日期事件之间不会插入间隔标记，和样例中同日事件连续排列的规则一致
• 间隔拆分严格按照「年→月→周→天」的大单位优先规则，和你给出的14个月15天拆分为1年+2个月+2周+1天的逻辑完全匹配
• 同日期事件默认按事件名称排序，如果有其他同日期排序规则（比如事件发生时间戳、业务优先级字段），替换窗口函数ORDER BY子句里的event字段即可
• 代码直接兼容Athena引擎基于的Presto/Trino语法，不需要额外UDF，所有函数都是引擎内置函数

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