RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression
O fereastră RANGE BETWEEN n PRECEDING ... măsoară distanța pe valoarea coloanei din ORDER BY, deci are nevoie de exact o coloană acolo. Lasă o singură coloană în ORDER BY-ul ferestrei sau folosește ROWS BETWEEN n PRECEDING dacă vrei ultimele n rânduri.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Într-o fereastră, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW înseamnă „rândul curent și cele două rânduri dinainte”. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW înseamnă altceva: „rândurile a căror valoare din ORDER BY e cu cel mult 2 mai mică decât valoarea curentă”. De exemplu, cu ORDER BY zi, ultimele 3 zile, chiar dacă în unele zile nu e niciun rând.
Ca să calculeze „cu 2 mai mic”, SQLite are nevoie de o singură valoare după care să măsoare. Dacă ORDER BY din fereastră are două coloane (ORDER BY zi, id) sau lipsește, nu știe pe care să o folosească și dă acest mesaj.
RANGE fără offset (UNBOUNDED PRECEDING, CURRENT ROW) merge cu oricâte coloane.
De ce apare: cauzele frecvente
1Două coloane în ORDER BY cu RANGE
SELECT zi, SUM(vizite) OVER (
ORDER BY zi, id
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
) FROM trafic;SELECT zi, SUM(vizite) OVER (
ORDER BY zi
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
) FROM trafic;Cu o singură coloană, 2 PRECEDING înseamnă clar „zi mai mică cu cel mult 2”.
2Voiai de fapt ultimele n rânduri
SELECT zi, AVG(vizite) OVER (
ORDER BY zi, id
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
) FROM trafic;SELECT zi, AVG(vizite) OVER (
ORDER BY zi, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) FROM trafic;ROWS numără rânduri, nu valori, deci merge cu orice ORDER BY.
Repară eroarea, direct în pagină
Vrem pentru fiecare zi totalul vizitelor din ultimele 3 zile (ziua curentă și cele două dinainte), după numărul zilei, chiar dacă unele zile lipsesc. Afișează zi și total_3_zile, ordonat după zi. Interogarea dă eroarea despre RANGE. Repar-o păstrând RANGE.
SELECT zi, SUM(vizite) OVER (
ORDER BY zi, id
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
) AS total_3_zile
FROM trafic
ORDER BY zi;zi | total_3_zile 1 | 100 2 | 220 4 | 210 5 | 200 6 | 330
Indiciu
RANGE măsoară distanța pe o singură coloană.
Reparația
SELECT zi, SUM(vizite) OVER (
ORDER BY zi
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
) AS total_3_zile
FROM trafic
ORDER BY zi;De ce merge
RANGE BETWEEN 2 PRECEDING ia rândurile cu zi între zi - 2 și ziua curentă, deci are nevoie de o singură coloană după care să măsoare. Cu ORDER BY zi, id nu știa ce înseamnă „cu 2 mai mic”.
Cu ORDER BY zi: ziua 4 adună zilele 2 și 4 (ziua 3 lipsește), adică 210, iar ziua 6 adună 4, 5 și 6, adică 330.
Alte forme ale mesajului
ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY columnERROR 3587 (HY000): Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type.
Cum o eviți pe viitor
Alege conștient între ROWS și RANGE. Pentru „ultimele 3 rânduri” (medie mobilă pe rânduri) folosește ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Pentru „ultimele 3 zile” folosește RANGE cu o singură coloană numerică în ORDER BY (de exemplu numărul zilei sau julianday(data)). Datele text de tip '2024-03-01' nu se scad: transformă-le în număr.
Întrebări frecvente
Care e diferența practică dintre ROWS și RANGE?
Dacă lipsește o zi, ROWS BETWEEN 2 PRECEDING ia oricum 3 rânduri, deci poate acoperi 4 zile. RANGE ia doar zilele din interval, deci uneori mai puține rânduri.
Pot folosi RANGE cu date calendaristice?
Da, dacă ordonezi după o valoare numerică: ORDER BY julianday(data) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW înseamnă ultimele 7 zile.
Erori înrudite
unsupported frame specification Cadrul ferestrei (ROWS/RANGE BETWEEN ... AND ...) are capetele în ordinea greșită: începutul vine după sfârșit, de exemplu BETWEEN CURRENT ROW AND 2 PRECEDING. Scrie întâi capătul din trecut, apoi pe cel din viitor: BETWEEN 2 PRECEDING AND CURRENT ROW. misuse of window function ROW_NUMBER() O funcție fereastră (ROW_NUMBER, RANK, LAG) e folosită în WHERE, GROUP BY sau fără OVER. Funcțiile fereastră se calculează după WHERE, deci nu poți filtra direct cu ele. Calculează-le într-o subinterogare sau CTE și filtrează rezultatul în interogarea exterioară. no such window: w Ai scris OVER w, dar fereastra w nu e definită. Adaugă clauza WINDOW w AS (...) după WHERE / GROUP BY / HAVING și înainte de ORDER BY, sau scrie fereastra direct: OVER (PARTITION BY ... ORDER BY ...). Învață SQL de la zero, cu exerciții la fiecare lecție
Curs SQL: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 15 lecții sunt gratuite.
