类别：

:doc:`/sql-reference/functions-window`（通用）

CONDITIONAL_CHANGE_EVENT

如果当前行中 expr1 实参的值与前一行中 expr1 的值不同，则返回窗口分区内每行的窗口事件编号。窗口事件编号从 0 开始并以 1 递增，以指明该窗口内到目前为止的变更数。

语法

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

实参

expr1

这是与上一行的表达式进行比较的表达式。

expr2

这是用于划分分区的可选表达式。

expr3

这是每个分区中作为排序规则的表达式。

使用说明

  • 表达式 CONDITIONAL_CHANGE_EVENT (expr1) OVER (window_frame) 的计算公式为：

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

    有关 CONDITIONAL_TRUE_EVENT 的更多信息，请参阅 CONDITIONAL_TRUE_EVENT。

示例

此示例展示如何检测电源故障和重新打开的次数（即，电压降至 0 或恢复的次数）。（此示例假设每 15 分钟对电压进行一次采样就足够了。由于电源故障的持续时间可能少于 15 分钟，因此您通常需要更频繁的采样，或者希望将查询结果视为近似值。）

创建并加载表：

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)
  ;

此示例显示电压为零的样本，无论这些零电压事件属于相同的电源故障，还是属于不同的电源故障。

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 |
+---------+-------------------------+---------+

此示例显示样本，以及指示电压是否更改的列：

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 |
+---------+-------------------------+---------+---------------+

此示例显示电源停止和重新启动的时间：

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 |
+---------+-------------------------+---------+---------------+

此示例显示电源停止和重新启动的次数：

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 |
+--------------------+

此示例说明：

  • 每次指定值更改时，分区中的变更号也会更改。

  • NULL 值不被视为新值或更改值。

  • 每个分区的变更计数从 0 开始。

创建并加载表：

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);

查询表：

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 |
+----------+-------+--------------+

下一个示例显示以下情况：

  • expr1 可以是列以外的表达式。此查询使用表达式 o_col < 15，并且查询的输出显示 o_col 中的值何时从小于 15 的值更改为大于或等于 15 的值。

  • expr3 不需要匹配 expr1。 换句话说，OVER 子句的 ORDER BY 分子句中表达式不需要与 CONDITIONAL_CHANGE_EVENT 函数中的表达式匹配。

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 |
+----------+-------+------------+--------------+----------------+

下一个示例比较 CONDITIONAL_CHANGE_EVENT 和 CONDITIONAL_TRUE_EVENT：

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 |
+----------+-------+--------------+------------+

此示例还比较 CONDITIONAL_CHANGE_EVENT 和 CONDITIONAL_TRUE_EVENT：

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 |
+----------------+-------------+---------+-----------------+------------------+----------+

下面是更广泛的示例：

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 |
+-----+----+--------------------------------------------------------------+