时间戳

首页 > 解决方案 > 谷歌大查询 - 点后拆分并合并结果

sql - 谷歌大查询 - 点后拆分并合并结果

问题描述

我正在尝试通过来自 Firebase 数据集的谷歌大查询来获得一些具有移动操作系统版本的用户。

我的输入是：

Row versions users   
1   12.1.2  27984    
2   12.1    5702     
3   11.4.1  1354     
4   12.0.1  1325     
5   12.1.1  837  
6   12.0    685  
7   10.3.3  484  
8   12.1.3  411  
9   11.4    353  
10  11.3.1  184  
11  11.2.6  140

我想要的输出是：

versions users
12       34000
11       23000
10       10000

基本上，我想知道有多少用户使用我们的 12、11、10、.. 版本。我不介意详细的版本。

总的来说，凭借我的 SQL 技能，我来到了这里：

SELECT device.operating_system_version as versions, count(distinct(user_pseudo_id)) as users
FROM [my-table:analytics_188188188.events_20190102] 
WHERE device.operating_system = "IOS" and device.operating_system_version LIKE '10.%'
GROUP BY versions
ORDER BY users desc
LIMIT 1000

它给了我很好的结果：

Row versions    users    
1   10.3.3  484  
2   10.2.1  53   
3   10.3.2  40   
4   10.2    31   
5   10.1.1  20   
6   10.3.1  18   
7   10.0.2  15   
8   10.0.1  5    
9   10.3    5    
10  10.0.3  2

但是在这里，我不知道接下来我应该做什么。我可以想象我必须（以某种方式）修剪点之后的所有内容，然后对用户数量求和，但该怎么做呢？你能帮我吗？

标签： sqlgoogle-bigquery

解决方案

您需要提取主要版本。这是一种方法：

SELECT REGEXP_EXTRACT(e.device.operating_system_version, '^[^.]*') as version,
       COUNT(DISTINCT e.user_pseudo_id) as users
FROM [air-bank-prod:analytics_188576439.events_20190102] e
WHERE e.device.operating_system = 'IOS' AND 
      e.device.operating_system_version LIKE '10.%'
GROUP BY version
ORDER BY users desc
LIMIT 1000;

注意：对于您的特定示例-由于LIKE条件-您可以只取前两个字符

SELECT SUBSTR(e.device.operating_system_version, 1, 2) as version,

推荐阅读