Założenia / wyjaśnienia
Nie ma potrzeby rozróżniania infinityi otwierania górnej granicy ( upper(range) IS NULL). (Możesz to zrobić w obie strony, ale w ten sposób jest to prostsze.)
Ponieważ datejest to dyskretny typ, wszystkie zakresy mają domyślne [)granice.
Według dokumentacji:
Wbudowany rodzaju zakres int4range, int8rangei daterangecałkowitego zużycia postaci kanonicznej, który zawiera dolną granicę i górną granicę obejmuje; to znaczy [),.
W przypadku innych typów (jak tsrange!) Egzekwowałbym to samo, jeśli to możliwe:
Rozwiązanie z czystym SQL
Z CTE dla jasności:
WITH a AS (
SELECT range
, COALESCE(lower(range),'-infinity') AS startdate
, max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
FROM test
)
, b AS (
SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
FROM a
)
, c AS (
SELECT *, count(step) OVER (ORDER BY range) AS grp
FROM b
)
SELECT daterange(min(startdate), max(enddate)) AS range
FROM c
GROUP BY grp
ORDER BY 1;
Lub to samo z podkwerendami, szybsze, ale mniej łatwe do odczytania:
SELECT daterange(min(startdate), max(enddate)) AS range
FROM (
SELECT *, count(step) OVER (ORDER BY range) AS grp
FROM (
SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
FROM (
SELECT range
, COALESCE(lower(range),'-infinity') AS startdate
, max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
FROM test
) a
) b
) c
GROUP BY grp
ORDER BY 1;
Lub z jednym poziomem podkwerend mniejszym, ale z odwróconą kolejnością sortowania:
SELECT daterange(min(COALESCE(lower(range), '-infinity')), max(enddate)) AS range
FROM (
SELECT *, count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
FROM (
SELECT range
, max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
, lead(lower(range)) OVER (ORDER BY range) As nextstart
FROM test
) a
) b
GROUP BY grp
ORDER BY 1;
- Posortuj okno w drugim kroku za pomocą
ORDER BY range DESC NULLS LAST(z NULLS LAST), aby uzyskać idealnie odwróconą kolejność sortowania. Powinno to być tańsze (łatwiejsze w produkcji, idealnie pasuje do kolejności sortowania sugerowanego indeksu) i dokładne dla przypadków narożnych rank IS NULL.
Wyjaśnić
a: Przy zamawianiu przez range, oblicz maksymalną liczbę górną granicę ( enddate) za pomocą funkcji okna.
Zastąp granice NULL (niezwiązane) +/- infinitytylko dla uproszczenia (bez specjalnych przypadków NULL).
b: W tym samym porządku sortowania, jeśli poprzedni enddatejest wcześniejszy niż startdatemamy przerwę i rozpoczynamy nowy zakres ( step).
Pamiętaj, że górna granica jest zawsze wykluczona.
c: Utwórz grupy ( grp), licząc kroki za pomocą innej funkcji okna.
W zewnętrznej SELECTkompilacji waha się od dolnej do górnej granicy w każdej grupie. Voilá.
Ściśle związana odpowiedź na SO z dodatkowymi wyjaśnieniami:
Rozwiązanie proceduralne z plpgsql
Działa dla dowolnej nazwy tabeli / kolumny, ale tylko dla typu daterange.
Rozwiązania proceduralne z pętlami są zwykle wolniejsze, ale w tym szczególnym przypadku oczekuję, że funkcja będzie znacznie szybsza, ponieważ wymaga tylko jednego sekwencyjnego skanowania :
CREATE OR REPLACE FUNCTION f_range_agg(_tbl text, _col text)
RETURNS SETOF daterange AS
$func$
DECLARE
_lower date;
_upper date;
_enddate date;
_startdate date;
BEGIN
FOR _lower, _upper IN EXECUTE
format($$SELECT COALESCE(lower(t.%2$I),'-infinity') -- replace NULL with ...
, COALESCE(upper(t.%2$I), 'infinity') -- ... +/- infinity
FROM %1$I t
ORDER BY t.%2$I$$
, _tbl, _col)
LOOP
IF _lower > _enddate THEN -- return previous range
RETURN NEXT daterange(_startdate, _enddate);
SELECT _lower, _upper INTO _startdate, _enddate;
ELSIF _upper > _enddate THEN -- expand range
_enddate := _upper;
-- do nothing if _upper <= _enddate (range already included) ...
ELSIF _enddate IS NULL THEN -- init 1st round
SELECT _lower, _upper INTO _startdate, _enddate;
END IF;
END LOOP;
IF FOUND THEN -- return last row
RETURN NEXT daterange(_startdate, _enddate);
END IF;
END
$func$ LANGUAGE plpgsql;
Połączenie:
SELECT * FROM f_range_agg('test', 'range'); -- table and column name
Logika jest podobna do rozwiązań SQL, ale możemy zrobić to za jednym przejściem.
SQL Fiddle.
Związane z:
Zwykłe ćwiczenie do obsługi danych wejściowych użytkownika w dynamicznym SQL:
Indeks
Dla każdego z tych rozwiązań zwykły (domyślny) indeks btree rangebyłby kluczowy dla wydajności w dużych tabelach:
CREATE INDEX foo on test (range);
Indeks btree ma ograniczone zastosowanie dla typów zakresów , ale możemy uzyskać wstępnie posortowane dane, a może nawet skanowanie tylko indeksu.