我正在使用Google Big Query，并且试图从公共样本数据集中得出重要的结果.
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.
现在我要做的是，以这种方式从表中获取结果:如果该单词很勇敢，请选择勇敢"作为column_1，如果该单词已有人参与，请选择"ATTENDED"作为column_2，然后进行汇总这些2的字数.
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;但是，此查询返回数据
我要找的是
我知道此数据集的枢纽没有意义.但我仅以此为例来说明问题.如果您能为我提供一些指导，那就太好了.
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.
我还提到了如何使用BigQuery?，似乎也有我在这里提到的相同问题.
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.
推荐答案更新2020:
只需调用fhoffa.x.pivot()，如这篇文章中所述:
Just call fhoffa.x.pivot(), as detailed in this post:
- medium/@hoffa /easy-pivot-in-bigquery-one-step-5a1f13c6c710
对于2019年的示例，例如:
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 );更新2019:
由于这是一个很普遍的问题，所以让我更新为#standardSQL和更通用的透视案例.在这种情况下，我们有多行，并且每个传感器都查看不同类型的属性.要对其进行调整，我们将执行以下操作:
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
作为AVG()的替代方法，您可以尝试MAX()，ANY_VALUE()等.
As an alternative to AVG() you can try MAX(), ANY_VALUE(), etc.
以前:
我不确定您要做什么，但是:
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 )