如何使用脚本INTERVAL 1< day | week | month>生成? [英] How to generate with scripting INTERVAL 1 <day|week|month>?
本文介绍了如何使用脚本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标准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屋!
查看全文