Le nombre de conducteurs actifs quotidiens n'a cessé d'augmenter depuis sa création

Question

J'ai une liste de chauffeurs, de commandes et de dates pour la période du 1er janvier 2022 au 15 janvier 2022 (15 jours) dans un tableau nommé all_data comme ceci :

driver_id	order_id	Date de commande
1	un	2022-01-01
1	b	2022-01-02
2	c	2022-01-01
2	d	2022-01-03

Comment connaître le nombre de chauffeurs actifs en continu ayant réalisé au moins une commande par jour à cette date pendant ces 15 jours ? Le résultat devrait être un tableau comme celui-ci :

Date de commande	active_drivers
2022-01-01	30
2022-01-02	27
2022-01-03	25

Par exemple, le 1er janvier 2022, 30 chauffeurs différents ont complété au moins une commande ce jour-là. Le 2 janvier 2022, nous devons connaître le nombre de conducteurs uniques ayant exécuté au moins une commande le 1er janvier 2022 et le 2 janvier 2022. Au 3 janvier 2022, il faut compter les conducteurs ayant complété au moins une commande le 1er janvier 2022, le 2 janvier 2022 et le 3 janvier 2022.

Ce que j'ai essayé

J'ai trouvé une solution similaire dans MySQL (ci-dessous), mais elle n'est pas autorisée dans bigquery en raison de l'erreur "Sous-requête non prise en charge avec table dans le prédicat de jointure".

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

J'ai également lu cette question Google BigQuery : Rolling Count Distinct, mais il s'agit d'une période fixe de 45 jours, alors qu'ici le nombre de jours est une variable basée sur la date. Comment écrire une requête dans BigQuerySQL pour trouver le nombre continu de pilotes actifs en permanence par jour ?

Answer 1

Trouvez d'abord toutes les combinaisons de date et de conducteur, puis obtenez le nombre de tous les conducteurs pour chaque date. Essayez ceci :

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

Considérez ce qui suit

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