sql - Google-Bigquery:整合聚合

Question

我正在尝试创建一个执行一些复杂操作的查询，但我一直无法找到任何可能为我指明正确方向的信息。也许您可以提供帮助!

这是源数据:

7457, "05:06:26 UTC", 15
7457, "05:06:26 UTC", 15
7457, "05:06:26 UTC", 15
7457, "05:06:26 UTC", 15 
2341, "05:12:34 UTC", 10
2341, "05:12:34 UTC", 10
2341, "05:12:34 UTC", 10
2341, "05:12:34 UTC", 10
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:34 UTC", 15
5678, "05:12:39 UTC", 15
5678, "05:12:39 UTC", 15
1111, "06:00:00 UTC", 10
2222, "07:00:00 UTC", 15
3333, "08:00:00 UTC", 10

我有一个查找重复统计信息的查询:

SELECT userID, timestamp, statType, COUNT(*) - 1 AS DuplCount
FROM [dataset1.table1] 
GROUP BY userID, timestamp, statType 
HAVING DuplCount > 0;

(请注意，只有具有相同用户 ID 和时间戳的统计信息才能被视为重复。)

这导致表格看起来像

userID  timestamp       statType    DuplCount    
7457    05:06:26 UTC    15          3    
2341    05:12:34 UTC    10          3    
5678    05:12:34 UTC    15          7     
5678    05:12:39 UTC    15          1   

我想进一步合并这些数据，以便它可以作为一行插入到另一个表中:相同 statType 的重复项计数的总和。我希望它看起来像

table            stat10DuplCount  stat15DuplCount    
dataset1.table1  3                11 

我不确定如何继续......这一切可以在一个查询中完成(首选)，还是我需要进行多个查询或进行一些查询后数据处理？

Answer 1

子查询:

SELECT "dataset1.table1" table, COUNT(IF(statType=10,1,null)) stat10DuplCount, COUNT(IF(statType=15,1,null)) stat15DuplCount
FROM (
    SELECT userID, timestamp, statType, COUNT(*) - 1 AS DuplCount
    FROM [dataset1.table1] 
    GROUP BY userID, timestamp, statType 
    HAVING DuplCount > 0
)

(如果您提供一个遍历公共(public)数据集的有效查询，或者发布您的数据样本，那么回答和测试总是更容易)

工作示例:

SELECT "dataset1.table1" tablename,
       COUNT(IF(statType=10,1,null)) stat10DuplCount,
       COUNT(IF(statType=15,1,null)) stat15DuplCount
FROM (SELECT 15 statType),(SELECT 10 statType),(SELECT 15 statType),(SELECT 15 statType)

tablename       stat10DuplCount stat15DuplCount  
dataset1.table1 1               3