Utilisation de SQL standard dans Google BigQuery.
J'ai une table avec 2 types de commande: A et B.
SELECT Id, Type,
LEAD(OrderDate) OVER (PARTITION BY Id ORDER BY OrderDate)
FROM xxx
Pour chaque type de commande A, je souhaite déterminer quand la prochaine commande B était, en ignorant toutes les autres commandes A.
Donc, dans mes données d'exemple, si je souhaite renvoyer ce qui suit:
Id | Type | NextOrderBDate -------------------------------- 1 | A | 2019-03-04 4 | A | 2019-04-05 5 | A | 2019-04-05
J'ai obtenu le résultat de manière extrêmement inefficace en joignant 2 tables séparées de A et B l'une à l'autre - mais l'ensemble de données est très volumineux et il a fallu plus d'une heure pour s'exécuter.
Ce que j'essaie actuellement à faire est d'utiliser une instruction LEAD comme ceci:
Id | Type | OrderDate ----------------- 1 | A | 2019-03-01 2 | B | 2019-03-04 3 | B | 2019-03-04 4 | A | 2019-03-05 5 | A | 2019-03-06 6 | B | 2019-04-05
De toute évidence, le problème ici est qu'il retournera la date suivante, quel que soit le type de commande.
Je me demande si la clé pour faire cela est de déterminer le décalage correct dont chaque ligne a besoin pour MENER au prochain ordre de type B, et j'ai du mal à trouver une solution (propre) ici.
Merci à l'avance.
3 Réponses :
Vous pouvez utiliser une requête en ligne comme suit:
id | type | NextOrderBDate -: | :--- | :------------- 1 | A | 2019-03-04 4 | A | 2019-04-05 5 | A | 2019-04-05
select
id,
type,
(
select min(OrderDate)
from mytable t1
where t1.Type = 'B' and t1.OrderDate >= t.OrderDate
) NextOrderBDate
from mytable t
where type = 'A'
Utilisez simplement un min cumulatif:
select t.*
from (select t.*,
min(case when type = 'B' then orderdate end) over (order by orderdate) as next_b_orderdate
from t
) t
where type = 'A';
@Gordon Linoff a raison sauf petit bug: la prochaine commande B doit être trouvée liée à chaque commande courante . Donc, la fenêtre de requête doit IMHO être adaptée de manière appropriée:
with t (id, type, orderdate) as (
select 1 , 'A' , date '2019-03-01' union
select 2 , 'B' , date '2019-03-04' union
select 3 , 'B' , date '2019-03-04' union
select 4 , 'A' , date '2019-03-05' union
select 5 , 'A' , date '2019-03-06' union
select 6 , 'B' , date '2019-04-05'
)
select t.*
from (select t.*,
min(case when type = 'B' then orderdate end)
over (order by orderdate
rows between current row and unbounded following
) as next_b_orderdate
from t
) t
where type = 'A';