如何在 BigQuery 中透视表 [英] How to Pivot table in BigQuery
问题描述
我正在使用 Google Big Query，并且我正在尝试从公共示例数据集中获得一个透视结果.
对现有表的简单查询是:
SELECT *来自 publicdata:samples.shakespeare限制 10；此查询返回以下结果集.
现在我想要做的是，从表格中获取结果，如果这个词是勇敢的，选择BRAVE"作为 column_1，如果这个词被出席，选择ATTENDED"作为 column_2，然后聚合这两个的字数.
这是我正在使用的查询.
SELECT(CASE WHEN word = 'brave' THEN 'BRAVE' ELSE '' END)AS column_1，(CASE WHEN word = 'attended' THEN 'ATTENDED' ELSE '' END)AS column_2，总和(字数)来自 publicdata:samples.shakespeareWHERE(字 = '勇敢' 或字 = '参加')GROUP BY column_1, column_2限制 10；但是，这个查询返回数据
我正在寻找的是
我知道这个数据集的这个支点没有意义.但我只是以此为例来说明问题.如果你能帮我指点迷津，那就太好了.
我还提到了
作为 AVG() 的替代方案，您可以尝试 MAX()、ANY_VALUE() 等
以前:
我不确定您要做什么，但是:
SELECT NTH(1, words) WITHIN RECORD column_1, NTH(2, words) WITHIN RECORD column_2, f0_从 (SELECT NEST(word) 单词，SUM(c)从 (选择单词，SUM(word_count) c来自 publicdata:samples.shakespeareWHERE word in ('brave', 'attended')按 1 分组))更新:相同的结果，更简单的查询:
SELECT NTH(1, word) column_1, NTH(2, word) column_2, SUM(c)从 (选择单词，SUM(word_count) c来自 publicdata:samples.shakespeareWHERE word in ('brave', 'attended')按 1 分组)I am using Google Big Query, and I am trying to get a pivoted result out from public sample data set.
A simple query to an existing table is:
SELECT *
FROM publicdata:samples.shakespeare
LIMIT 10;
This query returns following result set.
Now what I am trying to do is, get the results from the table in such way that if the word is brave, select "BRAVE" as column_1 and if the word is attended, select "ATTENDED" as column_2, and aggregate the word count for these 2.
Here is the query that I am using.
SELECT
(CASE WHEN word = 'brave' THEN 'BRAVE' ELSE '' END) AS column_1,
(CASE WHEN word = 'attended' THEN 'ATTENDED' ELSE '' END) AS column_2,
SUM (word_count)
FROM publicdata:samples.shakespeare
WHERE (word = 'brave' OR word = 'attended')
GROUP BY column_1, column_2
LIMIT 10;
But, this query returns the data
What I was looking for is
I know this pivot for this data set does not make sense. But I am just taking this as an example to explain the problem. It will be great if you can put in some directions for me.
EDITED: I also referred to How to simulate a pivot table with BigQuery? and it seems it also has the same issue I mentioned here.
Update 2020:
Just call fhoffa.x.pivot(), as detailed in this post:
For the 2019 example, for example:
CREATE OR REPLACE VIEW `fh-bigquery.temp.a` AS (
SELECT * EXCEPT(SensorName), REGEXP_REPLACE(SensorName, r'.*/', '') SensorName
FROM `data-sensing-lab.io_sensor_data.moscone_io13`
);
CALL fhoffa.x.pivot(
'fh-bigquery.temp.a'
, 'fh-bigquery.temp.delete_pivotted' # destination table
, ['MoteName', 'TIMESTAMP_TRUNC(Timestamp, HOUR) AS hour'] # row_ids
, 'SensorName' # pivot_col_name
, 'Data' # pivot_col_value
, 8 # max_columns
, 'AVG' # aggregation
, 'LIMIT 10' # optional_limit
);
Update 2019:
Since this is a popular question, let me update to #standardSQL and a more general case of pivoting. In this case we have multiple rows, and each sensor looks at a different type of property. To pivot it, we would do something like:
#standardSQL
SELECT MoteName
, TIMESTAMP_TRUNC(Timestamp, hour) hour
, AVG(IF(SensorName LIKE '%altitude', Data, null)) altitude
, AVG(IF(SensorName LIKE '%light', Data, null)) light
, AVG(IF(SensorName LIKE '%mic', Data, null)) mic
, AVG(IF(SensorName LIKE '%temperature', Data, null)) temperature
FROM `data-sensing-lab.io_sensor_data.moscone_io13`
WHERE MoteName = 'XBee_40670F5F'
GROUP BY 1, 2
As an alternative to AVG() you can try MAX(), ANY_VALUE(), etc.
Previously:
I'm not sure what you are trying to do, but:
SELECT NTH(1, words) WITHIN RECORD column_1, NTH(2, words) WITHIN RECORD column_2, f0_
FROM (
SELECT NEST(word) words, SUM(c)
FROM (
SELECT word, SUM(word_count) c
FROM publicdata:samples.shakespeare
WHERE word in ('brave', 'attended')
GROUP BY 1
)
)
UPDATE: Same results, simpler query:
SELECT NTH(1, word) column_1, NTH(2, word) column_2, SUM(c)
FROM (
SELECT word, SUM(word_count) c
FROM publicdata:samples.shakespeare
WHERE word in ('brave', 'attended')
GROUP BY 1
)
这篇关于如何在 BigQuery 中透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
