如何使用SQL复制和转换BigQuery表中的行?

Question

我在BQ中有一个表，看起来像这样:

<表类> 名称 城市 性别 日期 tbody><<tr>Alan丹佛M2021-01-11大卫亚特兰大M2021-01-11达西纽约道明>2021-01-11

您可以像下面这样通过与扁平日期数组交叉连接来复制一行。

WITH sample_table AS (
SELECT 'Alan' Name,  'Denver' city,  'M' Gender, DATE '2021-01-11' Date UNION ALL
SELECT 'David' Name, 'Atlanta' city, 'M' Gender, DATE '2021-01-11' Date UNION ALL
SELECT 'Darcy' Name, 'NY' city,      'F' Gender, DATE '2021-01-11' Date
)
SELECT t.* EXCEPT(date), date 
FROM sample_table t, UNNEST(GENERATE_DATE_ARRAY(date, date + 2)) date;
--Query results
+-------+---------+--------+------------+
| Name  |  city   | Gender |    date    |
+-------+---------+--------+------------+
| Alan  | Denver  | M      | 2021-01-11 |
| Alan  | Denver  | M      | 2021-01-12 |
| Alan  | Denver  | M      | 2021-01-13 |
| David | Atlanta | M      | 2021-01-11 |
| David | Atlanta | M      | 2021-01-12 |
| David | Atlanta | M      | 2021-01-13 |
| Darcy | NY      | F      | 2021-01-11 |
| Darcy | NY      | F      | 2021-01-12 |
| Darcy | NY      | F      | 2021-01-13 |
+-------+---------+--------+------------+