Kategorien:

Fensterfunktionen (Allgemein)

CONDITIONAL_CHANGE_EVENT

Gibt eine Fensterereignisnummer für jede Zeile innerhalb einer Fensterpartition zurück, wenn sich der Wert des Arguments expr1 in der aktuellen Zeile vom Wert von expr1 in der vorherigen Zeile unterscheidet. Die Fensterereignisnummer beginnt bei 0 und wird um 1 erhöht, um die Anzahl der Änderungen innerhalb dieses Fensters anzuzeigen.

Syntax

CONDITIONAL_CHANGE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )

Argumente

expr1

Dies ist ein Ausdruck, der mit dem Ausdruck der vorherigen Zeile verglichen wird.

expr2

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

expr3

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

Nutzungshinweise

  • Der Ausdruck CONDITIONAL_CHANGE_EVENT (expr1) OVER (window_frame) berechnet sich wie folgt:

    CONDITIONAL_TRUE_EVENT( <Ausdruck1> != LAG(<Ausdruck1>) OVER(window_frame)) OVER(window_frame)

    Weitere Informationen zu CONDITIONAL_TRUE_EVENT finden Sie unter CONDITIONAL_TRUE_EVENT.

Beispiele

Hier sehen Sie, wie oft der Strom ausgefallen und wieder eingeschaltet wurde (d. h. wie häufig die Spannung auf 0 gefallen ist oder wiederhergestellt wurde). (In diesem Beispiel wird davon ausgegangen, dass das Abtasten der Spannung alle 15 Minuten ausreichend ist. Da Stromausfälle weniger als 15 Minuten dauern können, möchten Sie in der Regel häufigere Stichproben oder die Abfrageergebnisse als Approximation behandeln.)

Erstellen und Laden der Tabelle:

CREATE TABLE voltage_readings (
  site_id INTEGER,  -- which refrigerator the measurement was taken in
  ts TIMESTAMP,     -- the time at which the temperature was measured
  voltage FLOAT
  );

INSERT INTO voltage_readings (site_id, ts, voltage) VALUES
  (1, '2019-10-30 13:00:00', 120),
  (1, '2019-10-30 13:15:00', 120),
  (1, '2019-10-30 13:30:00',   0),
  (1, '2019-10-30 13:45:00',   0),
  (1, '2019-10-30 14:00:00',   0),
  (1, '2019-10-30 14:15:00',   0),
  (1, '2019-10-30 14:30:00', 120)
  ;

Dies zeigt die Stichproben, für die die Spannung null war, unabhängig davon, ob diese Null-Volt-Ereignisse Teil desselben Stromausfalls oder verschiedener Stromausfälle waren.

SELECT site_id, ts, voltage
  FROM voltage_readings
  WHERE voltage = 0
  ORDER BY ts;

+---------+-------------------------+---------+
| SITE_ID | TS                      | VOLTAGE |
|---------+-------------------------+---------|
|       1 | 2019-10-30 13:30:00.000 |       0 |
|       1 | 2019-10-30 13:45:00.000 |       0 |
|       1 | 2019-10-30 14:00:00.000 |       0 |
|       1 | 2019-10-30 14:15:00.000 |       0 |
+---------+-------------------------+---------+

Dies zeigt die Stichproben zusammen mit einer Spalte, die angibt, ob sich die Spannung geändert hat:

SELECT
    site_id,
    ts,
    voltage,
    CONDITIONAL_CHANGE_EVENT(voltage = 0) OVER (ORDER BY ts) AS power_changes
  FROM voltage_readings;

+---------+-------------------------+---------+---------------+
| SITE_ID | TS                      | VOLTAGE | POWER_CHANGES |
|---------+-------------------------+---------+---------------|
|       1 | 2019-10-30 13:00:00.000 |     120 |             0 |
|       1 | 2019-10-30 13:15:00.000 |     120 |             0 |
|       1 | 2019-10-30 13:30:00.000 |       0 |             1 |
|       1 | 2019-10-30 13:45:00.000 |       0 |             1 |
|       1 | 2019-10-30 14:00:00.000 |       0 |             1 |
|       1 | 2019-10-30 14:15:00.000 |       0 |             1 |
|       1 | 2019-10-30 14:30:00.000 |     120 |             2 |
+---------+-------------------------+---------+---------------+

Dies zeigt die Zeiten, zu denen die Stromversorgung gestoppt und neu gestartet wurde:

WITH power_change_events AS (
  SELECT
      site_id,
      ts,
      voltage,
      CONDITIONAL_CHANGE_EVENT(voltage = 0) OVER (ORDER BY ts) AS power_changes
    FROM voltage_readings
)
SELECT
    site_id,
    MIN(ts),
    voltage,
    power_changes
  FROM power_change_events
  GROUP BY site_id, power_changes, voltage
  ORDER BY 2;

+---------+-------------------------+---------+---------------+
| SITE_ID | MIN(TS)                 | VOLTAGE | POWER_CHANGES |
|---------+-------------------------+---------+---------------|
|       1 | 2019-10-30 13:00:00.000 |     120 |             0 |
|       1 | 2019-10-30 13:30:00.000 |       0 |             1 |
|       1 | 2019-10-30 14:30:00.000 |     120 |             2 |
+---------+-------------------------+---------+---------------+

Dies zeigt, wie oft die Stromversorgung gestoppt und neu gestartet wurde:

WITH power_change_events AS (
  SELECT
      site_id,
      CONDITIONAL_CHANGE_EVENT(voltage = 0) OVER (ORDER BY ts) AS power_changes
    FROM voltage_readings
)
SELECT MAX(power_changes)
  FROM power_change_events
  GROUP BY site_id;

+--------------------+
| MAX(POWER_CHANGES) |
|--------------------|
|                  2 |
+--------------------+

Dieses Beispiel veranschaulicht Folgendes:

  • Die Änderungsnummer innerhalb einer Partition ändert sich jedes Mal, wenn sich der angegebene Wert ändert.

  • NULL-Werte werden nicht als neuer oder geänderter Wert betrachtet.

  • Der Änderungszähler beginnt für jede Partition wieder bei 0.

Erstellen und Laden der 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);

Abfragen der Tabelle:

SELECT province, o_col,
    CONDITIONAL_CHANGE_EVENT(o_col)
      OVER (PARTITION BY province ORDER BY o_col)
        AS change_event
  FROM table1
  ORDER BY province, o_col;

+----------+-------+--------------+
| PROVINCE | O_COL | CHANGE_EVENT |
|----------+-------+--------------|
| Alberta  |     0 |            0 |
| Alberta  |     0 |            0 |
| Alberta  |    13 |            1 |
| Alberta  |    13 |            1 |
| Alberta  |    14 |            2 |
| Alberta  |    15 |            3 |
| Alberta  |  NULL |            3 |
| Manitoba |    30 |            0 |
+----------+-------+--------------+

Das nächste Beispiel zeigt Folgendes:

  • expr1 kann ein anderer Ausdruck als eine Spalte sein. Diese Abfrage verwendet den Ausdruck o_col < 15. Die Ausgabe der Abfrage zeigt an, wann sich der Wert in „o_col“ von einem Wert unter 15 in einen Wert über oder gleich 15 ändert.

  • expr3 muss nicht mit expr1 übereinstimmen. Mit anderen Worten, der Ausdruck in der ORDER BY-Unterklausel der OVER-Klausel muss nicht mit dem Ausdruck in der Funktion CONDITIONAL_CHANGE_EVENT übereinstimmen.

SELECT province, o_col,
    'o_col < 15' AS condition,
    CONDITIONAL_CHANGE_EVENT(o_col)
      OVER (PARTITION BY province ORDER BY o_col)
        AS change_event,
    CONDITIONAL_CHANGE_EVENT(o_col < 15)
      OVER (PARTITION BY province ORDER BY o_col)
        AS change_event_2
  FROM table1
  ORDER BY province, o_col;

+----------+-------+------------+--------------+----------------+
| PROVINCE | O_COL | CONDITION  | CHANGE_EVENT | CHANGE_EVENT_2 |
|----------+-------+------------+--------------+----------------|
| Alberta  |     0 | o_col < 15 |            0 |              0 |
| Alberta  |     0 | o_col < 15 |            0 |              0 |
| Alberta  |    13 | o_col < 15 |            1 |              0 |
| Alberta  |    13 | o_col < 15 |            1 |              0 |
| Alberta  |    14 | o_col < 15 |            2 |              0 |
| Alberta  |    15 | o_col < 15 |            3 |              1 |
| Alberta  |  NULL | o_col < 15 |            3 |              1 |
| Manitoba |    30 | o_col < 15 |            0 |              0 |
+----------+-------+------------+--------------+----------------+

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 behind.
  ('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_CHANGE_EVENT(o) OVER (PARTITION BY p ORDER BY o)
  FROM tbl
  ORDER BY p, o;

+-----+----+--------------------------------------------------------------+
|   P |  O | CONDITIONAL_CHANGE_EVENT(O) OVER (PARTITION BY P ORDER BY O) |
|-----+----+--------------------------------------------------------------|
| 100 |  1 |                                                            0 |
| 100 |  2 |                                                            1 |
| 100 |  3 |                                                            2 |
| 100 |  4 |                                                            3 |
| 100 |  5 |                                                            4 |
| 100 |  6 |                                                            5 |
| 200 |  7 |                                                            0 |
| 200 |  8 |                                                            1 |
| 200 |  9 |                                                            2 |
| 200 | 10 |                                                            3 |
| 200 | 11 |                                                            4 |
| 300 | 12 |                                                            0 |
| 400 | 13 |                                                            0 |
+-----+----+--------------------------------------------------------------+