sql - SQL - Vertica:如何使用最近的日期数据生成每日行
问题描述
我有一个如下的基表:
score_upd (Upd_dt,Url,Score) AS (
SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
)
Upd_dt URL Score
2019-07-26 A x
2019-07-26 B alpha
2019-08-01 A y
2019-08-01 B beta
2019-08-03 A z
2019-08-03 B gamma
我想在daily-url级别创建一个表,使用新行的大多数先前日期的值,结果应如下所示:
score_upd (Upd_dt,Url,Score) AS (
SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-07-27','A','x'
UNION ALL SELECT DATE '2019-07-27','B','alpha'
UNION ALL SELECT DATE '2019-07-28','A','x'
UNION ALL SELECT DATE '2019-07-28','B','alpha'
UNION ALL SELECT DATE '2019-07-29','A','x'
UNION ALL SELECT DATE '2019-07-29','B','alpha'
UNION ALL SELECT DATE '2019-07-30','A','x'
UNION ALL SELECT DATE '2019-07-30','B','alpha'
UNION ALL SELECT DATE '2019-07-31','A','x'
UNION ALL SELECT DATE '2019-07-31','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-02','A','y'
UNION ALL SELECT DATE '2019-08-02','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
UNION ALL SELECT DATE '2019-08-04','A','z'
UNION ALL SELECT DATE '2019-08-04','B','gamma'
UNION ALL SELECT DATE '2019-08-05','A','z'
UNION ALL SELECT DATE '2019-08-05','B','gamma'
)
看起来像:
Upd_dt URL Score
2019-07-26 A x
2019-07-26 B alpha
2019-07-27 A x
2019-07-27 B alpha
2019-07-28 A x
2019-07-28 B alpha
2019-07-29 A x
2019-07-29 B alpha
2019-07-30 A x
2019-07-30 B alpha
2019-07-31 A x
2019-07-31 B alpha
2019-08-01 A y
2019-08-01 B beta
2019-08-02 A y
2019-08-02 B beta
2019-08-03 A z
2019-08-03 B gamma
2019-08-04 A z
2019-08-04 B gamma
2019-08-05 A z
2019-08-05 B gamma
.
.
.
目前的流程是:我从 2019 年 7 月 26 日到今天建立了一个每日维度表:
/* SELECT CAST(slice_time AS DATE) 日期从 testcalendar mtc TIMESERIES slice_time as '1 day' OVER (ORDER BY CAST(mtc.dates as TIMESTAMP)); */
所以我得到:
日期
2019-07-26
2019-07-27
2019-07-28
2019-07-29
.
.
.
2019-10-12(今天)
我在想我是否可以使用诸如“插入前一个值”之类的函数按日期加入我的第一个表,通过使用大多数前一个日期数据中的值来生成缺失的天数,而它失败了。
结果未生成缺失天数的行。
如果有人对此有更好的想法,请告诉我。
谢谢!
解决方案
作为一个开始的警告:只有在真正需要时才存储“每日照片”。在我的过去,我曾经每年有太多 364 行,因为这些值每年只改变一次。在 Vertica 中,这需要许可证、CPU 和时钟时间来加入和分组......
但是,对于其余的 - 良好的开端。
但是您可以应用 TIMESERIES 而无需构建日历。
诀窍是手动“推断”您可以INTERPOLATE
自动执行的操作。
添加一个内联“填充”表,其中包含每个 URL 的最新值,但使用 Vertica 特有的分析限制子句CURRENT_DATE
而不是最新的实际日期。 LIMIT 1 OVER(PARTITION BY url ORDER BY upd_dt DESC)
使用您的输入 UNION SELECT 填充表,并将 TIMESERIES 子句应用于该 UNION SELECT。
像这样:
WITH
-- your input ...
score_upd (Upd_dt,Url,Score) AS (
SELECT DATE '2019-07-26','A','x'
UNION ALL SELECT DATE '2019-07-26','B','alpha'
UNION ALL SELECT DATE '2019-08-01','A','y'
UNION ALL SELECT DATE '2019-08-01','B','beta'
UNION ALL SELECT DATE '2019-08-03','A','z'
UNION ALL SELECT DATE '2019-08-03','B','gamma'
)
-- real WITH clause would start here ...
,
-- newest row per Url, just with current date
pad_newest AS (
SELECT
CURRENT_DATE
, url
, score
FROM score_upd
LIMIT 1 OVER(PARTITION BY url ORDER BY upd_dt DESC)
)
,
with_newest AS (
SELECT
*
FROM score_upd
UNION ALL
SELECT *
FROM pad_newest
)
SELECT
ts_dt::DATE AS upd_dt
, url AS url
, TS_FIRST_VALUE(score) AS score
FROM with_newest
TIMESERIES ts_dt AS '1 day' OVER (
PARTITION BY url ORDER BY upd_dt::TIMESTAMP
)
ORDER BY 1,2
;
推荐阅读
- angular-material - 角度材料设计中的运行时样式
- serialization - 我可以逐字节提取的序列化器?
- mysql - MySQL删除列中n个单词后的所有内容
- python - 使用烧瓶和 Python 的动态路由不起作用
- docker - 在 docker 容器中运行代理服务
- c# - Windows 身份验证 - 以编程方式注销用户?
- mysql - 提交容器后,docker容器中的MySQL服务器不起作用
- php - 如何在公共场合获取存储文件的名称 - laravel 5
- xml - 如何在此 XML/XSLT 中去除/添加名称空间/前缀?
- gcc - 使用 gcc 编译时没有创建分析数据