1
votes

Ignorer des lignes spécifiques en utilisant LAG dans SQL

J'ai un tableau qui ressemble à ceci:

En utilisant la fonction LAG en SQL, je voudrais exécuter le LAG uniquement sur les valeurs où star_date = date_fin et obtenir le dernier enregistrement date_début précédent où date_début = date_fin. Que ma table de bout aura une colonne supplémentaire comme celle-ci:

J'espère que ma question est claire, toute aide est appréciée.

sql google-bigquery lag skip

0 commentaires

3 Réponses :

2
votes

Vous pouvez attribuer un groupe à ces valeurs et les utiliser:

select t.*,
       (case when start_date = end_date
             then lag(start_date) over (partition by (case when start_date = end_date then 1 else 2 end), (case when start_date <> end_date then start_date end) order by start_date)
        end) as prev_eq_start_date
from t;

Ou:

select t.*,
       (case when start_date = end_date
             then lag(start_date) over (partition by start_date = end_date order by start_date)
        end) as prev_eq_start_date
from t;

Notez si vos données sont volumineuses et la plupart des lignes ont des dates différentes, alors vous pourriez avoir un problème de ressources. Dans ce cas, une clé supplémentaire partition by non utilisée peut aider:

select t.*,
       (case when start_date = end_date
             then lag(start_date) over (partition by (case when start_date = end_date then 1 else 0 end) order by start_date)
        end) as prev_eq_start_date
from t;

Cela n'a aucun impact sur le résultat mais cela peut éviter une erreur de ressources causé par trop de lignes avec des valeurs différentes.

9 commentaires

Je crois que partition by (StartDate = EndDate) donnera une erreur de syntaxe!

@VigneshKumarA. . . Je me suis tellement habitué à utiliser countif () que j'ai oublié que BQ ne traite pas les booléens comme les autres types.

Je pensais que la partition par (StartDate = EndDate) fonctionnerait parfaitement! avez-vous testé et confirmé que non?!

@MikhailBerlyant. . . En fait, je l'ai testé et cela a généré une erreur. Bien sûr, j'ai peut-être mal fait le test. Je n'ai pas insisté sur la question.

@GordonLinoff - vient de tester votre réponse originale (avec partition by (start_date = end_date) ) et cela fonctionne parfaitement pour moi (BigQuery Standard SQL) - je pense que c'est assez élégant pour y renoncer: o (je pensais évidemment que j'aime mieux ma réponse: o)

J'ai également utilisé BigQuery Standard SQL et cela a fonctionné pour moi. Merci pour l'aide!

@GordonLinoff Salut! J'ai une question similaire, mais je ne peux pas faire les choses correctement, pourriez-vous m'aider plz, je suis coincé là-dedans depuis beaucoup de temps. Mais c'est Spark SQL stackoverflow.com/questions/64123064/...

@GordonLinoff mais en gros je veux faire un décalage avec une condition comme dans T-SQL

@JoseMacedo. . . La question à laquelle vous avez fait référence est une question Spark. Vous avez une question T-SQL?

0
votes

Utilisez JOIN

SQL FIDDLE a >

SELECT T.*,T1.LAG_Result
FROM TABLE T LEFT JOIN 
(
   SELECT User_Id,LAG(start_date) OVER(ORDER BY start_date) LAG_Result
   FROM TABLE S 
   WHERE start_date = end_date
) T1 ON T.User_Id = T1.User_Id

0 commentaires

1
votes

Ci-dessous, pour BigQuery Standard SQL

Row user_id start_date  end_date    lag_result   
1   1   01/01/2019      28/02/2019  null     
2   3   27/02/2019      28/02/2019  null     
3   4   04/08/2019      01/09/2019  null     
4   2   01/02/2019      01/02/2019  null     
5   5   07/08/2019      07/08/2019  01/02/2019   
6   6   27/08/2019      27/08/2019  07/08/2019

Si appliquer à des exemples de données dans votre question - le résultat est

#standardSQL
SELECT *, NULL AS lag_result
FROM `project.dataset.table` WHERE start_date != end_date
UNION ALL
SELECT *, LAG(start_date) OVER(ORDER BY PARSE_DATE('%d/%m/%Y', start_date))
FROM `project.dataset.table` WHERE start_date = end_date

Btw, dans cas si vos start_date et end_date sont de type de données STRING ('27 / 02/2019 ') vs type DATE (' 2019-02-27 'comme cela a été supposé dans la requête ci-dessus) - vous devriez utiliser ci-dessous un

Row user_id start_date  end_date    lag_result   
1   1       2019-01-01  2019-02-28  null     
2   3       2019-02-27  2019-02-28  null     
3   4       2019-08-04  2019-09-01  null     
4   2       2019-02-01  2019-02-01  null     
5   5       2019-08-07  2019-08-07  2019-02-01   
6   6       2019-08-27  2019-08-27  2019-08-07

avec résultat

#standardSQL
SELECT *, NULL AS lag_result
FROM `project.dataset.table` WHERE start_date != end_date
UNION ALL
SELECT *, LAG(start_date) OVER(ORDER BY start_date)
FROM `project.dataset.table` WHERE start_date = end_date

0 commentaires