如何计算表列GoogleSQL Bigquery中单词的频率

Question

例如，我在表中有一个列，其中包含特定的单词。

MyTable

 Name    device_Used
-----  ----------------  
 a       iphone, Ipad, laptop
 b       mobile, laptop, android, desktop
 c       android, desktop, washing machine

现在我想从那张桌子上搜索一组单词，比如Ipad，android，笔记本电脑，并计算其频率。

我试着通过阅读一些在线解决方案并为自己创建一个解决方案来实现这一点，但却遇到了一些错误。这就是我尝试过的。

WITH words AS (
  SELECT word
  FROM UNNEST([Ipad,android,laptop,desktop]) word
),
listcolumn AS (
  SELECT device
  FROM UNNEST(MyTable.device_Used) device
)
SELECT device, COUNT(word) AS cnt
FROM listcolumn
JOIN words
ON (device <> "")
GROUP BY device
ORDER BY cnt DESC;

我想创建一个新的表结果如下:-

   device_Used      count
---------------   -----------
Ipad                1            
android             2
laptop              2
desktop             2

由于我是新手，您可以建议一个新代码，因为我正在使用的代码是通过阅读在线建议来操作的。

Answer 1

下面是用于BigQuery标准SQL的

#standardSQL
SELECT TRIM(LOWER(device_Used)) AS device_Used, COUNT(1) `count`
FROM `project.dataset.table`,
UNNEST(SPLIT(device_Used)) device_Used
GROUP BY device_Used
HAVING LOWER(device_Used) IN ('ipad','android','laptop')

如果要应用于问题中的样本数据，请参见下面的示例

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' Name, 'iphone, Ipad, laptop' device_Used UNION ALL
  SELECT 'b', 'mobile, laptop, android, desktop' UNION ALL
  SELECT 'c', 'android, desktop, washing machine' 
)
SELECT TRIM(LOWER(device_Used)) AS device_Used, COUNT(1) `count`
FROM `project.dataset.table`,
UNNEST(SPLIT(device_Used)) device_Used
GROUP BY device_Used
HAVING LOWER(device_Used) IN ('ipad','android','laptop')

结果是

Row device_Used count    
1   ipad        1    
2   laptop      2    
3   android     2     

如果你能提供注释什么行做什么对

有很大的帮助

SELECT TRIM(LOWER(device_Used)) AS device_Used, COUNT(1) `count` -- (4) 
FROM `project.dataset.table`, -- (1)
UNNEST(SPLIT(device_Used)) device_Used -- (2)  
GROUP BY device_Used -- (3) 
HAVING LOWER(device_Used) IN ('ipad','android','laptop') -- (5)    

1. 从表
2. 中选择，将逗号分隔的列表转换为数组，并根据非嵌套数组
3. 的元素取消
4. 组，删除尾随空格和小写，以解决大小写敏感性

<代码>H 115>仅在预定义列表H 216G 217中的输出设备