Częstym problemem jaki należy rozwiązać jest uzyskanie rekordu pierwszego, drugiego lub n-ego w trakcie grupowania. Problem nie jest trywialny, a istnieje szereg metod by sobie z nim poradzić. Same wbudowane funkcje agregujące to za mało, ponieważ oprócz braku funkcji first i last musimy mieć także coś, co nada porządek podczas grupowania (który jest potrzebny, aby mówić o “pierwszym” rekordzie). Pół biedy jak mamy pole LP, ale jak sobie radzić gdy nie mamy? Da się jednak uzyskać pożądany efekt z wykorzystaniem funkcji analitycznych, a z wykorzystaniem dodatkowego warunku uzyskać wynik jak przy grupowaniu.
Rozpatrzmy przykładowe dane
CREATE TABLE TEST (LP INTEGER, WARTOSC VARCHAR(10));
INSERT INTO TEST VALUES (1, 'A');
INSERT INTO TEST VALUES (2, 'B');
INSERT INTO TEST VALUES (3, 'C');
INSERT INTO TEST VALUES (4, 'D');
Można tak:
select max(case when lp = 1 then wartosc else null end) as pierwszy,
max(case when lp = 2 then wartosc else null end) as drugi,
max(case when lp = 4 then wartosc else null end) as czwarty
-- dla ostatniego nie znamy LP
from test;
Powyższe jest ok jeśli mamy kolumne LP zawsze numerującą od 1, niepowtarzającą się i ciągłą, oraz nie interesuje nas ostatni rekord.
Jeśli nie interesuje nas n-ty rekord, a jedynie pierwszy i ostatni wynik możemy uzyskać tak:
CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1; $$;
CREATE AGGREGATE first (
sfunc = first_agg,
basetype = anyelement,
stype = anyelement
);
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $2; $$;
CREATE AGGREGATE last (
sfunc = last_agg,
basetype = anyelement,
stype = anyelement
);
Takie zapytanie zadziała:
select first(wartosc), last(wartosc) from test;
ale z uwagi na niepewność co do porządku rekordów przekazywanych do funkcji lepiej użyć
select first(wartosc ORDER BY lp), last(wartosc ORDER BY lp) from test;
Jeśli potrzebny jest nam n-ty rekord i nie mamy kolumny LP – można nadać ją samemu z wykorzystaniem podzapytania i funkcji analitycznych:
select max(case when lp_asc = 1 then wartosc else null end) as pierwszy,
max(case when lp_asc = 2 then wartosc else null end) as drugi,
max(case when lp_asc = 4 then wartosc else null end) as czwarty,
max(case when lp_desc = 1 then wartosc else null end) as ostatni
from (select row_number() over (order by lp asc) lp_asc, row_number() over (order by lp desc) lp_desc, test.* from test) q;
Można także wogóle pominąć grupowanie i wykorzystać same funkcje analityczne: trzeba jednak przedefiniować domyślne okno i pozostawić finalnie tylko jeden rekord (pamiętajmy, że inaczej niż dla funkcji agregujących, które zwracają tyle wyników ile jest grup, funkcje analityczne zwracają tyle wyników ile jest rekordów). To pociąga za sobą konieczność wykorzystania podzapytania:
SELECT * FROM (
SELECT FIRST_VALUE(wartosc) OVER w as pierwsza,
LEAD(wartosc, 1) OVER w as druga,
NTH_VALUE(wartosc, 4) OVER w as czwarta,
LAST_VALUE(wartosc) OVER w as czwarta,
ROW_NUMBER() OVER w as lp
FROM TEST
WINDOW w AS (ORDER BY LP ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) q
WHERE lp = 1
Trzeba oddać sprawiedliwość, że nie jest to klasyczna agregacja, ale uzyskany efekt wydaje się w pełni zadowalający.
W przypadku, gdyby występowała potrzeba pogrupowania danych można to zrobić następująco:
CREATE TABLE TEST2 (LP INTEGER, GRUPA INTEGER, WARTOSC VARCHAR(10));
INSERT INTO TEST2 VALUES (1, 1, 'A');
INSERT INTO TEST2 VALUES (2, 1, 'B');
INSERT INTO TEST2 VALUES (3, 1, 'C');
INSERT INTO TEST2 VALUES (4, 1, 'D');
INSERT INTO TEST2 VALUES (5, 2, 'X');
INSERT INTO TEST2 VALUES (6, 2, 'Y');
INSERT INTO TEST2 VALUES (7, 2, 'Z');
INSERT INTO TEST2 VALUES (8, 2, 'Ż');
Z wykorzystaniem funkcji agregujących first i last zdefiniowanych wcześniej:
select first(wartosc ORDER BY lp), last(wartosc ORDER BY lp) from test2
group by grupa;
Lub z użyciem funkcji analitycznych i wygenerowanego lp
select max(case when lp_asc = 1 then wartosc else null end) as pierwszy,
max(case when lp_asc = 2 then wartosc else null end) as drugi,
max(case when lp_asc = 4 then wartosc else null end) as czwarty,
max(case when lp_desc = 1 then wartosc else null end) as ostatni
from (select row_number() over (partition by grupa order by lp asc) lp_asc,
row_number() over (partition by grupa order by lp desc) lp_desc, test2.* from test2) q
group by grupa;
lub zapytanie emulujące grupowanie funkcjami analitycznymi i warunkiem where:
SELECT * FROM (
SELECT GRUPA,
FIRST_VALUE(wartosc) OVER w as pierwsza,
LEAD(wartosc, 1) OVER w as druga,
NTH_VALUE(wartosc, 4) OVER w as czwarta,
LAST_VALUE(wartosc) OVER w as ostatnia,
ROW_NUMBER() OVER w as lp
FROM TEST2
WINDOW w AS (PARTITION BY GRUPA ORDER BY LP ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) q
WHERE lp = 1
