如何在 24 小时窗口内为每个唯一身份用户获得重复的转化

Question

我需要为每个唯一用户获取重复的转换。这里的规则是，我需要一列，我只获得一天内第一次转换的计数。因此，我可以在 2019 年 3 月 28 日触发 10 次转化，但"重复数据删除"列只会拉入 1 次转化。

这是我在 BigQuery 中的原始数据：

Date    User_ID
3/3/19  1234
3/3/19  1234
3/3/19  1234
3/3/19  12
3/3/19  12
3/4/19  1234
3/4/19  1234
3/5/19  1

我希望我的最终输出如下所示：

Date     User_ID   Total_Conversions   Deduped
3/3/19   1234      3                   1
3/3/19   12        2                   1
3/5/19   1         1                   1
3/4/19   1234      2                   1

Answer 1

下面是 BigQuery Standard SQL

#standardSQL
SELECT day, user_id, 
COUNT(1) total_conversions,
COUNT(DISTINCT user_id) deduped
FROM `project.dataset.table`
GROUP BY day, user_id   

如果将上述应用于您问题中的样本数据 - 结果是

Row day     user_id total_conversions   deduped  
1   3/4/19  1234    2                   1    
2   3/5/19  1       1                   1    
3   3/3/19  1234    3                   1    
4   3/3/19  12      2                   1   

注意：检查我在您的问题下方留下的评论！

如果我们在最终输出中没有日期列，

如果在代码中构造了重复数据删除的规则，其中分组将基于最终输出中没有"day"列的uder_id怎么办？

下面的示例执行此操作

#standardSQL
WITH `project.dataset.table` AS (
SELECT '3/3/19' day, 1234 user_id UNION ALL
SELECT '3/3/19', 1234 UNION ALL
SELECT '3/3/19', 1234 UNION ALL
SELECT '3/3/19', 12 UNION ALL
SELECT '3/3/19', 12 UNION ALL
SELECT '3/4/19', 1234 UNION ALL
SELECT '3/4/19', 1234 UNION ALL
SELECT '3/5/19', 1 
)
SELECT user_id, 
COUNT(1) total_conversions,
COUNT(DISTINCT day) deduped
FROM `project.dataset.table`
GROUP BY user_id   

有结果

Row user_id total_conversions   deduped  
1   12      2                   1    
2   1       1                   1    
3   1234    5                   2