sql - 生成最小和最大日期之间的日期范围 Athena presto SQL 序列错误
问题描述
unnest
我正在尝试在 Presto SQL (Athena) 中使用和在 postgres 中sequence
类似的东西生成一系列日期。generate_series
我的桌子看起来像
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 风格的限制,还是我在某个地方犯了一个男生错误?
解决方案
您需要提供interval
生成日期序列(在这种情况下interval '1' day
):
WITH dataset AS (
SELECT *
FROM
( VALUES
('A', DATE '2021-08-21'), ('A', DATE '2021-08-25'),
('B', DATE '2021-08-07'), ('B', DATE '2021-08-24')
) AS d (job_name, run_date)
)
select job_name, sequence(min(run_date), max(run_date), interval '1' day) seq
from dataset
group by job_name
输出:
工作名称 | 序列 |
---|---|
一种 | [2021-08-21 00:00:00.000, 2021-08-22 00:00:00.000, 2021-08-23 00:00:00.000, 2021-08-24 00:00:00.000, 2021-08-25 00:00:00.000] |
乙 | [2021-08-07 00:00:00.000, 2021-08-08 00:00:00.000, 2021-08-09 00:00:00.000, 2021-08-10 00:00:00.000, 2021-08-11 00:00:00.000, 2021-08-12 00:00:00.000, 2021-08-13 00:00:00.000, 2021-08-14 00:00:00.000, 2021-08-15 00:00:00.000, 2021-08-16 00:00:00.000, 2021-08-17 00:00:00.000, 2021-08-18 00:00:00.000, 2021-08-19 00:00:00.000, 2021-08-20 00 :00:00.000, 2021-08-21 00:00:00.000, 2021-08-22 00:00:00.000, 2021-08-23 00:00:00.000, 2021-08-24 00:00:00.000] |
推荐阅读
- php - 用于 Laravel Nova 中同一模型上的两个资源字段的 relatableQuery()
- javascript - 循环多次返回相同的值
- aws-amplify - 放大在哪里抓取`amplify env pull的值
--restore` 来自? - next.js - 现在如何将 next.js + express 应用程序部署到 Zeit UnhandledPromiseRejectionWarning noserverpages built
- spring - 如何根据请求访问 Spring 应用程序配置值?
- reactjs - react-router location.pathname 例如 /users/ 匹配 /users/profiles 和 /users/details。/users/* 如果可以
- android - 片段中的条件导航仅触发一次
- reactjs - 将背景框与画布上的文本对齐
- react-native - 用世博相机拍照
- plotly - 具有全息视图和绘图的网络图