Sélectionnez le score de test le plus récent sur une base mensuelle

Question

Je utilise Oracle SQL, et j'essaie de réaliser une analyse historique des scores de test (pour visualiser les améliorations des scores de test sur une base mensuelle pour les individus). Tout d'abord, j'ai une table qui est une liste d'utilisateurs et des mois respectifs où ils sont actifs; cela ressemble à ceci :

    TABLE1
    ________________________
    UserName  |  ActiveDate
    ________________________
    John Doe,    01-MAY-18
    John Doe,    01-APR-18
    John Doe,    01-MAR-18
    Jane Doe,    01-APR-18
    Jane Doe,    01-MAR-18
    Jim Doe,     01-MAY-18

En plus de cela, j'ai une autre table qui répertorie les Scores de test, qui sont horodatés (vous pouvez refaire le test autant de fois que vous le souhaitez). Cela ressemble à ceci :

    TABLE2
    ________________________________________
    UserName  |  TestScore  |  EffectiveDate
    ________________________________________
    John Doe,    87,           07-FEB-18
    John Doe,    85,           14-FEB-18
    John Doe,    90,           18-FEB-18
    John Doe,    92,           02-MAR-18
    John Doe,    91,           12-MAR-18
    Jane Doe,    70,           01-FEB-18
    Jane Doe,    72,           02-FEB-18
    Jane Doe,    78,           18-FEB-18
    Jane Doe,    77,           06-MAR-18
    Jane Doe,    81,           18-MAR-18
    Jim Doe,     50,           03-MAR-18
    Jim Doe,     48,           23-MAR-18
    Jim Doe,     58,           08-APR-18

Pour chaque ligne dans la première table (tous les paires UserName | ActiveDate sont distincts), je voudrais sélectionner le TestScore le plus récent de Table2 où la EffectiveDate est antérieure à l'ActiveDate

Donc j'espère obtenir quelque chose comme ceci

    UserName  |  ActiveDate  |  TestScore le plus récent avant ActiveDate
    ______________________________________
    John Doe,    01-MAY-18,     91
    John Doe,    01-APR-18,     91
    John Doe,    01-MAR-18,     90
    Jane Doe,    01-APR-18,     81
    Jane Doe,    01-MAR-18,     78
    Jim Doe,     01-MAY-18,     58

J'ai essayé de faire fonctionner cela en JOIGNANT Table1 à Table2 sur UserName, où EffectiveDate < ActiveDate, mais je n'arrive pas à trouver la déclaration SQL dont j'ai besoin pour SÉLECTIONNER * de Table2 où EffectiveDate < ActiveDate, mais j'ai du mal à trouver cela sur une base "par ligne"...

Merci pour tous les conseils à l'avance. C'est ma première publication sur StackOverflow, donc j'espère avoir posé correctement cette question!

Éditer: Merci à tous pour l'aide, je pense avoir tout ce dont j'ai besoin pour poursuivre mon projet maintenant. Je vais m'assurer d'apporter quelques améliorations à ma publication la prochaine fois que je poserai une question ici sur SO.

Answer 1

Si vous souhaitez simplement obtenir le score de test, une sous-requête corrélée pourrait être l'approche la plus simple :

select t1.*,
       (select max(t2.score) keep (dense_rank first order by t2.effectivedate desc)
        from table2 t2
        where t2.effectivedate < t1.activedate
       ) as most_recent_score
from table1 t1;

Answer 2

Deux solutions nécessitant uniquement une seule jointure:

Configuration Oracle:

CREATE TABLE TABLE1 ( NomUtilisateur, DateActive ) AS
  SELECT 'John Doe', DATE '2018-05-01' FROM DUAL UNION ALL
  SELECT 'John Doe', DATE '2018-04-01' FROM DUAL UNION ALL
  SELECT 'John Doe', DATE '2018-03-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', DATE '2018-04-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', DATE '2018-03-01' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  DATE '2018-05-01' FROM DUAL;

CREATE TABLE TABLE2 ( NomUtilisateur, ScoreTest, DateEffective ) AS
  SELECT 'John Doe', 87, DATE '2018-02-07' FROM DUAL UNION ALL
  SELECT 'John Doe', 85, DATE '2018-02-14' FROM DUAL UNION ALL
  SELECT 'John Doe', 90, DATE '2018-02-18' FROM DUAL UNION ALL
  SELECT 'John Doe', 92, DATE '2018-03-02' FROM DUAL UNION ALL
  SELECT 'John Doe', 91, DATE '2018-03-12' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 70, DATE '2018-02-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 72, DATE '2018-02-02' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 78, DATE '2018-02-18' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 77, DATE '2018-03-06' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 81, DATE '2018-03-18' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  50, DATE '2018-03-03' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  48, DATE '2018-03-23' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  58, DATE '2018-04-08' FROM DUAL;

Requête 1:

SELECT *
FROM   (
  SELECT t2.*,
         t1.DateActive,
         ROW_NUMBER() OVER ( PARTITION BY t2.NomUtilisateur, t1.DateActive ORDER BY DateEffective DESC ) AS rn
  FROM   table2 t2
         INNER JOIN
         table1 t1
         ON ( t1.NomUtilisateur = t2.NomUtilisateur
              AND t2.DateEffective < t1.DateActive )
) t2
WHERE rn = 1;

Résultat:

NOMUTILISATEUR   SCORETEST   DATEFFECTIVE    DATEACTIVE  RN
----------  ----------  --------------  ----------  ---
Jane Doe    78          18-FEB-18       01-MAR-18   1
Jane Doe    81          18-MAR-18       01-APR-18   1
Jim Doe     58          08-APR-18       01-MAY-18   1
John Doe    90          18-FEB-18       01-MAR-18   1
John Doe    91          12-MAR-18       01-APR-18   1
John Doe    91          12-MAR-18       01-MAY-18   1

Requête 2:

SELECT t1.NomUtilisateur,
       t1.DateActive,
       MAX( ScoreTest ) KEEP ( DENSE_RANK LAST ORDER BY DateEffective ) AS ScoreTestPlusRecent
FROM   table2 t2
       INNER JOIN
       table1 t1
       ON ( t1.NomUtilisateur = t2.NomUtilisateur
            AND t2.DateEffective < t1.DateActive )
GROUP BY t1.NomUtilisateur, t1.DateActive;

Résultat:

NOMUTILISATEUR    DATEACTIVE  SCORETESTPLUSRECENT
----------  ----------  -------------------
Jim Doe     01-MAY-18   58
Jane Doe    01-MAR-18   78
Jane Doe    01-APR-18   81
John Doe    01-MAR-18   90
John Doe    01-APR-18   91
John Doe    01-MAY-18   91

Answer 3

Voici une option (vous avez besoin des lignes 24 et suivantes; les lignes précédentes ne sont que des CTE de test):

SQL> with table1 (username, activedate) as
  2    (select 'jod', date '2018-05-01' from dual union all
  3     select 'jod', date '2018-04-01' from dual union all
  4     select 'jod', date '2018-03-01' from dual union all
  5     select 'jad', date '2018-04-01' from dual union all
  6     select 'jad', date '2018-03-01' from dual union all
  7     select 'jid', date '2018-05-01' from dual
  8    ),
  9  table2 (username, testscore, effectivedate) as
 10    (select 'jod', 87, date '2018-02-07' from dual union all
 11     select 'jod', 85, date '2018-02-14' from dual union all
 12     select 'jod', 90, date '2018-02-18' from dual union all
 13     select 'jod', 92, date '2018-03-02' from dual union all
 14     select 'jod', 91, date '2018-03-12' from dual union all
 15     select 'jad', 70, date '2018-02-01' from dual union all
 16     select 'jad', 72, date '2018-02-02' from dual union all
 17     select 'jad', 78, date '2018-02-18' from dual union all
 18     select 'jad', 77, date '2018-03-06' from dual union all
 19     select 'jad', 81, date '2018-03-18' from dual union all
 20     select 'jid', 50, date '2018-03-03' from dual union all
 21     select 'jid', 48, date '2018-03-23' from dual union all
 22     select 'jid', 58, date '2018-04-08' from dual
 23    )
 24  select t1.username, t1.activedate, t2.testscore
 25  from table1 t1 join table2 t2 on t1.username = t2.username
 26  where t2.effectivedate = (select max(t2a.effectivedate)
 27                            from table2 t2a
 28                            where t2a.username = t2.username
 29                              and t2a.effectivedate < t1.activedate
 30                           )
 31  order by t1.username, t1.activedate desc;

UTILISATEUR ACTIVEDAT TESTSCORE
--- --------- ----------
jad 01-avr-18         81
jad 01-mar-18         78
jid 01-mai-18         58
jod 01-mai-18         91
jod 01-avr-18         91
jod 01-mar-18         90

6 lignes sélectionnées.

SQL>