- Kategorien:
Fensterfunktionen (Allgemein)
CONDITIONAL_TRUE_EVENT¶
Gibt eine Fensterereignisnummer für jede Zeile einer Fensterpartition basierend auf dem Ergebnis des booleschen Arguments expr1 zurück. Die Zahl beginnt bei 0 und wird für jede Zeile, für die der Ausdruck expr1 den Wert „true“ ergibt, um 1 erhöht.
Eine Verwendung dieser Funktion ist das Bilden von Sitzungen für Fensterpartitionen (Sessionisieren). Beispielsweise kann in Click-Stream-Daten ermittelt werden, ob ein Benutzer eine neue Sitzung gestartet hat, indem überprüft wird, ob das letzte Ereignis länger als ein Schwellenwert zurückliegt.
Syntax¶
CONDITIONAL_TRUE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> )
Argumente¶
expr1Dies ist ein boolescher Ausdruck, der den Wert der Fensterereignisnummer ändert, wenn er „true“ ergibt.
expr2Dies ist der optionale Ausdruck, nach dem partitioniert werden soll.
expr3Dies ist der Ausdruck, nach dem in jeder Partition sortiert werden soll.
Nutzungshinweise¶
Der bedingte Ausdruck
expr1kann die rangbezogenen Funktionen LAG und LEAD enthalten, mit denen sich aussagekräftige Fenster erstellen lassen. Falls verwendet müssen diese Funktionen dieselbe OVER-Spezifikation wie CONDITIONAL_TRUE_EVENT verwenden.
Beispiele¶
Das erste Beispiel zeigt Folgendes:
Der Zähler innerhalb einer Partition erhöht sich jedes Mal, wenn die angegebene Spalte TRUE ist (in diesem Fall ungleich Null).
NULL-Werte werden nicht als TRUE-Wert betrachtet.
Der Zähler beginnt bei 0 für jede Partition von vorne.
Erstellen und laden Sie die Tabelle:
CREATE TABLE table1 (province VARCHAR, o_col INTEGER, o2_col INTEGER); INSERT INTO table1 (province, o_col, o2_col) VALUES ('Alberta', 0, 10), ('Alberta', 0, 10), ('Alberta', 13, 10), ('Alberta', 13, 11), ('Alberta', 14, 11), ('Alberta', 15, 12), ('Alberta', NULL, NULL), ('Manitoba', 30, 30);
Fragen Sie die Tabelle ab:
SELECT province, o_col, CONDITIONAL_TRUE_EVENT(o_col) OVER (PARTITION BY province ORDER BY o_col) AS true_event FROM table1 ORDER BY province, o_col ; +----------+-------+------------+ | PROVINCE | O_COL | TRUE_EVENT | |----------+-------+------------| | Alberta | 0 | 0 | | Alberta | 0 | 0 | | Alberta | 13 | 1 | | Alberta | 13 | 2 | | Alberta | 14 | 3 | | Alberta | 15 | 4 | | Alberta | NULL | 4 | | Manitoba | 30 | 1 | +----------+-------+------------+
Das nächste Beispiel zeigt Folgendes:
expr1kann ein anderer Ausdruck als eine Spalte sein. Diese Abfrage verwendet den Ausdrucko_col > 20. Die Ausgabe der Abfrage zeigt an, wann sich der Wert in „o_col“ von einem Wert kleiner oder gleich 20 in einen Wert größer 20 ändert.expr3muss nicht mitexpr1übereinstimmen. Mit anderen Worten, der Ausdruck in der ORDER BY-Unterklausel der OVER-Klausel muss nicht mit dem Ausdruck in der Funktion CONDITIONAL_TRUE_EVENT übereinstimmen.Fragen Sie die Tabelle ab:
SELECT province, o_col, CONDITIONAL_TRUE_EVENT(o_col) OVER (PARTITION BY province ORDER BY o_col) AS true_event, CONDITIONAL_TRUE_EVENT(o_col > 20) OVER (PARTITION BY province ORDER BY o_col) AS true_event_gt_20 FROM table1 ORDER BY province, o_col ; +----------+-------+------------+------------------+ | PROVINCE | O_COL | TRUE_EVENT | TRUE_EVENT_GT_20 | |----------+-------+------------+------------------| | Alberta | 0 | 0 | 0 | | Alberta | 0 | 0 | 0 | | Alberta | 13 | 1 | 0 | | Alberta | 13 | 2 | 0 | | Alberta | 14 | 3 | 0 | | Alberta | 15 | 4 | 0 | | Alberta | NULL | 4 | 0 | | Manitoba | 30 | 1 | 1 | +----------+-------+------------+------------------+
Im nächsten Beispiel wird CONDITIONAL_CHANGE_EVENT mit CONDITIONAL_TRUE_EVENT verglichen:
SELECT province, o_col, CONDITIONAL_CHANGE_EVENT(o_col) OVER (PARTITION BY province ORDER BY o_col) AS change_event, CONDITIONAL_TRUE_EVENT(o_col) OVER (PARTITION BY province ORDER BY o_col) AS true_event FROM table1 ORDER BY province, o_col ; +----------+-------+--------------+------------+ | PROVINCE | O_COL | CHANGE_EVENT | TRUE_EVENT | |----------+-------+--------------+------------| | Alberta | 0 | 0 | 0 | | Alberta | 0 | 0 | 0 | | Alberta | 13 | 1 | 1 | | Alberta | 13 | 1 | 2 | | Alberta | 14 | 2 | 3 | | Alberta | 15 | 3 | 4 | | Alberta | NULL | 3 | 4 | | Manitoba | 30 | 0 | 1 | +----------+-------+--------------+------------+
In diesem Beispiel werden auch CONDITIONAL_CHANGE_EVENT und CONDITIONAL_TRUE_EVENT verglichen:
CREATE TABLE borrowers ( name VARCHAR, status_date DATE, late_balance NUMERIC(11, 2), thirty_day_late_balance NUMERIC(11, 2) ); INSERT INTO borrowers (name, status_date, late_balance, thirty_day_late_balance) VALUES -- Pays late frequently, but catches back up rather than falling further -- behind. ('Geoffrey Flake', '2018-01-01'::DATE, 0.0, 0.0), ('Geoffrey Flake', '2018-02-01'::DATE, 1000.0, 0.0), ('Geoffrey Flake', '2018-03-01'::DATE, 2000.0, 1000.0), ('Geoffrey Flake', '2018-04-01'::DATE, 0.0, 0.0), ('Geoffrey Flake', '2018-05-01'::DATE, 1000.0, 0.0), ('Geoffrey Flake', '2018-06-01'::DATE, 2000.0, 1000.0), ('Geoffrey Flake', '2018-07-01'::DATE, 0.0, 0.0), ('Geoffrey Flake', '2018-08-01'::DATE, 0.0, 0.0), -- Keeps falling further behind. ('Cy Dismal', '2018-01-01'::DATE, 0.0, 0.0), ('Cy Dismal', '2018-02-01'::DATE, 0.0, 0.0), ('Cy Dismal', '2018-03-01'::DATE, 1000.0, 0.0), ('Cy Dismal', '2018-04-01'::DATE, 2000.0, 1000.0), ('Cy Dismal', '2018-05-01'::DATE, 3000.0, 2000.0), ('Cy Dismal', '2018-06-01'::DATE, 4000.0, 3000.0), ('Cy Dismal', '2018-07-01'::DATE, 5000.0, 4000.0), ('Cy Dismal', '2018-08-01'::DATE, 6000.0, 5000.0), -- Fell behind and isn't catching up, but isn't falling further and -- further behind. Essentially, this person just 'failed' once. ('Leslie Safer', '2018-01-01'::DATE, 0.0, 0.0), ('Leslie Safer', '2018-02-01'::DATE, 0.0, 0.0), ('Leslie Safer', '2018-03-01'::DATE, 1000.0, 1000.0), ('Leslie Safer', '2018-04-01'::DATE, 2000.0, 1000.0), ('Leslie Safer', '2018-05-01'::DATE, 2000.0, 1000.0), ('Leslie Safer', '2018-06-01'::DATE, 2000.0, 1000.0), ('Leslie Safer', '2018-07-01'::DATE, 2000.0, 1000.0), ('Leslie Safer', '2018-08-01'::DATE, 2000.0, 1000.0), -- Always pays on time and in full. ('Ida Idyll', '2018-01-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-02-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-03-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-04-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-05-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-06-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-07-01'::DATE, 0.0, 0.0), ('Ida Idyll', '2018-08-01'::DATE, 0.0, 0.0) ;SELECT name, status_date, late_balance AS "OVERDUE", thirty_day_late_balance AS "30 DAYS OVERDUE", CONDITIONAL_CHANGE_EVENT(thirty_day_late_balance) OVER (PARTITION BY name ORDER BY status_date) AS change_event_cnt, CONDITIONAL_TRUE_EVENT(thirty_day_late_balance) OVER (PARTITION BY name ORDER BY status_date) AS true_cnt FROM borrowers ORDER BY name, status_date ; +----------------+-------------+---------+-----------------+------------------+----------+ | NAME | STATUS_DATE | OVERDUE | 30 DAYS OVERDUE | CHANGE_EVENT_CNT | TRUE_CNT | |----------------+-------------+---------+-----------------+------------------+----------| | Cy Dismal | 2018-01-01 | 0.00 | 0.00 | 0 | 0 | | Cy Dismal | 2018-02-01 | 0.00 | 0.00 | 0 | 0 | | Cy Dismal | 2018-03-01 | 1000.00 | 0.00 | 0 | 0 | | Cy Dismal | 2018-04-01 | 2000.00 | 1000.00 | 1 | 1 | | Cy Dismal | 2018-05-01 | 3000.00 | 2000.00 | 2 | 2 | | Cy Dismal | 2018-06-01 | 4000.00 | 3000.00 | 3 | 3 | | Cy Dismal | 2018-07-01 | 5000.00 | 4000.00 | 4 | 4 | | Cy Dismal | 2018-08-01 | 6000.00 | 5000.00 | 5 | 5 | | Geoffrey Flake | 2018-01-01 | 0.00 | 0.00 | 0 | 0 | | Geoffrey Flake | 2018-02-01 | 1000.00 | 0.00 | 0 | 0 | | Geoffrey Flake | 2018-03-01 | 2000.00 | 1000.00 | 1 | 1 | | Geoffrey Flake | 2018-04-01 | 0.00 | 0.00 | 2 | 1 | | Geoffrey Flake | 2018-05-01 | 1000.00 | 0.00 | 2 | 1 | | Geoffrey Flake | 2018-06-01 | 2000.00 | 1000.00 | 3 | 2 | | Geoffrey Flake | 2018-07-01 | 0.00 | 0.00 | 4 | 2 | | Geoffrey Flake | 2018-08-01 | 0.00 | 0.00 | 4 | 2 | | Ida Idyll | 2018-01-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-02-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-03-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-04-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-05-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-06-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-07-01 | 0.00 | 0.00 | 0 | 0 | | Ida Idyll | 2018-08-01 | 0.00 | 0.00 | 0 | 0 | | Leslie Safer | 2018-01-01 | 0.00 | 0.00 | 0 | 0 | | Leslie Safer | 2018-02-01 | 0.00 | 0.00 | 0 | 0 | | Leslie Safer | 2018-03-01 | 1000.00 | 1000.00 | 1 | 1 | | Leslie Safer | 2018-04-01 | 2000.00 | 1000.00 | 1 | 2 | | Leslie Safer | 2018-05-01 | 2000.00 | 1000.00 | 1 | 3 | | Leslie Safer | 2018-06-01 | 2000.00 | 1000.00 | 1 | 4 | | Leslie Safer | 2018-07-01 | 2000.00 | 1000.00 | 1 | 5 | | Leslie Safer | 2018-08-01 | 2000.00 | 1000.00 | 1 | 6 | +----------------+-------------+---------+-----------------+------------------+----------+
Hier ist ein ausführlicheres Beispiel:
CREATE OR REPLACE TABLE tbl
(p int, o int, i int, r int, s varchar(100));
INSERT INTO tbl VALUES
(100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'fourty'),(100,4,NULL,90,'ninety'),(100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
(200,7,7,40,'fourty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),(200,11,NULL,90,'ninety'),
(300,12,12,30,'thirty'),
(400,13,NULL,20,'twenty');
SELECT * FROM tbl ORDER BY p, o, i;
+-----+----+--------+--------+---------+
| P | O | I | R | S |
+-----+----+--------+--------+---------+
| 100 | 1 | 1 | 70 | seventy |
| 100 | 2 | 2 | 30 | thirty |
| 100 | 3 | 3 | 40 | fourty |
| 100 | 4 | [NULL] | 90 | ninety |
| 100 | 5 | 5 | 50 | fifty |
| 100 | 6 | 6 | 30 | thirty |
| 200 | 7 | 7 | 40 | fourty |
| 200 | 8 | [NULL] | [NULL] | n_u_l_l |
| 200 | 9 | [NULL] | [NULL] | n_u_l_l |
| 200 | 10 | 10 | 20 | twenty |
| 200 | 11 | [NULL] | 90 | ninety |
| 300 | 12 | 12 | 30 | thirty |
| 400 | 13 | [NULL] | 20 | twenty |
+-----+----+--------+--------+---------+
SELECT p, o, CONDITIONAL_TRUE_EVENT(o>2) OVER (PARTITION BY p ORDER BY o) FROM tbl ORDER BY p, o;
+-----+----+--------------------------------------------------------------+
| P | O | CONDITIONAL_TRUE_EVENT(O>2) OVER (PARTITION BY P ORDER BY O) |
|-----+----+--------------------------------------------------------------|
| 100 | 1 | 0 |
| 100 | 2 | 0 |
| 100 | 3 | 1 |
| 100 | 4 | 2 |
| 100 | 5 | 3 |
| 100 | 6 | 4 |
| 200 | 7 | 1 |
| 200 | 8 | 2 |
| 200 | 9 | 3 |
| 200 | 10 | 4 |
| 200 | 11 | 5 |
| 300 | 12 | 1 |
| 400 | 13 | 1 |
+-----+----+--------------------------------------------------------------+
SELECT p, o, CONDITIONAL_TRUE_EVENT(LAG(O) OVER (PARTITION BY P ORDER BY O) >1) OVER (PARTITION BY P ORDER BY O) FROM tbl ORDER BY p, o;
+-----+----+-----------------------------------------------------------------------------------------------------+
| P | O | CONDITIONAL_TRUE_EVENT(LAG(O) OVER (PARTITION BY P ORDER BY O) >1) OVER (PARTITION BY P ORDER BY O) |
|-----+----+-----------------------------------------------------------------------------------------------------|
| 100 | 1 | 0 |
| 100 | 2 | 0 |
| 100 | 3 | 1 |
| 100 | 4 | 2 |
| 100 | 5 | 3 |
| 100 | 6 | 4 |
| 200 | 7 | 0 |
| 200 | 8 | 1 |
| 200 | 9 | 2 |
| 200 | 10 | 3 |
| 200 | 11 | 4 |
| 300 | 12 | 0 |
| 400 | 13 | 0 |
+-----+----+-----------------------------------------------------------------------------------------------------+