- Categorias:
Funções de janela (General)
CONDITIONAL_TRUE_EVENT¶
Retorna um número de evento de janela para cada linha dentro de uma partição de janela com base no resultado do argumento booleano expr1. O número começa a partir de 0 e é incrementado por 1 para cada linha em que o expr1 avalia como verdadeiro.
Um dos usos desta função é a sessão de partições de janelas. Por exemplo, nos dados do fluxo de clique, ele pode ser usado para determinar se um usuário iniciou uma nova sessão, verificando se o último evento foi há mais tempo do que um limite.
Sintaxe¶
CONDITIONAL_TRUE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> )
Argumentos¶
expr1Esta é uma expressão booleana que muda o valor do número de eventos da janela quando ela avalia como true.
expr2Esta é a expressão opcional para divisão.
expr3Esta é a expressão a ordenar dentro de cada partição.
Notas de uso¶
A expressão condicional
expr1pode conter as funções relacionadas a classificaçãoLAG e LEAD, que nos permitem criar janelas mais expressivas. Se usadas, estas funções têm que usar a mesma especificação OVER que a CONDITIONAL_TRUE_EVENT.
Exemplos¶
O primeiro exemplo ilustra isso:
O número dentro de uma partição aumenta cada vez que a coluna especificada é TRUE (diferente de zero, neste caso).
Valores NULL não são considerados um valor TRUE.
O número 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);
Consultar a tabela:
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 | +----------+-------+------------+
O próximo exemplo mostra isso:
expr1pode ser uma expressão que não seja uma coluna. Esta consulta usa a expressãoo_col > 20, e a saída da consulta mostra quando o valor em o_col muda de um valor menor ou igual a 20 para um valor maior que 20.expr3não precisa ser compatível comexpr1. 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_TRUE_EVENT.Consultar a tabela:
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 | +----------+-------+------------+------------------+
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 | +----------+-------+--------------+------------+
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) ;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 | +----------------+-------------+---------+-----------------+------------------+----------+
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_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 |
+-----+----+-----------------------------------------------------------------------------------------------------+