PostgreSQL - Range-Typen

PostgreSQL - Range-Typen

Donnerstag, 20.07.2017 13:27 Jörg Fechner

Seit der Version 9.2 gibt es die sogenannten RANGE Typen in PostgreSQL.
Bieten diese Typen einen Mehrwert?
Welchen Nutzen haben Sie?

 

Anforderung aus der Fachabteilung

  Aus der Fachabteilung kommen viele Anforderung, die Auswertungen (Reports) der folgenden Art sind:

  • Die Personalabteilung möchte wissen, welcher Mitarbeiter am 13.07.2014 ein Anstellungsverhältnis hatte?
  • Welcher Mitarbeiter war zum 13.07.2014 ausgeschieden?
  • Welche Mitarbeiter haben in den Sommerferien Urlaub?
  • Der Verkauf möchte wissen, welche Produkte kann man bei uns in der Preisspanne von 250 € - 550 € kaufen?
  • Welche Projekte wurden in dem Zeitraum 12.06.2014 bis 13.07.2014 bearbeitet?
  • Welcher Preis hatte der Artikel „Copa del Mundo“ am 13.07.2014? Und wie ist seine Preisentwicklung?
  • Welchen Zustand hatte der Vertrag „Bundestrainer am 12.06.2014 und am 13.07.2014?
  • Welches Auto kann ich mit einem Budget von 12.000 € - 15.000 € erwerben?

  Man sieht also, solche Fragen sind aus dem wahren Leben und nicht konstruiert.

Wie kann man so etwas lösen?

Nun, in den meisten Fällen hat man in der entsprechenden Tabelle ein Min-Wert und einen Max-Wert eingebaut.

  • gueltig_von und gueltig_bis
  • preis_von und preis_bis



Beispiel - Gebrauchtwagenhandel

Betrachten wie einen Gebrauchtwagenhandel. In der Datenbank sind die vorhandenen Autos mit den entsprechen Von- und Bis-Preisen angelegt:

id name preis_von preis_bis
1 Nissan Micra 2.000 4.000
2 Toyota Starlet 5.000 8.000
3 VW Golf 10.000 12.000
4 Toyota Avensis 11.000 15.000
5 Toyota RAV4 12.000 16.000
6 BMW Z4 13.000 18.000
7 Audi R8 28.000 35.000
8 Porsche 911 24.000 29.000

Für die bessere Übersicht habe ich die Tabelle graphisch aufbereitet, um die Überlappung der Preise zu verdeutlichen.



Finde die Autos - I.

Welche Autos sind mit meinem Budget von 13.000 € - 15.000 € zu bekommen?

Hier die zugehörige SQL-Abfrage

SELECT *
FROM gebrauchtwagen
WHERE
 (
 preis_von <= 13000 AND
 preis_von <= 15000 AND
 preis_bis >= 13000 AND
 preis_bis <= 15000
 ) OR
 (
 preis_von <= 13000 AND
 preis_von <= 15000 AND
 preis_bis >= 13000 AND
 preis_bis >= 15000
 ) OR
 (
 preis_von >= 13000 AND
 preis_von <= 15000 AND
 preis_bis >= 13000 AND
 preis_bis <= 15000
 ) OR
 (
 preis_von >= 13000 AND
 preis_von <= 15000 AND
 preis_bis >= 13000 AND
 preis_bis >= 15000
 )
ORDER BY preis_von;

Das Ergebnis sieht wie folgt aus:

id name preis_von preis_bis
4 Toyota Avensis 11.000 15.000
5 Toyota RAV4 12.000 16.000
6 BMW Z4 13.000 18.000

Eine schreibintensive Abfrage und fehleranfällig. Wenn sich das Budget ändert hat man viel zu tippen.



Die Range-Typen

Die Range-Typen beinhalten den Min- und Max-Wert, man benötigt also keine zwei Felder mehr!

Welche Art Range-Typen gibt es bereits in PostgreSQL?

  • int4range — Range of integer

  • int8range — Range of bigint

  • numrange — Range of numeric

  • tsrange — Range of timestamp without time zone

  • tstzrange — Range of timestamp with time zone

  • daterange — Range of date



mathematische Betrachtung

Nehmen wir das Beispiel des int4range.

Wir haben einen Bereich von 3 - 7.

Bei Abfragen ist zu berücksichtigen, ob die 3 und die 7 zu der Ergebnismenge gehören, oder nicht.

Mit den eckigen Klammern [] wird der Wert inklusiv berücksichtigt.

Mit den runden Klammern () wird der Wert exklusiv berücksichtigt.

In der Schreibweise der Mathematik

Range math. Ausdruck
[3, 7] 3 ≤ x ≤ 7
[3, 7) 3 ≤ x < 7
(3, 7] 3 < x ≤ 7
(3, 7) 3 < x < 7

Eine weiterführende Beschreibung gibt es hier



Erweiterung der Gebrauchtwagen Tabelle

Wir fügen ein neues Feld ein. preis_bereich vom Typ int4range.

Hier die DDL:

create table gebrauchtwagen
(
    id serial not null,
    name varchar(50),
    preis_von integer,
    preis_bis integer,
    preis_bereich int4range
)
;

Hier der Inhalt der Daten

id name preis_von preis_bis preis_bereich
1 Nissan Micra 2.000 4.000 [2000,4001)
2 Toyota Starlet 5.000 8.000 [5000,8001)
3 VW Golf 10.000 12.000 [10000,12001)
4 Toyota Avensis 11.000 15.000 [11000,15001)
5 Toyota RAV4 12.000 16.000 [12000,16001)
6 BMW Z4 13.000 18.000 [13000,18001)
7 Audi R8 28.000 35.000 [28000,35001)
8 Porsche 911 24.000 29.000 [24000,29001)



Die Operatoren

Um die Autos zu finden brauche ich in der SQL-Abfrage auch die zugehörigen Opeartoren.

Operator Description Example Result
= equal int4range(1,5) = '[1,4]'::int4range t
<> not equal numrange(1.1,2.2) <> numrange(1.1,2.3) t
< less than int4range(1,10) < int4range(2,3) t
> greater than int4range(1,10) > int4range(1,5) t
<= less than or equal numrange(1.1,2.2) <= numrange(1.1,2.2) t
>= greater than or equal numrange(1.1,2.2) >= numrange(1.1,2.0) t
@> contains range int4range(2,4) @> int4range(2,3) t
@> contains element '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp t
<@ range is contained by int4range(2,4) <@ int4range(1,7) t
<@ element is contained by 42 <@ int4range(1,7) f
&& overlap (have points in common) int8range(3,7) && int8range(4,12) t
<< strictly left of int8range(1,10) << int8range(100,110) t
>> strictly right of int8range(50,60) >> int8range(20,30) t
&< does not extend to the right of int8range(1,20) &< int8range(18,20) t
&> does not extend to the left of int8range(7,20) &> int8range(5,10) t
-|- is adjacent to numrange(1.1,2.2) -|- numrange(2.2,3.3) t
+ union numrange(5,15) + numrange(10,20) [5,20)
* intersection int8range(5,15) * int8range(10,20) [10,15)
- difference int8range(5,15) - int8range(10,20) [5,10)


Hier geht es zu der Dokumentation.



Finde die Autos II.

Jetzt wird in der Abfrage der && overlap operator verwendet. Damit werden zwei Bereiche verglichen, ob diese Schnittpunkte haben.

In unserem Fall lautet die Abfrage:

SELECT *
FROM gebrauchtwagen
WHERE preis_bereich && int4range(13000, 15000, '[]')
ORDER BY lower(preis_bereich);

und wir bekommen exakt das gleiche Ergebnis

id name preis_von preis_bis preis_bereich
4 Toyota Avensis 11.000 15.000 [11000,15001)
5 Toyota RAV4 12.000 16.000 [12000,16001)
6 BMW Z4 13.000 18.000 [13000,18001)



Gibt es günstigere Autos?

Hierfür verwendet man den << strictly left of operator.

Alle Autos, die unterhalb meines Budgets liegen.

SELECT *
FROM gebrauchtwagen
WHERE preis_bereich << int4range(13000, 15000)
ORDER BY lower(preis_bereich);
id name preis_von preis_bis preis_bereich
1 Nissan Micra 2.000 4.000 [2000,4001)
2 Toyota Starlet 5.000 8.000 [5000,8001)
3 VW Golf 10.000 12.000 [10000,12001)



Welche Autos liegen im Bereich meines Budgets, mit den günstigeren

Hierfür verwendet man eine Kombination aus && overlap operator und << strictly left of operator.

Alle Autos die unterhalb und innerhalb meines Budgets liegen.

SELECT *
FROM gebrauchtwagen
WHERE preis_bereich << int4range(13000, 15000) OR preis_bereich && int4range(13000, 15000)
ORDER BY lower(preis_bereich);
id name preis_von preis_bis preis_bereich
1 Nissan Micra 2.000 4.000 [2000,4001)
2 Toyota Starlet 5.000 8.000 [5000,8001)
3 VW Golf 10.000 12.000 [10000,12001)
4 Toyota Avensis 11.000 15.000 [11000,15001)
5 Toyota RAV4 12.000 16.000 [12000,16001)
6 BMW Z4 13.000 18.000 [13000,18001)



Welche Autos liegen im Bereich meines Budgets und darüber hinaus

Hierfür verwendet man eine Kombination aus && overlap operator und >> strictly right of operator.

Alle Autos die innerhalb und oberhalb meines Budgets liegen.

SELECT *
FROM gebrauchtwagen
WHERE preis_bereich >> int4range(13000, 15000) OR preis_bereich && int4range(13000, 15000)
ORDER BY lower(preis_bereich);
id name preis_von preis_bis preis_bereich
4 Toyota Avensis 11.000 15.000 [11000,15001)
5 Toyota RAV4 12.000 16.000 [12000,16001)
6 BMW Z4 13.000 18.000 [13000,18001)
7 Audi R8 28.000 35.000 [28000,35001)
8 Porsche 911 24.000 29.000 [24000,29001)



Fazit

Die Range-Typen erleichtern dem Entwickler erheblich die Arbeit. Gerade in Bezug auf unser Kerngeschäft, der Versicherungssoftware.

Hier haben wir endlich ein Mittel, welches die Historisierung der Daten erheblich erleichert.

vorheriger Eintrag nächster Eintrag