J'ai 2 tableaux. Le premier est une liste d'individus (ID) et une date de transaction. La seconde est une table d'historique client qui contient un instantané des données client au fil du temps. Je souhaite extraire l'indicateur d'adhésion pour la date d'exécution la plus proche mais AVANT la date de transaction.

* EDIT : J'ai essayé d'utiliser TOP 1, et de classer/partitionner... mais ils prennent trop de temps à s'exécuter. Je n'ai pas le droit de créer un index sur la table. C'est la requête qui m'a donné ce dont j'ai besoin :

SELECT  t1.*, t2.RunDate, t2.CST_ISMEMBER 
FROM table1 t1
INNER JOIN table2 t2 
   ON t1.ID=t2.ID
   AND t2.RunDate = 
            (SELECT MAX(t2b.RunDate) 
            FROM table2 t2b  
            WHERE t2b.ID=t2.ID AND t1.TransDate >= t2b.RunDate)

PLUS DE MODIFICATIONS * J'ai constaté que pour environ 9 000 individus du tableau 1, la date d'exécution du tableau 2 survient APRÈS la date de la transaction. Alors maintenant, j'ai besoin de trouver la date la PLUS PROCHE de (avant ou après) la transaction. J'ai du mal à trouver une solution de datediff qui n'implique pas de classement/partition ou de top 1 donnant mon manque de problème d'index. J'ai dû tuer la requête après 5 minutes pour ne pas me faire crier dessus par l'informatique ;-) Ce sont les index disponibles sur la table 2 qui est la plus grande.

enter image description here

    TABLE 1 
ID   TransDate 
1    8/20/13
2    9/5/14

TABLE 2
ID   RunDate      IsMember
1    6/30/2010    Y
1    7/11/2012    N
1    7/30/2013    N
1    8/15/2013    Y
2    9/1/2014     Y
2    9/10/2014    N 

Résultat désiré:

ID  TransDate   IsMember
1   8/20/13     N
2   9/5/14      Y

Quelle est la meilleure façon d'aborder cela?

Merci d'avance.

1
kiki1113 7 févr. 2020 à 18:29

1 réponse

Meilleure réponse

Une sous-requête corrélée est une méthode simple. Voici à quoi ressemble le code :

select t1.*,
       (select top (1) t2.ismember
        from table2 t2
        where t2.id = t1.id and t2.rundate < t1.transdate
        order by t2.rundate desc
       ) as ismember
from table1 t1;

Pour les performances, vous voulez un indice sur table2(id, rundate, ismember).

ÉDITER:

Vous avez des indices moche pour cela. Comment cette table d'historique n'a pas pu avoir d'index sur id est très étrange. Voici une approche entièrement différente qui utilise des fonctions de fenêtre.

with t as (
      select id, transdate as dte, null as ismember, 1 as which
      from table1
      union all
      select id, rundate, ismember, 2
      from table2
     )
select t.*
from (select t.*, lag(ismember) over (partition by id order by dte, which desc) as ismember
      from t
     ) t
where which = 1;

Je soupçonne que le lag() sur les données unifiées sera plus rapide que toute tentative de correction de la première méthode. Cependant, le premier serait beaucoup plus rapide avec l'index approprié.

2
Gordon Linoff 19 févr. 2020 à 22:00