我想制作一个度量数据检查表-下面是我现在所拥有的。我希望自动化";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
....
您可以使用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
)