SQL
Window-functies

Het toevoegen van het totale aantal geselecteerde rijen aan elke rij

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

In plaats van twee query's te gebruiken om een telling dan de regel te krijgen, kunt u een aggregatie als vensterfunctie gebruiken en het volledige resultaatset als venster gebruiken.
Dit kan worden gebruikt als basis voor verdere berekening zonder de complexiteit van extra self-joins.

Een vlag instellen als andere rijen een gemeenschappelijke eigenschap hebben

Laten we zeggen dat ik deze gegevens heb:

Tafel items

Ik wil graag al die regels krijgen en weten of een tag door andere regels wordt gebruikt

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

Het resultaat zal zijn:

Als uw database geen OVER en PARTITION heeft, kunt u dit gebruiken om hetzelfde resultaat te produceren:

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

Een lopend totaal krijgen

Gezien deze gegevens:

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

zal je geven

Verkrijg de N meest recente rijen over meerdere groepen

Gezien deze gegevens

;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

Met n = 1 krijg je de meest recente rij per user_id :

"Out-of-sequence" -records zoeken met de functie LAG ()

Gezien deze voorbeeldgegevens:

Items geïdentificeerd door ID waarden moeten in volgorde van STATUS 'EEN' naar 'TWEE' naar 'DRIE' gaan, zonder statussen over te slaan. Het probleem is om gebruikerswaarden ( STATUS_BY ) te vinden die de regel overtreden en onmiddellijk van 'EEN' naar 'DRIE' gaan.

De analytische functie LAG() helpt het probleem op te lossen door voor elke rij de waarde in de vorige rij te retourneren:

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'

Als uw database geen LAG () heeft, kunt u dit gebruiken om hetzelfde resultaat te produceren:

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'