Correlare dati tra tabelle con il ROW_NUMBER

Le window function sono un argomento molto ampio e meriteranno un approfondimento in un articolo ad hoc, ma riassumendo queste funzioni permettono di partizionare ed eventualmente ordinare un set di righe prima dell’applicazione di una funzione.

La clausola OVER definisce un set di righe specificato dall’utente all’interno di un set di risultati della query. Successivamente una funzione calcola un valore per ogni riga del set.

Definizione del dataset

Ora lavoriamo sulle seguenti tabelle, che sono delle tabelle provvisorie create ad hoc, queste sono una parte di dei raggruppamenti per la colonna Foo e filtrate per Foo=2183098.

SELECT * INTO #temp_table_1
FROM (
SELECT N'2183098' AS Foo, N'142230' AS Bar UNION ALL
SELECT N'2183098' AS Foo, NULL AS Bar UNION ALL
SELECT N'2183098' AS Foo, NULL AS Bar ) t;

SELECT * INTO #temp_table_2
FROM (
SELECT N'2183098' AS Foo, N'142228' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142229' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142230' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142231' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142232' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142233' AS Bar ) t;

Il mio obiettivo è associare le due tabelle (come se ci fosse una specie di JOIN) per ottenere il seugente risultato

Questo può essere utile in caso di update o di confronto dati tra le due tabelle e così via.

Estrazione del ROW_NUMBER

Analizzando approfonditamente il problema, si nota che quello che voglio fare è associare le tabelle per numero di riga: è necessario conseguentemente eseguire un SELECT sulle tabelle che aggiunga anche il numero di righe al dataset ottenuto dalle tabelle.

Per fare questo utilizzo la funzione di rango ROW_NUMBER che restituisce il numero sequenziale di una riga all’interno di una partizione di un set di risultati, a partire da 1 per la prima riga di ogni partizione.

SELECT row_number() OVER (
            PARTITION BY Foo ORDER BY Foo
            ) AS Riga_1
        , *
    FROM #temp_table_1

ed analogamente anche sulla seconda tabella.
I risultati che ottengo sono i seguenti:

Il PARTITION BY Foo è superfluo in questo semplice esempio, in quanto ho già precedentemente filtrato per Foo=2183098, ma in un caso reale così non è.

Associazione dei dati

Una volta fatto ciò, l’associazinoe risulta immediata con un JOIN in questo modo:

SELECT T1.Foo
    , T1.Bar AS [Bar tabella 1]
    , T2.Bar AS [Bar tabella 2]
FROM (
    ... prima query
    ) AS T1
INNER JOIN (
    ... seconda query
    ) AS T2 ON T1.Riga_1 = T2.riga_2

Che mi permette di ottenere quanto voluto.