Athena Presto SQL使用sequence生成日期间隔序列报错如何解决

问题定位 #

该报错属于代码编写问题，不是Athena Presto的功能限制。
报错信息明确说明sequence函数不支持直接传入两个date类型的参数，仅支持两类入参组合：

• 两个bigint类型的数字，可选第三个参数为步长
• 两个timestamp类型的时间戳，必须指定时间间隔作为第三个参数

解决方法 #

提供两种兼容性较高的实现方案，均可以得到预期的按job分组的连续日期序列：

方案1：数字序列偏移法（全版本兼容，推荐使用） #

通过计算日期间隔生成数字序列，再通过日期偏移得到连续日期：

WITH job_log_table AS (
    SELECT d.job_name, date(d.run_date) as run_date
    FROM (
        VALUES
            ('A', '2021-08-21'), ('A', '2021-08-25'),
            ('B', '2021-08-07'), ('B', '2021-08-24')
    ) d(job_name, run_date)
)
SELECT 
    jd.job_name,
    date_add('day', offset, jd.mind) AS run_date
FROM (
    SELECT 
        job_name,
        min(run_date) as mind,
        max(run_date) as maxd,
        date_diff('day', min(run_date), max(run_date)) as day_diff
    FROM job_log_table
    GROUP BY job_name
) jd
CROSS JOIN UNNEST(sequence(0, day_diff)) AS t(offset)

方案2：时间戳序列法 #

将日期转为timestamp类型后，指定1天的间隔生成时间序列，再转回date类型：

WITH job_log_table AS (
    SELECT d.job_name, date(d.run_date) as run_date
    FROM (
        VALUES
            ('A', '2021-08-21'), ('A', '2021-08-25'),
            ('B', '2021-08-07'), ('B', '2021-08-24')
    ) d(job_name, run_date)
)
SELECT 
    jd.job_name,
    date(dte) AS run_date
FROM (
    SELECT 
        job_name,
        min(run_date) as mind,
        max(run_date) as maxd,
        SEQUENCE(cast(min(run_date) as timestamp), cast(max(run_date) as timestamp), interval '1' day) as date_arr
    FROM job_log_table
    GROUP BY job_name
) jd
CROSS JOIN UNNEST(jd.date_arr) AS t(dte)

额外注意 #

原编写的SQL中LEFT JOIN关联条件使用了不存在的字段t.latest_date，如果需要关联原表其他字段，需要将该字段修改为原表的run_date字段。

内容的提问来源于stack exchange，提问作者Umar.H