如何在大查询中转换表格 [英] How to Pivot table in Big Query

问题描述

我对Google Big Query并不陌生，我试图从公开的示例数据集中获得一个pivoted结果。

对现有表的简单查询是

  SELECT * 
 FROM publicdata：samples.shakespeare 
 LIMIT 10; 
  

此查询返回以下结果集。

现在我在做什么试图做的是从表中得到结果，如果单词很勇敢，选择BRAVE作为column_1，如果出现该单词，请选择ATTENDED作为column_2，然后汇总这两个单词的计数。

以下是我正在使用的查询。

  SELECT 
（CASE WHEN'word'='brave'THEN'BRAVE'ELSE''END）as column_1，
（CASE when case ='attend'THEN'ATTENDED'ELSE''END）as column_2，
 SUM（word_count）
 FROM publicdata：samples.shakespeare 
 WHERE（word ='brave'OR word ='attend'）
 GROUP BY column_1，column_2 
 LIMIT 10; 
  

但是，这个查询返回数据

我在找什么因为是

我知道这个数据集的支点没有意义。但我只是以此为例来解释这个问题。如果你能为我提供一些方向，这将是非常棒的。

编辑：我也提到如何用BigQuery模拟数据透视表？，它似乎也有我在这里提到的相同问题。

b $ b

解决方案

我不确定你在做什么，但：

<$ p $ （1，字）WITHIN RECORD column_1，NTH（2，字）WITHIN RECORD column_2，f0_
FROM（
SELECT NEST（字）字，SUM（c， ）
FROM（
SELECT word，SUM（word_count）c
FROM publicdata：samples.shakespeare
（'勇敢'，'出席'）
BY 1
）
）

更新：相同的结果，更简单的查询：

  SELECT NTH（1，word）column_1，NTH（2，字）column_2，SUM（c）
 FROM（
 SELECT word，SUM（word_count）c 
 FROM publicdata：samples.shakespeare 
（'brave'，' '）
 GROUP BY 1 
）
  

I am little new to Google Big Query and I am trying to get a pivoted result out from public sample data set.

A simple query to 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.

解决方案

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
)

这篇关于如何在大查询中转换表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！