CONDITIONAL_CHANGE_EVENT¶

Sintaxe¶

CONDITIONAL_CHANGE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> ) 

Argumentos¶

expr1

Esta é uma expressão que se compara com a expressão da linha anterior.

expr2

Esta é a expressão opcional para divisão.

expr3

Esta é a expressão a ordenar dentro de cada partição.

Notas de uso¶

• A expressão CONDITIONAL_CHANGE_EVENT (expr1) OVER (window_frame) é calculada como:

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

  Para obter mais informações sobre CONDITIONAL_TRUE_EVENT, consulte CONDITIONAL_TRUE_EVENT.

Exemplos¶

Isto mostra como detectar o número de vezes que a energia falhou e foi ligada novamente (ou seja, o número de vezes que a tensão caiu para 0 ou foi restaurada). (Este exemplo considera que a amostragem da tensão a cada 15 minutos é suficiente. Como as falhas de energia podem durar menos de 15 minutos, você normalmente desejaria amostras mais frequentes, ou trataria os resultados da consulta como uma aproximação).

Criar e carregar a tabela:

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

Copy

Isto mostra as amostras para as quais a voltagem era zero, se esses eventos de voltagem zero eram ou não parte da mesma falha de energia ou falhas de energia diferentes.

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

Copy

Isto mostra as amostras, juntamente com uma coluna indicando se a tensão mudou:

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

Copy

Isto mostra as horas em que a energia parou e reiniciou:

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

Copy

Isto mostra quantas vezes a energia parou e reiniciou:

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

Copy

Este exemplo ilustra isso:

• O número de mudança dentro de uma partição muda cada vez que o valor especificado muda.

• Valores NULL não são considerados valores novos ou alterados.

• A contagem das mudanças começa novamente em 0 para cada partição.

  Criar e carregar a tabela:

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

  Copy

  Consultar a tabela:

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

  Copy

O próximo exemplo mostra isso:

• expr1 pode ser uma expressão que não seja uma coluna. Esta consulta usa a expressão o_col < 15, e a saída da consulta mostra quando o valor em o_col muda de um valor menor que 15 para um valor maior ou igual a 15.

• expr3 não precisa ser compatível com expr1. Em outras palavras, a expressão na subcláusula ORDER BY da cláusula OVER não precisa corresponder à expressão na função CONDITIONAL_CHANGE_EVENT.

  Consultar a tabela:

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

  Copy

O próximo exemplo compara CONDITIONAL_CHANGE_EVENT e 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 | +----------+-------+--------------+------------+ 

Copy

Este exemplo também compara CONDITIONAL_CHANGE_EVENT e 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 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) ; 

Copy

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

Copy

Aqui está um exemplo mais extenso:

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