英文:
Use PIVOT in BIGQUERY with a multi-column ARRAY STRUCT
问题
我有一个在BIGQUERY中的表，有三列，我正在对第一列进行数据透视，但我需要使用其他两列创建一个数组：
这是我的表：
| fecData | total | name |
|---|---|---|
| 202301 | 250 | first |
| 202302 | 50 | sec |
| 202303 | 100 | th |
这是带有值的查询：
WITH initTable AS (
SELECT '202301' AS fecData, 250 AS total, 'first' as name
UNION ALL
SELECT '202302' AS fecData, 50 AS total, 'sec' as name
UNION ALL
SELECT '202303' AS fecData, 100 AS total, 'th' as name
),
pivotTable AS (
SELECT *
FROM initTable
PIVOT (
MAX(total)
FOR fecData IN ('202301', '202302', '202303')
)
)
SELECT *
FROM pivotTable
这是我得到的结果：
| name | 202301 | 202302 | 202303 |
|---|---|---|---|
| first | 250 | null | null |
| sec | null | 50 | null |
| th | null | null | 100 |
这是我需要的：
| 202301.total | 202301.name | 202302.total | 202302.name | 202303.total | 202303.name |
|---|---|---|---|---|---|
| 250 | first | 50 | sec | 100 | th |
我需要将fecData的值转换为包含total和name列及其相应值的RECORD REPEATED列。我无法在透视列上应用ARRAY(STRUCT)。
英文:
I have a table in BIGQUERY with three columns, I'm pivoting the first column but I need an ARRAY to be formed with the other two columns:
This is my table:
| fecData | total | name |
|---|---|---|
| 202301 | 250 | first |
| 202302 | 50 | sec |
| 202303 | 100 | th |
This is the query with the values:
WITH initTable AS (
SELECT '202301' AS fecData, 250 AS total, 'first' as name
UNION ALL
SELECT '202302' AS fecData, 50 AS total, 'sec' as name
UNION ALL
SELECT '202303' AS fecData, 100 AS total, 'th' as name
),
pivotTable AS (
SELECT *
FROM initTable
PIVOT (
MAX(total)
FOR fecData IN ('202301', '202302', '202303')
)
)
SELECT *
FROM pivotTable
This is what I get:
| name | 202301 | 202302 | 202303 |
|---|---|---|---|
| first | 250 | null | null |
| sec | null | 50 | null |
| th | null | null | 100 |
This is what I need:
| 202301.total | 202301.name | 202302.total | 202302.name | 202303.total | 202303.name |
|---|---|---|---|---|---|
| 250 | first | 50 | sec | 100 | th |
I need to convert the fecData values into RECORD REPEATED columns that contain the total and name columns with their respective values. I can't apply an ARRAY(STRUCT on the pivoted columns
答案1
得分: 1
以下是已翻译的代码部分：
您可以在[PIVOT][1]中包括多个聚合函数。因此，您可以尝试以下内容
WITH initTable AS (
-- 在此处放入示例数据
),
pivotTable AS (
SELECT * FROM initTable
PIVOT (
MAX(total) total, ANY_VALUE(name) name
FOR fecData IN ('202301', '202302', '202303')
)
)
SELECT * FROM pivotTable
查询结果
| total_202301 | name_202301 | total_202302 | name_202302 | total_202303 | name_202303 |
|---|---|---|---|---|---|
| 250 | first | 50 | sec | 100 | th |
或者您也可以考虑以下方式。
pivotTable AS (
SELECT * FROM initTable
PIVOT (
ANY_VALUE(STRUCT(total, name))
FOR fecData IN ('202301', '202302', '202303')
)
)
查询结果
请注意，这是代码的翻译部分，没有其他内容。
<details>
<summary>英文:</summary>
You can include multiple aggregation functions in the [PIVOT][1]. So, you can try below
```sql
WITH initTable AS (
-- put sample data here
),
pivotTable AS (
SELECT * FROM initTable
PIVOT (
MAX(total) total, ANY_VALUE(name) name
FOR fecData IN ('202301', '202302', '202303')
)
)
SELECT * FROM pivotTable
Query results
| total_202301 | name_202301 | total_202302 | name_202302 | total_202303 | name_202303 |
|---|---|---|---|---|---|
| 250 | first | 50 | sec | 100 | th |
or you can consider below as well.
pivotTable AS (
SELECT * FROM initTable
PIVOT (
ANY_VALUE(STRUCT(total, name))
FOR fecData IN ('202301', '202302', '202303')
)
)
Query results
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论