我正在尝试使用 Presto SQL (Athena) 生成一系列日期unnest
and sequence
类似的东西generate_series
在 postgres 中。
我的桌子看起来像
job_name | run_date
A | '2021-08-21'
A | '2021-08-25'
B | '2021-08-07'
B | '2021-08-24'
SELECT d.job_name, d.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)
我的目标是输出如下
job_name | run_date
A | 2021-08-21
A | 2021-08-22
A | 2021-08-23
A | 2021-08-24
A | 2021-08-25
B | 2021-08-07
B | 2021-08-08
B | 2021-08-09
B | 2021-08-10
B | 2021-08-11
B | 2021-08-12
B | 2021-08-13
B | 2021-08-14
B | 2021-08-15
B | 2021-08-16
B | 2021-08-17
B | 2021-08-18
B | 2021-08-19
B | 2021-08-20
B | 2021-08-21
B | 2021-08-22
B | 2021-08-23
B | 2021-08-24
我尝试使用以下查询来实现此目的 - 但是在尝试取消嵌套日期序列时出现错误
SELECT t.job_name, d.dte
FROM (SELECT job_name
, min(run_date) as mind
, max(run_date) as maxd
, SEQUENCE(min(run_date), max(run_date)) as date_arr
FROM job_log_table t
GROUP BY job_name
) jd
CROSS JOIN
UNNEST(jd.date_arr) d(dte)
LEFT JOIN job_log_table t
ON t.job_name = jd.job_name
AND t.latest_date = d.dte;
这会产生以下错误:
[HY000][100071] [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. [ErrorCategory:USER_ERROR, ErrorCode:SYNTAX_ERROR], Detail:SYNTAX_ERROR: line 5:14: Unexpected parameters (date, date) for function sequence. Expected: sequence(bigint, bigint, bigint) , sequence(bigint, bigint) , sequence(timestamp, timestamp, interval day to second) , sequence(timestamp, timestamp, interval year to month)
这是 Athena 的 Presto SQL 风格的限制还是我在某个地方犯了一个小学生错误?