4
votes

Comment cibler un enregistrement de la table B vers et l'ID de la table A en fonction d'une restriction de temps?

Objectif: je souhaite faire correspondre un transaction_id à la première note qui a été entrée dans le système dans les 20 minutes suivant l'horodatage de transcation_id.

Situation: Les deux tableaux sont liés par e-mail. Envoyez un e-mail à aa@email.com par exemple sous la forme d'un identifiant de transaction enregistré à 3h59 le 1er janvier 2019. Je voudrais voir si une note a été insérée dans les 20 minutes. La note 1 du tableau 2 doit donc être ciblée. Fondamentalement, la première occurrence. Pour la deuxième transaction (associée à l'email zz@email.com), aucune note ne lui sera attachée puisque la première insertion de note dure> 20mns.

Tableau 1:

SELECT t1.timestamp
    ,t1.email
    ,t1.transaction_id
    ,Emails
    ,Dates
FROM t1
    INNER JOIN 
        (
        SELECT t2.email AS Emails
            ,t2.note AS Notes
            ,t2.timestamp AS Dates
            ,ROW_NUMBER()
                OVER(PARTITION BY t2.email ORDER BY t2.timestamp ASC) AS Top1_note
        FROM t2
        ) AS Subquery 
    ON t1.email=Subquery.Emails

sql sql-server

0 commentaires

3 Réponses :

2
votes

Vous pouvez utiliser CROSS APPLY pour obtenir toutes les notes en 20 minutes. Utilisez row_number () pour obtenir uniquement (une des) notes les plus anciennes.

SELECT *
       FROM table1 t1
            CROSS APPLY (SELECT *,
                                row_number() OVER (ORDER BY timestamp) rn
                                FROM table2 t2
                                     WHERE t2.email = t1.email
                                           AND t2.timestamp >= t1.timestamp
                                           AND t2.timestamp <= dateadd(minute, 20, t1.timestamp)) x
       WHERE x.rn = 1;

db violon

4 commentaires

le WHERE t2.email = t1.email remplace la clause PARTITION BY email dans le row_number?

@RogerSteinberg: Le partitionnement par e-mail serait inutile car l'e-mail est le même sur toutes les lignes ici.

alors quelle est la différence entre la jointure interne et la croix s'applique ici. Je sais que cette question n'est pas pertinente par rapport à la question, mais je ne suis pas sûr de comprendre

@RogerSteinberg: Avec APPLY , vous pouvez utiliser les valeurs de la requête externe dans la sous-requête. Limitez ici les enregistrements à ceux des 20 minutes. Avec les jointures, vous ne pouvez pas faire cela.

1
votes

Voici une approche alternative qui utilise les fonctions de fenêtre.

La requête sélectionne d'abord toutes les notes liées à la transaction (dans les 20 minutes suivantes), puis utilise une condition NOT EXISTS avec une sous-requête corrélée pour ne conserver que la plus récente.

SELECT
    t1.*, t2.note, t2.timestamp note_timestamp
FROM 
    table1 t1
    INNER JOIN table2 t2 
        ON t1.email = t2.email
        AND t2.timestamp >=  t1.timestamp 
        AND t2.timestamp < DATEADD(MINUTE, 20, t1.timestamp)
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM table2
        WHERE 
            email = t2.email
            AND timestamp > t2.timestamp
            AND timestamp < DATEADD(MINUTE, 20, t1.timestamp)
    )

2 commentaires

que fait select 1 ici? et pourquoi pas existe au lieu d'exister?

@RogerSteinberg: le but de cette condition NOT EXISTS est de s'assurer que la ligne que nous sélectionnons est la dernière (donc il est dit: assurez-vous qu'aucun enregistrement plus récent N'EXISTE). Comme cette condition n'est là que pour vérifier si une ligne EXISTE (le contenu de cette ligne n'est pas réellement utile), nous utilisons «SELECT 1»

1
votes

Essayez ceci,

 SELECT  TOP 1 t1.[timestamp] ,t1.[Email],t1.transaction_id 
      ,t2.[Note]
      ,t2.[timestamp] as  note_timestamp
       FROM table1 t1 inner Join table2 t2
       on t2.email = t1.email
       And   t2.timestamp >= t1.timestamp
       AND t2.timestamp <= dateadd(minute, 20, t1.timestamp)

0 commentaires