如何在BigQuery中进行数据分组

Question

我有需要分组的数据库列表。我已经通过使用R成功地做到了这一点，但现在我必须通过使用BigQuery来做到这一点。数据如下表所示

|category|sub_category|date|day|timestamp|type|cpc|gmv||----------|---------------|------------|---------------||||ABC | ABC-1 | 2020年2月17日|周一|晚上11:37:36 | BI | 1.94 | 252293||ABC | ABC-1 | 2020年2月17日|周一|晚上11:37:39 | RT | 1.94 | 252293||ABC | ABC-1 | 2020年2月17日|周一|晚上11:38:29 | RT | 1.58 | 205041||ABC | ABC-1 | 2020年2月18日|星期二|凌晨12:05:14 | BI | 1.6 | 208397||ABC | ABC-1 | 2020年2月18日|星期二|凌晨12:05:18 | RT | 1.6 | 208397||ABC | ABC-1 | 2020年2月18日|星期二|凌晨12:05:52 | RT | 1.6 | 208397||ABC | ABC-1 | 2020年2月18日|周二上午12:06:33 | BI | 1.55 | 201354||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:55:47 | PP |1 | 129282||XYZ | XYZ-1 | 2020年2月17日|周一|下午11:56:23 | PP | 0.98 | 126928||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:57:19 | PP | 0.98 | 126928||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:57:34 | PP | 0.98 | 126928||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:58:46 | PP | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:59:27 | PP | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一|晚上11:59:51 | RT | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一|凌晨12:00:57 | BI | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一|凌晨12:01:11 | PP | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一|凌晨12:03:01 | PP | 0.89 | 116168||XYZ | XYZ-1 | 2020年2月17日|周一| 12:12:42 AM | RT | 1.19 | 154886 |

我想对行进行分组。具有<=的行与下一行的8分钟时间戳差异将被分组为一行，输出示例如下：

|category|sub_category|date|day|time|start_timestamp|end_timestamp | type|cpc|gmv||----------|--------------------------------------------|--------------------------------------------||ABC | ABC-1 | 2020年2月17日|周一| 23:37:36|(20年2月7日23:37:36(|(20月2日23:38:29(| BI | RT | 1.82 | 236542||ABC|ABC-1|2020年2月18日|周二|0:05:14|(20年2月1日00:05:14(|(20月2日00:06:33(|BI|RT|1.59|206636||XYZ|XYZ-1|02/17/2020 |02/18/2020 |周一|周二|0:06:21|(02/17/20 23:55:47(|(02/18/20 00:12:42(|PP|RT|BI|0.95|123815|

有一些新生成的字段，如下所示：

|字段|定义||-----------------|------------------------------------------------------||day |行的日期(如果有不同的日期，则组合(||time|时间戳的开始||start_timestamp |组中第一行的开始时间戳||end_timestamp |组中最后一行的开始时间戳||type |行的类型(如果有不同类型，则组合(||cpc|集团平均cpc||gwm|该组的平均GMV |

有人能帮我按照上述要求进行查询吗？

谢谢

Answer 1

这是一个缺口和孤岛问题。这里是一个使用lag()和累积sum()来定义间隔小于8分钟的相邻记录组的解决方案；剩下的就是聚合。

select
category,
sub_category,
string_agg(distinct day, '|' order by dt) day,
min(dt) start_dt,
max(dt) end_dt,
string_agg(distinct type, '|' order by dt) type,
avg(cpc) cpc,
avg(gwm) gwm
from (
select
t.*,
sum(case when dt <= datetime_add(lag_dt, interval 8 minute) then 0 else 1 end)
over(partition by category, sub_category order by dt) grp
from (
select
t.*,
lag(dt) over(partition by category, sub_category order by dt) lag_dt
from (
select t.*, datetime(date, timestamp) dt
from mytable t
) t
) t
) t
) t
group by category, sub_category, grp

请注意，您不应该将时间戳的日期和时间部分存储在单独的列中：当您需要组合它们时，这会使逻辑更加复杂(我添加了另一个嵌套级别，以避免重复转换，这会混淆代码(。