SQL
Funzioni della finestra

Aggiungere le righe totali selezionate a ogni riga

SELECT your_columns, COUNT(*) OVER() as Ttl_Rows FROM your_data_set

Invece di utilizzare due query per ottenere un conteggio della riga, è possibile utilizzare una funzione di aggregazione come finestra e utilizzare il set di risultati completo come finestra.
Questo può essere usato come base per ulteriori calcoli senza la complessità degli extra self join.

Impostazione di un flag se altre righe hanno una proprietà comune

Diciamo che ho questi dati:

Articoli da tavola

Mi piacerebbe avere tutte quelle righe e sapere se un tag è usato da altre linee

SELECT id, name, tag, COUNT(*) OVER (PARTITION BY tag) > 1 AS flag FROM items

Il risultato sarà:

Nel caso in cui il tuo database non abbia OVER e PARTITION puoi usarlo per produrre lo stesso risultato:

SELECT id, name, tag, (SELECT COUNT(tag) FROM items B WHERE tag = A.tag) > 1 AS flag FROM items A

Ottenere un totale parziale

Dati questi dati:

SELECT date, amount, SUM(amount) OVER (ORDER BY date ASC) AS running
FROM operations
ORDER BY date ASC

ti darò

Ottenere le N righe più recenti su più raggruppamenti

Dati questi dati

;with CTE as
(SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY User_ID 
                           ORDER BY Completion_Date DESC) Row_Num
FROM    Data)
SELECT * FORM CTE WHERE Row_Num <= n

Usando n = 1, otterrai la riga più recente per user_id :

Ricerca di record "fuori sequenza" utilizzando la funzione LAG ()

Dati questi dati di esempio:

Gli articoli identificati dai valori ID devono passare da STATUS 'UNO' a 'DUE' a 'TRE' in sequenza, senza saltare gli stati. Il problema è trovare i valori degli utenti ( STATUS_BY ) che violano la regola e passare da "UNO" immediatamente a "TRE".

La funzione analitica di LAG() aiuta a risolvere il problema restituendo per ogni riga il valore nella riga precedente:

SELECT * FROM (
 SELECT 
  t.*, 
  LAG(status) OVER (PARTITION BY id ORDER BY status_time) AS prev_status 
  FROM test t
) t1 WHERE status = 'THREE' AND prev_status != 'TWO'

Nel caso in cui il tuo database non abbia il LAG () puoi usarlo per produrre lo stesso risultato:

SELECT A.id, A.status, B.status as prev_status, A.status_time, B.status_time as prev_status_time
FROM Data A, Data B
WHERE A.id = B.id
AND   B.status_time = (SELECT MAX(status_time) FROM Data where status_time < A.status_time and id = A.id)
AND   A.status = 'THREE' AND NOT B.status = 'TWO'