Bigquery 中多列的动态 unpivot SQL

将每一列写入 unpivot 子句对我来说是一项艰巨的工作...

BigQuery(BQ) 对于数据分析或处理非常有用。

它擅长处理海量数据。 它在短时间内返回汇总结果。

通过数据处理，有时我们想将列更改为行。

但是为了使用 UNPIVOT，我们必须写下每个列名。

很麻烦。

最好动态设置列。

有没有更简单的反旋转方法？

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

作者

中级工程师（AI、系统）。 擅长Python和SQL。

Bigquery 中的 UNPIVOT

我们可以像下面这样使用 Bigquery 的 UNPIVOT。

首先，准备多列数据。

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)。

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      |
+---------+-------+---------+

但是这样一来，我们就得给每一个列名设置。

这需要时间。

我想要更简单的方法。

Bigquery 中多列的动态 unpivot SQL

为了对多列进行逆透视，我们可以使用function。

该函数是 fhoffa.x.unpivot()。

我们可以像下面这样使用它。

SELECT product, unpivotted
FROM Produce a 
  , UNNEST(fhoffa.x.unpivot(a, 'Q')) 
unpivotted

然后我们得到以下结果。

fhoffa.x.unpivot(a, 'Q') 的第二个参数决定列。

在这种情况下，名称中包含“Q”的列是目标列。

我们用 Q[1-9] 得到相同的结果。

此功能于 2020 年推出。

它是用户定义函数（UDF）。

源代码已打开。
所以你可以在你自己的项目中定义它。

现在我们可以对多个列使用相同的 SQL。

结论

今天我解释了“Bigquery 中多列的动态逆透视 SQL”。

为了反透视多个列，我们可以使用 UDF fhoffa.x.unpivot()。

使用正则表达式，我们可以选择很多列。

现在我们不必设置每个列名了。

还有一些关于 BigQuey 的其他文章。

如果您对它们感兴趣，请阅读它们。