我在名為 all_data 的表中有一個(gè) 2022 年 1 月 1 日到 2022 年 1 月 15 日（15 天）期間的驅(qū)動(dòng)程序、訂單和日期列表，如下所示：
| driver_id | order_id | 訂單日期 |
|---|---|---|
| 1 | 一個(gè) | 2022-01-01 |
| 1 | b | 2022-01-02 |
| 2 | c | 2022-01-01 |
| 2 | d | 2022-01-03 |
在這 15 天內(nèi)，我如何找到截至該日期每天至少完成一份訂單的持續(xù)活躍司機(jī)的數(shù)量？輸出應(yīng)該是這樣的表格：
| 訂單日期 | active_drivers |
|---|---|
| 2022-01-01 | 30 |
| 2022-01-02 | 27 |
| 2022-01-03 | 25 |
例如，2022 年 1 月 1 日，有 30 位不同的司機(jī)當(dāng)天至少完成了一份訂單。在 2022 年 1 月 2 日，我們必須找到在 2022 年 1 月 1 日和 2022 年 1 月 2 日至少完成一份訂單的唯一司機(jī)的數(shù)量。在 2022 年 1 月 3 日，我們必須統(tǒng)計(jì)在 2022 年 1 月 1 日、2022 年 1 月 2 日和 2022 年 1 月 3 日至少完成一個(gè)訂單的司機(jī)。
我嘗試過(guò)的
我在 MySQL 中找到了類似的解決方案（如下），但在 bigquery 中不允許這樣做，因?yàn)殄e(cuò)誤“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
我還讀過(guò)這個(gè) Google BigQuery: Rolling Count Distinct 問(wèn)題，但這是固定的 45 天，而這里的天數(shù)是基于日期的變量。如何在 BigQuerySQL 中編寫(xiě)查詢來(lái)查找每天持續(xù)活躍的驅(qū)動(dòng)程序的滾動(dòng)數(shù)量？
2
0
0
首先找出日期和驅(qū)動(dòng)程序的所有組合，然后獲取每個(gè)日期所有驅(qū)動(dòng)程序的計(jì)數(shù)。試試這個(gè)：
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
考慮下面
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
