如何在BigQuery中进行数据逆转操作？

Question

不确定要调用哪些函数，但转置是我能想到的最接近的东西。

我在BigQuery中有一个配置如下的表格：

但我想查询一个配置为以下方式的表格：

创建这个表的SQL代码是什么样子的？

谢谢！

- Ben Leathers

Answer 1

2021年更新:

BigQuery引入了新的UNPIVOT操作符。

在使用UNPIVOT将Q1、Q2、Q3和Q4旋转到销售额和季度列之前：

产品	Q1	Q2	Q3	Q4
Kale	51	23	45	3
Apple	77	0	25	2

使用UNPIVOT将Q1、Q2、Q3和Q4旋转到销售额和季度列后：

产品	销售额	季度
Kale	51	Q1
Kale	23	Q2
Kale	45	Q3
Kale	3	Q4
Apple	77	Q1
Apple	0	Q2
Apple	25	Q3
Apple	2	Q4

查询：

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
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

- Sergey Geron

---

为什么这个被投票否决了？我可以通过这种方式解决我的问题。 - andreas030241

这应该是现在被接受的答案。 - Benjamin Dubreu

如何动态地将除一个列之外的所有列添加到in()语句中？ - undefined

Answer 2

2020更新: fhoffa.x.unpivot()

请参见：

• https://medium.com/@hoffa/how-to-unpivot-multiple-columns-into-tidy-pairs-with-sql-and-bigquery-d9d0e74ce675

我创建了一个公共持久性UDF。如果你有一个名为a的表，你可以将整行数据传递给UDF以进行逆转置：

SELECT geo_type, region, transportation_type, unpivotted
FROM `fh-bigquery.public_dump.applemobilitytrends_20200414` a
  , UNNEST(fhoffa.x.unpivot(a, '_2020')) unpivotted

将这样的表格：

转化为这个样子：

---

正如评论所提到的，我的解决方案并未解决问题。

因此，这里有一个变化，同时我正在寻找如何将所有内容整合成一个：

CREATE TEMP FUNCTION unpivot(x ANY TYPE) AS (
(
  SELECT 
   ARRAY_AGG(STRUCT(
     REGEXP_EXTRACT(y, '[^"]+') AS key
   , REGEXP_EXTRACT(y, ':([0-9]+)') AS value
   ))
  FROM UNNEST((
    SELECT REGEXP_EXTRACT_ALL(json,'"[smlx][meaxl]'||r'[^:]+:\"?[^"]+?') arr
    FROM (SELECT TO_JSON_STRING(x) json))) y
)
);

SELECT location, unpivotted.*
FROM `robotic-charmer-726.bl_test_data.reconfiguring_a_table` x
  , UNNEST(unpivot(x)) unpivotted

使用表的 UNION（在 BigQuery 中使用“，”）以及一些列别名：

SELECT Location, Size, Quantity
FROM (
  SELECT Location, 'Small' as Size, Small as Quantity FROM [table]
), (
  SELECT Location, 'Medium' as Size, Medium as Quantity FROM [table]
), (
  SELECT Location, 'Large' as Size, Large as Quantity FROM [table]
)

- Felipe Hoffa

---

感谢@FelipeHoffa的快速和有用的回复！ 它不需要第二个和第三个“FROM”。所以最终它像这样工作： “SELECT Location，Size，Quantity FROM ( SELECT Location，'Small' as Size，Small as Quantity FROM [table] ), ( SELECT Location，'Medium' as Size，Medium as Quantity FROM [table] ), ( SELECT Location，'Large' as Size，Large as Quantity FROM [table] )”谢谢！ - Ben Leathers

正确！查询已修复（如果您将示例数据上传到BQ，则我可以在将其粘贴到此处之前测试我的查询） - Felipe Hoffa

好的，我该怎么指向我的BigQuery数据集呢？（目前我只使用Tableau和BigQuery Web UI进行查询，所以我不知道如何告诉别人如何访问我的数据集。谢谢！） - Ben Leathers

如果您将数据集公开，那么StackOverflow上的任何人都可以查看并提供帮助：）。例如，这是我8月份的维基百科页面浏览日志：https://bigquery.cloud.google.com/table/fh-bigquery:wikipedia.pagecounts_201408（项目：数据集.表） - Felipe Hoffa

感谢这个2020年的更新，祝贺你。只是要提醒一下，只要列没有嵌套在结构体内部，它就能正常工作。当键值开始出现问题时，实现仍然是一个巨大的灵感来源。再次感谢。 - David Mabodo

Answer 3

@Felipe，我尝试使用标准SQL，但在您的查询的第一行上出现错误，显示：“列名位置不明确 [1:8]”

我使用了另一种查询方法，对我有效：

"最初的回答"

SELECT Location, 'Small' as Size, Small as Quantity FROM `table`
UNION ALL
SELECT Location, 'Medium' as Size, Medium as Quantity FROM `table`
UNION ALL
SELECT Location, 'Large' as Size, Large as Quantity FROM `table`

- Amit

Answer 4

我有一个解决方案，使用STRUCT、ARRAY和CROSS JOIN+UNNEST：

WITH
  My_Table_Metrics_Data AS (
  SELECT
    ...,
    [
        STRUCT('...' AS Metric, ... AS Data),
        STRUCT('...' AS Metric, ... AS Data),
    ] AS Metrics_Data
  FROM
    `My_Dataset.My_Table`
  WHERE
    ...
  )
SELECT
  ...,
  Metric_Data
FROM
  My_Table_Metrics_Data
CROSS JOIN
  UNNEST(My_Table_Metrics_Data.Metrics_Data) AS Metric_Data

完整的解释和指示：https://yuhuisdatascienceblog.blogspot.com/2018/06/how-to-unpivot-table-in-bigquery.html

- user11765613