自成立以來，每日活躍駕駛員數量不斷增加

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