Kategorien:

Fensterfunktionen (Allgemein)

CONDITIONAL_TRUE_EVENT

Gibt eine Fensterereignisnummer für jede Zeile einer Fensterpartition basierend auf dem Ergebnis des booleschen Arguments Ausdruck1 zurück. Die Zahl beginnt bei 0 und wird für jede Zeile, für die der Ausdruck 1 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

Ausdruck1

Dies ist ein boolescher Ausdruck, der den Wert der Fensterereignisnummer ändert, wenn er „true“ ergibt.

Ausdruck2

Dies ist der optionale Ausdruck, nach dem partitioniert werden soll.

Ausdruck3

Dies ist der Ausdruck, nach dem in jeder Partition sortiert werden soll.

Nutzungshinweise

  • Der bedingte Ausdruck Ausdruck1 kann die Analysefunktionen 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:

  • Ausdruck1 kann ein anderer Ausdruck als eine Spalte sein. Diese Abfrage verwendet den Ausdruck o_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.

  • Ausdruck3 muss nicht mit Ausdruck1 ü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 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 |
+-----+----+-----------------------------------------------------------------------------------------------------+