Die Zahl der täglich aktiven Fahrer ist seit seiner Einführung kontinuierlich gestiegen

Question

Ich habe eine Liste mit Fahrern, Bestellungen und Terminen für den Zeitraum 1. Januar 2022 bis 15. Januar 2022 (15 Tage) in einer Tabelle mit dem Namen all_data wie folgt:

Treiber_ID	order_id	Bestelldatum
1	eins	2022-01-01
1	b	2022-01-02
2	c	2022-01-01
2	d	03.01.2022

Wie finde ich die Anzahl der kontinuierlich aktiven Fahrer heraus, die zu diesem Zeitpunkt in diesen 15 Tagen mindestens einen Auftrag pro Tag abgeschlossen haben? Die Ausgabe sollte eine Tabelle wie diese sein:

Bestelldatum	aktive_Treiber
2022-01-01	30
2022-01-02	27
03.01.2022	25

Zum Beispiel haben am 1. Januar 2022 30 verschiedene Fahrer an diesem Tag mindestens eine Bestellung abgeschlossen. Am 2. Januar 2022 müssen wir die Anzahl der einzelnen Fahrer ermitteln, die am 1. Januar 2022 und am 2. Januar 2022 mindestens eine Bestellung ausgeführt haben. Am 3. Januar 2022 müssen wir Fahrer zählen, die am 1. Januar 2022, 2. Januar 2022 und 3. Januar 2022 mindestens eine Bestellung abgeschlossen haben.

Was ich versucht habe

Ich habe eine ähnliche Lösung in MySQL gefunden (unten), aber sie ist in BigQuery aufgrund des Fehlers „Nicht unterstützte Unterabfrage mit Tabelle im Join-Prädikat“ nicht zulässig.

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

Ich habe auch diese Google BigQuery: Rolling Count Distinct-Frage gelesen, aber hier handelt es sich um feste 45 Tage, während die Anzahl der Tage hier eine datumsbasierte Variable ist. Wie schreibe ich eine Abfrage in BigQuerySQL, um die fortlaufende Anzahl der Fahrer zu ermitteln, die pro Tag kontinuierlich aktiv sind?

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