自成立以来，每日活跃驾驶员数量不断增加

Question

我在名为 all_data 的表中有一个 2022 年 1 月 1 日到 2022 年 1 月 15 日（15 天）期间的驱动程序、订单和日期列表，如下所示：

driver_id	order_id	订单日期
1	一个	2022-01-01
1	b	2022-01-02
2	c	2022-01-01
2	d	2022-01-03

在这 15 天内，我如何找到截至该日期每天至少完成一份订单的持续活跃司机的数量？输出应该是这样的表格：

订单日期	active_drivers
2022-01-01	30
2022-01-02	27
2022-01-03	25

例如，2022 年 1 月 1 日，有 30 位不同的司机当天至少完成了一份订单。在 2022 年 1 月 2 日，我们必须找到在 2022 年 1 月 1 日和 2022 年 1 月 2 日至少完成一份订单的唯一司机的数量。在 2022 年 1 月 3 日，我们必须统计在 2022 年 1 月 1 日、2022 年 1 月 2 日和 2022 年 1 月 3 日至少完成一个订单的司机。

我尝试过的

我在 MySQL 中找到了类似的解决方案（如下），但在 bigquery 中不允许这样做，因为错误“Unsupported subquery with table in join predicate”。

MySQL
SELECT order_date, 
 (SELECT COUNT(distinct s1.driver_id) as num_hackers 
   FROM all_data s2 
   join all_data s1 
   on s2. order_date = s1. order_date and     
     (SELECT COUNT(distinct s3. order_date) 
      FROM all_data s3 
      WHERE s3.driver_id = s2.driver_id 
        AND s3. order_date < s1. order_date) 
     = datediff(s1. order_date, date('2022-01-01'), day)
))
from all_data

我还读过这个 Google BigQuery: Rolling Count Distinct 问题，但这是固定的 45 天，而这里的天数是基于日期的变量。如何在 BigQuerySQL 中编写查询来查找每天持续活跃的驱动程序的滚动数量？

Answer 1

首先找出日期和驱动程序的所有组合，然后获取每个日期所有驱动程序的计数。试试这个：

select order_date, count(*)
from(
    select order_date, driver_id, count(*)
    from all_data ad
    group by order_date, driver_id)
group by order_date

Answer 2

考虑下面

select order_date, count(distinct if(flag, driver_id, null)) active_drivers
from (
  select order_date, driver_id, 
    row_number() over(partition by driver_id order by order_date) - 
    date_diff(order_date, min(order_date) over(), day) = 1 as flag
  from (select distinct order_date, driver_id from all_data)
)
group by order_date