如何用BigQuery模拟数据透视表? [英] How to simulate a pivot table with BigQuery?
问题描述
我需要在列中组织查询的结果,就好像它是一个数据透视表。我怎么做到这一点?
I need to organize the results of a query in columns, as if it was a pivot table. How can I do that?
推荐答案
使用条件语句将查询结果组织到行和列中。在下面的示例中,搜索以Google值开头的大多数修订维基百科文章的结果将组织到显示修订计数的列中(如果符合各种条件)。
Use conditional statements to organize the results of a query into rows and columns. In the example below, results from a search for most revised Wikipedia articles that start with the value 'Google' are organized into columns where the revision counts are displayed if they meet various criteria.
SELECT
page_title,
/* Populate these columns as True or False, depending on the condition */
IF(page_title CONTAINS 'search', INTEGER(total), 0) AS search,
IF(page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
FROM
/* Subselect to return top revised Wikipedia articles containing 'Google'
* followed by additional text.
*/
(SELECT
TOP(title, 5) as page_title,
COUNT(*) as total
FROM
[publicdata:samples.wikipedia]
WHERE
REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
);
结果:
Result:
+---------------+--------+------+
| page_title | search | geo |
+---------------+--------+------+
| Google search | 4261 | 0 |
| Google Earth | 0 | 3874 |
| Google Chrome | 0 | 0 |
| Google Maps | 0 | 2617 |
| Google bomb | 0 | 0 |
+---------------+--------+------+
一个类似的例子,不使用子查询:
A similar example, without using a subquery:
SELECT SensorType, DATE(DTimestamp), AVG(data) avg,
FROM [data-sensing-lab:io_sensor_data.moscone_io13]
WHERE DATE(DTimestamp) IN ('2013-05-16', '2013-05-17')
GROUP BY 1, 2
ORDER BY 2, 3 DESC;
生成3列表格:传感器类型,日期和平均数据。
Generates a 3 column table: sensor type, date, and avg data. To "pivot" and have the dates as columns:
SELECT
SensorType,
AVG(IF(DATE(DTimestamp) = '2013-05-16', data, null)) d16,
AVG(IF(DATE(DTimestamp) = '2013-05-17', data, null)) d17
FROM [data-sensing-lab:io_sensor_data.moscone_io13]
GROUP BY 1
ORDER BY 2 DESC;
这篇关于如何用BigQuery模拟数据透视表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!