时间戳

首页 > 解决方案 > 如何根据条件从 1 条记录创建更多记录（SQL）

sql - 如何根据条件从 1 条记录创建更多记录（SQL）

问题描述

我正在尝试根据某些条件从 1 条记录中创建更多记录。例如：如果用户购买了包月套餐，该表将创建每周的记录，直到结束日期。

从此表：

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |

对此：

|Date Purchased| Date Ended| Package|
|2019-1-1      | 2019-2-1  | 1Month |
|2019-1-8      | 2019-2-1  | 1Month |
|2019-1-15     | 2019-2-1  | 1Month |
|2019-1-23     | 2019-2-1  | 1Month |

关于如何在 SQL 中执行此操作的任何想法？谢谢

标签： sqlgoogle-bigqueryunion

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT * EXCEPT(day) REPLACE(day AS date_purchased)
FROM `project.dataset.table`, UNNEST((
  SELECT CASE package 
    WHEN 'Week' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 DAY)
    WHEN 'Month' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)
    WHEN 'Quarter' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
    WHEN 'Year' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 QUARTER)
    ELSE GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
  END  
)) day

您可以使用虚拟数据进行测试，在上面播放，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2019-01-01' date_purchased, DATE '2019-02-01' date_ended, 'Month' package UNION ALL
  SELECT '2019-01-01', '2019-03-31', 'Quarter' UNION ALL
  SELECT '2019-01-01', '2020-12-31', 'Year' 
)
SELECT * EXCEPT(day) REPLACE(day AS date_purchased)
FROM `project.dataset.table`, UNNEST((
  SELECT CASE package 
    WHEN 'Week' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 DAY)
    WHEN 'Month' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)
    WHEN 'Quarter' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
    WHEN 'Year' THEN GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 QUARTER)
    ELSE GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 MONTH)
  END  
)) day

结果 

Row date_purchased  date_ended  package  
1   2019-01-01      2019-02-01  Month    
2   2019-01-08      2019-02-01  Month    
3   2019-01-15      2019-02-01  Month    
4   2019-01-22      2019-02-01  Month    
5   2019-01-29      2019-02-01  Month    
6   2019-01-01      2019-03-31  Quarter  
7   2019-02-01      2019-03-31  Quarter  
8   2019-03-01      2019-03-31  Quarter  
9   2019-01-01      2020-12-31  Year     
10  2019-04-01      2020-12-31  Year     
11  2019-07-01      2020-12-31  Year     
12  2019-10-01      2020-12-31  Year     
13  2020-01-01      2020-12-31  Year     
14  2020-04-01      2020-12-31  Year     
15  2020-07-01      2020-12-31  Year     
16  2020-10-01      2020-12-31  Year

推荐阅读