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.