dexblog.ro
Acces rapid: Învață AI Învață Programare Specificații Dispozitive Comparații
SQLITE Toate comenzile SQLite

ROW_NUMBER, RANK, LAG (funcții fereastră) în SQLite

Interogări avansate SQLite 3.25.0

Calculează pentru fiecare rând un număr de ordine, un loc în clasament sau valoarea din rândul vecin, fără să grupeze rândurile.

Sintaxă 
functie() OVER ([PARTITION BY coloane] [ORDER BY coloane])
functie() OVER nume_fereastra ... WINDOW nume_fereastra AS (...)
-- row_number() rank() dense_rank() ntile(n)
-- lag(x [, pasi [, implicit]]) lead(x [, pasi [, implicit]])
-- first_value(x) last_value(x) nth_value(x, n)

Ce face ROW_NUMBER, RANK, LAG (funcții fereastră)

O funcție fereastră se calculează pentru fiecare rând, privind la un grup de rânduri înrudite (fereastra). Spre deosebire de GROUP BY, rândurile nu se comprimă: fiecare rând rămâne în rezultat și primește o valoare în plus. Fereastra se descrie după OVER: PARTITION BY împarte rândurile în grupuri (de exemplu pe clase), iar ORDER BY stabilește ordinea în fiecare grup.

row_number() numerotează 1, 2, 3, … fără repetiții. rank() dă același loc la valori egale și sare peste locurile următoare (1, 2, 2, 4), iar dense_rank() nu sare (1, 2, 2, 3). lag(x) și lead(x) întorc valoarea lui x din rândul anterior, respectiv următor, util pentru diferențe de la o lună la alta.

Dacă folosești aceeași fereastră de mai multe ori, o poți numi cu WINDOW w AS (...) la finalul interogării și scrie OVER w. Funcțiile fereastră pot apărea doar în lista SELECT și în ORDER BY; ca să filtrezi după ele, pune interogarea într-un CTE sau într-o subinterogare.

Parametri

Parametru Descriere
PARTITION BY Opțional: împarte rândurile în grupuri independente; numerotarea o ia de la capăt în fiecare grup.
ORDER BY Ordinea rândurilor în fereastră; obligatorie pentru clasamente și pentru lag/lead.
pasi, implicit La lag/lead: câte rânduri înapoi/înainte (implicit 1) și valoarea întoarsă când rândul nu există (implicit NULL).

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE note (
  nume  TEXT NOT NULL,
  clasa TEXT NOT NULL,
  nota  INTEGER NOT NULL
);
INSERT INTO note VALUES
  ('Ana',   '9A', 10),
  ('Mihai', '9A', 9),
  ('Elena', '9A', 9),
  ('Radu',  '9A', 7),
  ('Ioana', '9B', 10),
  ('Dan',   '9B', 8),
  ('Mara',  '9B', 6);

Exemplul 1: Trei feluri de clasament

Ana și Ioana au 10, Elena și Mihai au 9. row_number le dă totuși numere diferite (între egali decide numele, adăugat în ORDER BY). rank și dense_rank folosesc fereastra numită w, ordonată doar după notă: rank sare de la 1 la 3, dense_rank continuă cu 2.

SELECT nume, nota,
       row_number() OVER (ORDER BY nota DESC, nume) AS nr,
       rank()       OVER w AS loc,
       dense_rank() OVER w AS loc_dens
FROM note
WINDOW w AS (ORDER BY nota DESC)
ORDER BY nr;

Rezultat

╭───────┬──────┬────┬─────┬──────────╮
│ nume  │ nota │ nr │ loc │ loc_dens │
╞═══════╪══════╪════╪═════╪══════════╡
│ Ana   │   10 │  1 │   1 │        1 │
│ Ioana │   10 │  2 │   1 │        1 │
│ Elena │    9 │  3 │   3 │        2 │
│ Mihai │    9 │  4 │   3 │        2 │
│ Dan   │    8 │  5 │   5 │        3 │
│ Radu  │    7 │  6 │   6 │        4 │
│ Mara  │    6 │  7 │   7 │        5 │
╰───────┴──────┴────┴─────┴──────────╯

Exemplul 2: Primul din fiecare clasă

PARTITION BY clasa face clasamentul separat pentru 9A și 9B. Filtrarea după loc = 1 se face în afara CTE-ului, pentru că WHERE nu vede funcțiile fereastră.

WITH clasament AS (
  SELECT nume, clasa, nota,
         rank() OVER (PARTITION BY clasa ORDER BY nota DESC) AS loc
  FROM note
)
SELECT clasa, nume, nota FROM clasament
WHERE loc = 1
ORDER BY clasa;

Rezultat

╭───────┬───────┬──────╮
│ clasa │ nume  │ nota │
╞═══════╪═══════╪══════╡
│ 9A    │ Ana   │   10 │
│ 9B    │ Ioana │   10 │
╰───────┴───────┴──────╯

Exemplul 3: Diferența față de luna trecută cu lag

lag(suma) aduce suma din luna anterioară. Pentru ianuarie nu există lună anterioară, deci primești NULL (celulă goală).

CREATE TABLE lunar (luna TEXT, suma INTEGER);
INSERT INTO lunar VALUES
  ('2026-01', 4200), ('2026-02', 3900), ('2026-03', 5100), ('2026-04', 5100);

SELECT luna, suma,
       lag(suma) OVER (ORDER BY luna) AS luna_trecuta,
       suma - lag(suma) OVER (ORDER BY luna) AS diferenta,
       lead(luna) OVER (ORDER BY luna) AS urmatoarea
FROM lunar
ORDER BY luna;

Rezultat

╭─────────┬──────┬──────────────┬───────────┬────────────╮
│  luna   │ suma │ luna_trecuta │ diferenta │ urmatoarea │
╞═════════╪══════╪══════════════╪═══════════╪════════════╡
│ 2026-01 │ 4200 │              │           │ 2026-02    │
│ 2026-02 │ 3900 │         4200 │      -300 │ 2026-03    │
│ 2026-03 │ 5100 │         3900 │      1200 │ 2026-04    │
│ 2026-04 │ 5100 │         5100 │         0 │            │
╰─────────┴──────┴──────────────┴───────────┴────────────╯

Greșeli frecvente

  • Scrii WHERE row_number() OVER (...) = 1. Funcțiile fereastră se calculează după WHERE, deci SQLite dă eroare; filtrează într-un CTE sau într-o subinterogare.
  • Folosești row_number() fără un ORDER BY complet. La valori egale ordinea nu este garantată și numerele se pot schimba de la o rulare la alta; adaugă o coloană care desparte egalitățile.
  • Rulezi pe un SQLite mai vechi de 3.25.0, de exemplu pe un Android vechi. Funcțiile fereastră nu există acolo; verifică versiunea cu sqlite_version().

Comenzi înrudite

  • SUM() OVER: totaluri cumulate și medii mobile: Folosește SUM, AVG, COUNT, MIN și MAX ca funcții fereastră: totaluri cumulate, medii pe ultimele zile, procente din total.
  • WITH (CTE): Dă un nume unei subinterogări (Common Table Expression) și o folosește apoi ca pe un tabel, ca interogarea să fie mai ușor de citit.
  • group_concat / string_agg: Lipește într-un singur text valorile dintr-un grup de rânduri, cu separatorul ales, de exemplu toți elevii unui curs.