如何使用脚本生成 INTERVAL 1 <day|week|month>? [英] How to generate with scripting INTERVAL 1 <day|week|month>?

问题描述

我们试图找到一种语法来从日期函数的第三个参数生成 DAY|WEEK|MONTH 选项.

We are trying to find a syntax to generate the DAY|WEEK|MONTH options from the 3rd param of date functions.

DECLARE var_date_option STRING DEFAULT 'DAY';
select GENERATE_DATE_ARRAY('2019-01-01','2020-01-01',INTERVAL 1 WEEK)
                                            dynamic param here -^^^

您知道在 DECLARE 中使用的正确语法是什么吗?应该将其转换为有效的 SQL.

Do you know what's the proper syntax to use in DECLARE and that should be converted to valid SQL.

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard SQL

那些 DAY|WEEK|MONTH 是文字，不能参数化
而且，如您所知 - 动态 SQL 也不可用

Those DAY|WEEK|MONTH are LITERALs and cannot be parametrized
And, as you know - dynamic SQL is also not available yet

所以，不幸的是，下面是我今天能想到的唯一解决方案

So, unfortunately below is the only solution I can think of as of today

#standardSQL
DECLARE var_date_option STRING DEFAULT 'DAY';
DECLARE start_date, end_date DATE;
DECLARE date_array ARRAY<DATE>;

SET (start_date, end_date, var_date_option) = ('2019-01-01','2020-01-01', 'MONTH');

SET date_array = (
  SELECT CASE var_date_option 
    WHEN 'DAY' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)
    WHEN 'WEEK' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 WEEK)
    WHEN 'MONTH' THEN GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 MONTH)
  END
);

SELECT * FROM UNNEST(date_array) AS date_dt;

这篇关于如何使用脚本生成 INTERVAL 1 <day|week|month>?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！