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 ?
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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_dateConsidé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