时间戳

首页 > 解决方案 > 是否可以使用 PARTITION BY 进行计数？

sql - 是否可以使用 PARTITION BY 进行计数？

问题描述

我在 BigQuery 中有一个数据库，其中每条记录都是我网站上的网络流量会话。

我目前有这张表告诉我一个人是如何来到我的网站的，还有一个列告诉我每个事件的顺序。

最终目标是查看一个人在有机会议之前必须进行多少次“非有机”会议。

我正在尝试创建一个附加列，每次发生转换时都会返回“包含”（当在非自然会话之后发生自然会话时）

我知道如何使用 Excel 执行此操作，但我不知道如何使用 SQL 执行此操作。我有一种感觉，“PARTITION BY”是解决方案，但我不知道如何。

数据库示例

这是我的 Excel 解决方案：

=IF(AND((COUNTIF($B$2:B2,FALSE))>=1,(IF(COUNTIF($B$2:B2,FALSE)>=1,COUNTIFS($B$2:B2,TRUE,$C$2:C2,">1"),0))>=1),"include","exclude")

标签： sqlgoogle-bigquery

解决方案

如果您尝试添加status标志，那么您似乎需要以下规则：

  • “排除”所有无机会话。
  • “包括”在第一个无机会话之前的所有有机会话。
  • “包括”所有其他有机会议。

如果是这样，您可以简单地使用：

select t.*
       (case when not is_organic then 'exclude'
             when countif(not is_organic) over (partition by partition by person_id order by sequence) = 0
             then 'exclude'
             else 'include'
        end) as status
from t;

但是，对于这个问题：

最终目标是查看一个人在有机会议之前必须进行多少次“非有机”会议。

我会简单地将聚合与窗口函数一起使用：

select person_id, countif(sequence < first_organic_sequence)
from (select t.*,
             min(case when is_organic then sequence end) over (partition by person_id) as first_organic_sequence
      from t
     ) t
group by person_id;

或者，如果sequence总是从1以及如何没有间隙开始：

select person_id, min(case when is_organic then sequence end) - 1
from t
group by person_id;

推荐阅读