时间戳

首页 > 解决方案 > 如何计算 SQL Google Big Query 中不同字符串值的每周和每月出现次数？

sql - 如何计算 SQL Google Big Query 中不同字符串值的每周和每月出现次数？

问题描述

我是 SQL 新手，我有一个包含日期值和域列的数据集。域列仅包含值“个人”和“业务”。我想要完成的是计算每个域类型的每周和每月滚动计数。

我想做的是创建 2 个单独的列 - is_personal 和 is_business - 对于 domain_type 具有适当值的行，其值为 1。例如，如果 domain_type 是“个人”，那么 is_personal 列中的值为 1。否则，1 将在 is_business 行中。然后，我要计算滚动总和。

但是，我想知道我是否可以避免创建额外的列并直接从 Google Big Query 中的字符串列执行每周和每月滚动计数。

到目前为止，我尝试的是“分组”日期列，DATE_TRUNC(CAST(created_at AS date), ISOWEEK)用于按周“汇总”日期。当我在 domain_type 列上尝试任何滚动函数时，我得到了很多错误。有些与尝试 Google Big Query 无法识别的功能有关，有些与我正在使用字符串列的事实有关，等等。

我试图实现的最终目标是计算“业务”和“个人”域类型的每周和每月滚动计数。请让我知道我是否可以提供有帮助的其他信息。谢谢！

目前的数据外观：

       Date          domain_type

     2017-10-02      personal
     2017-10-03      business
     2017-10-04      personal
     2017-10-05      business
     2017-10-06      personal
     2017-10-07      business
     2017-10-08      personal 
     2017-10-09      business
     2017-10-10      personal
     2017-10-11      business
     2017-10-12      personal
     2017-10-13      business
     2017-10-14      personal
     2017-10-15      business

假设在 2017 年 10 月 2 日这一周，共有 10 位用户使用个人电子邮件地址注册，共有 20 位用户使用企业电子邮件地址注册。在 2017 年 10 月 9 日这一周，共有 25 人注册了个人电子邮件，30 人注册了企业电子邮件。因此，在 2 周内，个人域类型的滚动计数为 35，业务域类型的滚动计数为 50。

我试图实现的输出：

Date          domain_type  rolling_count_for_week
2017-10-02    personal           10
2017-10-02    business           20
2017-10-09    personal           35
2017-10-09    business           50

标签： sqlgoogle-bigqueryrolling-computation

解决方案

如果您想要一周内不同值的数量，请使用聚合：

select date_trunc(date, week) as wk, email_type,
       count(*)  -- or count(distinct email) if they are not already unique
from t
group by wk, email_type
order by 1, 2;

我没有看到任何关于您正在尝试做的事情的“滚动”——除非您可能想要连续两周进行计数。如果是这种情况，请使用窗口函数：

select date_trunc(date, week) as wk, email_type,
       count(*) as this_week,
       sum(count(*)) over (partition by email_type order by date_trunc(date, wk) rows between 1 preceding and current row) as 2_week_count
from t
group by wk, email_type
order by 1, 2;

推荐阅读