
BigQuery(BQ) 对于数据分析或处理非常有用。
它擅长处理海量数据。 它在短时间内返回汇总结果。
通过数据处理,有时我们想将列更改为行。
但是为了使用 UNPIVOT
,我们必须写下每个列名。
很麻烦。
最好动态设置列。

所以今天我介绍一下“Bigquery 中多列的动态 unpivot SQL”。
作者

阅读优势
你可以理解“Bigquery 中多列的动态 unpivot SQL”。 那么你不必担心unpivot。
Bigquery 中的 UNPIVOT
我们可以像下面这样使用 Bigquery 的 UNPIVOT
。
首先,准备多列数据。
Data
WITH Produce AS ( SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL SELECT 'Apple', 77, 0, 25, 2) SELECT * FROM Produce +---------+----+----+----+----+ | product | Q1 | Q2 | Q3 | Q4 | +---------+----+----+----+----+ | Kale | 51 | 23 | 45 | 3 | | Apple | 77 | 0 | 25 | 2 | +---------+----+----+----+----+
然后我们将列设置为 UNPIVOT
运算符,如 IN (Q1, Q2, Q3, Q4)
。
Result
SELECT * FROM Produce UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4)) +---------+-------+---------+ | product | sales | quarter | +---------+-------+---------+ | Kale | 51 | Q1 | | Kale | 23 | Q2 | | Kale | 45 | Q3 | | Kale | 3 | Q4 | | Apple | 77 | Q1 | | Apple | 0 | Q2 | | Apple | 25 | Q3 | | Apple | 2 | Q4 | +---------+-------+---------+
Reference
但是这样一来,我们就得给每一个列名设置。
这需要时间。

Bigquery 中多列的动态 unpivot SQL
为了对多列进行逆透视,我们可以使用function。
该函数是 fhoffa.x.unpivot()
。
我们可以像下面这样使用它。
SQL
SELECT product, unpivotted FROM Produce a , UNNEST(fhoffa.x.unpivot(a, 'Q')) unpivotted
然后我们得到以下结果。
Result
product | unpivotted.key | unpivotted.value |
Kale | Q1 | 51 |
Kale | Q2 | 23 |
Kale | Q3 | 45 |
Kale | Q4 | 3 |
Apple | Q1 | 77 |
Apple | Q2 | 0 |
Apple | Q3 | 25 |
Apple | Q4 | 2 |
fhoffa.x.unpivot(a, 'Q')
的第二个参数决定列。
在这种情况下,名称中包含“Q”的列是目标列。
我们用 Q[1-9]
得到相同的结果。
此功能于 2020 年推出。
它是用户定义函数(UDF)。
源代码已打开。
所以你可以在你自己的项目中定义它。

结论
今天我解释了“Bigquery 中多列的动态逆透视 SQL”。
为了反透视多个列,我们可以使用 UDF fhoffa.x.unpivot()
。
使用正则表达式,我们可以选择很多列。

还有一些关于 BigQuey 的其他文章。
如果您对它们感兴趣,请阅读它们。