Bilangan pemandu aktif harian terus meningkat sejak penubuhannya

Question

Saya mempunyai senarai pemandu, pesanan dan tarikh untuk tempoh 1 Januari 2022 hingga 15 Januari 2022 (15 hari) dalam jadual bernama all_data seperti ini:

id_pemandu	order_id	Tarikh tempahan
1	satu	2022-01-01
1	b	2022-01-02
2	c	2022-01-01
2	d	2022-01-03

Bagaimanakah saya boleh mencari bilangan pemandu yang aktif secara berterusan yang telah menyelesaikan sekurang-kurangnya satu pesanan setiap hari pada tarikh tersebut dalam tempoh 15 hari ini? Output mestilah jadual seperti ini:

Tarikh tempahan	pemandu_aktif
2022-01-01	30
2022-01-02	27
2022-01-03	25

Sebagai contoh, pada 1 Januari 2022, 30 pemandu berbeza menyelesaikan sekurang-kurangnya satu pesanan pada hari itu. Pada 2 Januari 2022, kami perlu mencari bilangan pemandu unik yang memenuhi sekurang-kurangnya satu pesanan pada 1 Januari 2022 dan 2 Januari 2022. Pada 3 Januari 2022, kami mesti mengira pemandu yang menyelesaikan sekurang-kurangnya satu pesanan pada 1 Januari 2022, 2 Januari 2022 dan 3 Januari 2022.

Apa yang saya cuba

Saya menemui penyelesaian yang serupa dalam MySQL (di bawah), tetapi ia tidak dibenarkan dalam pertanyaan besar kerana ralat "Subquery tidak disokong dengan jadual dalam predikat gabungan".

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

Saya juga membaca soalan Google BigQuery: Rolling Count Distinct ini, tetapi ini adalah 45 hari tetap, sedangkan di sini bilangan hari ialah pembolehubah berdasarkan tarikh. Bagaimana untuk menulis pertanyaan dalam BigQuerySQL untuk mencari bilangan bergulir pemandu yang aktif secara berterusan setiap hari?

Answer 1

Mula-mula cari semua kombinasi tarikh dan pemandu, kemudian dapatkan kiraan semua pemandu untuk setiap tarikh. Cuba ini:

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

Pertimbangkan perkara berikut

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