在 BigQuery 中执行循环 [英] Do loop in BigQuery

问题描述

我有一个在 BigQuery 中看起来像这样的表:

I have a table that looks like this in BigQuery:

Type    Start_Date  End_Date   Total_Spend
TV      20180101   20180131    10000
Radio   20180107   20180207    5000

我想运行一个查询，该查询运行一个循环，该循环创建一个表，将每一行拆分为一个日常表:

And I want to run a query that runs a loop that creates a table that splits each row into a daily table:

Type     Date      Spend
TV       20180101   322,6
TV       20180102   322,6
TV       20180103   322,6
TV       20180104   322,6
TV       20180105   322,6
TV       20180106   322,6
TV       20180107   322,6
...
TV       20180131   322,6
Radio    20180107   161,3
Radio    20180108   161,3
Radio    20180109   161,3
...
Radio    20180207   161,3

基本上我想创建的 do 循环是这样的:

Basically the do loop I want to create is like this:

do date=start_date to end_date;
    spend = total_spend/(end_date-start_date+1);

但我不知道如何在 BigQuery 中这样做.有什么想法或建议吗?

But I do not know how to do so in BigQuery. Any ideas or suggestions?

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT type, FORMAT_DATE('%Y%m%d', day) day, 
  ROUND(Total_Spend / ARRAY_LENGTH(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))), 2) Spend
FROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))) day

您可以使用您的问题中的示例数据进行测试和使用，如下例所示

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'TV' Type, '20180101' Start_Date, '20180131' End_Date, 10000 Total_Spend UNION ALL
  SELECT 'Radio', '20180107', '20180207', 5000 
)
SELECT type, FORMAT_DATE('%Y%m%d', day) day, 
  ROUND(Total_Spend / ARRAY_LENGTH(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))), 2) Spend
FROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y%m%d', Start_Date), PARSE_DATE('%Y%m%d', End_Date))) day
-- ORDER BY Type, day

这篇关于在 BigQuery 中执行循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！