时间戳

首页 > 解决方案 > 如何计算表列GoogleSQL Bigquery中单词的频率

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

问题描述

例如，我有一个表格中的一列，其中包含某些单词。

MyTable

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

现在我想从该表中搜索一组单词，例如 [Ipad,android,laptop] 并计算其频率。

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

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

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

标签： sqlgoogle-bigquery

解决方案

以下是 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. 删除尾随空格和小写以解决区分大小写的问题
  5. 将这些设备留在预定义列表中的输出中

推荐阅读