时间戳

首页 > 解决方案 > 如何在 Presto SQL 中制作动态日期

sql - 如何在 Presto SQL 中制作动态日期

问题描述

我想制作一个指标数据检查表 - 下面是我现在所拥有的。我希望自动化部分“2021-04-30”，所以每次运行逻辑时，它都会动态更改为每个月的最后一天。（从 2021-01-31、2021-02-28、2021-03-31、......）

CREATE TABLE data_check_result AS
WITH day_count AS (
    SELECT day(date '2021-04-30' - date '2020-01-01') AS ideal_days 
)
, metric AS (
    SELECT country
        , day(max(datepartition)- date '2020-01-01') AS actual_has_days
    FROM table
    GROUP BY 1
)
SELECT date '2021-04-30' AS report_period
    , country
    , 'metric_a' AS metric_name
    , CASE WHEN metric.actual_has_days = day_count.ideal_days THEN 'YES' ELSE 'NO' END AS data_passed
FROM day_count
JOIN metric
    ON 1=1
;

理想输出表

report_period    country             metric_name    data_passed
2021-04-30       Australia           metric_a            YES
2021-04-30       Canada              metric_a            NO
2021-04-30       China               metric_a            YES
2021-03-31       US                  metric_a            NO
2021-03-31       Canada              metric_a            YES
....

标签： sqlfunctiondynamicautomationpresto

解决方案

您可以使用该last_day_of_month函数获取每个月的最后一天：

SELECT last_day_of_month(date('2021-' || cast(month as varchar) || '-01'))
FROM UNNEST(sequence(1,12)) t(month)

=>

   _col0
------------
 2021-01-31
 2021-02-28
 2021-03-31
 2021-04-30
 2021-05-31
 2021-06-30
 2021-07-31
 2021-08-31
 2021-09-30
 2021-10-31
 2021-11-30
 2021-12-31
(12 rows)

您可以以这种方式将它们放在一起（未经验证，但它应该是一个好的开始）：

CREATE TABLE data_check_result AS
WITH metric AS (
    SELECT country
        , day(max(datepartition)- date '2020-01-01') AS actual_has_days
    FROM table
    GROUP BY 1
),
last_days_of_month AS (
    SELECT last_day_of_month(date('2021-' || cast(month as varchar) || '-01')) last_day
    FROM UNNEST(sequence(1,12)) t(month)
)
SELECT
    last_day AS report_period
    , country
    , 'metric_a' AS metric_name
    , CASE WHEN metric.actual_has_days = day_count.ideal_days THEN 'YES' ELSE 'NO' END AS data_passed
FROM metric
   CROSS JOIN (
        SELECT last_day, day(last_day - date '2020-01-01') AS ideal_days
        FROM last_days_of_month
   )

推荐阅读