- 카테고리:
윈도우 함수 (일반)
CONDITIONAL_TRUE_EVENT¶
부울 인자 expr1 의 결과를 기반으로 윈도우 파티션 내의 각 행에 대한 윈도우 이벤트 번호를 반환합니다. 숫자는 0부터 시작하며 expr1 이 true로 평가되는 각 행에 대해 1씩 증가합니다.
이 함수의 한 가지 용도는 윈도우 파티션을 세션화하는 것입니다. 예를 들어, 클릭 스트림 데이터에서 이는 마지막 이벤트가 임계값보다 더 오래되었는지 여부를 확인함으로써 사용자가 새 세션을 시작했는지 여부를 판별하는 데 사용할 수 있습니다.
구문¶
CONDITIONAL_TRUE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> )
인자¶
expr1true로 평가될 때 윈도우 이벤트 번호 값을 변경하는 부울 식입니다.
expr2분할하는 선택적 식입니다.
expr3각 파티션 내에서 순서를 지정하는 식입니다.
사용법 노트¶
조건식
expr1은 순위 관련 함수 LAG 및 LEAD를 포함할 수 있으므로, 보다 표현적인 윈도우를 빌드할 수 있습니다. 사용되는 경우, 이러한 함수는 CONDITIONAL_TRUE_EVENT와 동일한 OVER 사양을 사용해야 합니다.
예¶
첫 번째 예는 다음을 보여줍니다.
파티션 내의 숫자는 지정된 열이 TRUE(이 경우 0이 아님)일 때마다 증가합니다.
NULL 값은 TRUE 값으로 간주되지 않습니다.
숫자는 각 파티션에 대해 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_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 | +----------+-------+------------+
다음 예는 다음을 보여줍니다.
expr1은 열이 아닌 식일 수 있습니다. 이 쿼리는o_col > 20식을 사용하며, 쿼리의 출력은 o_col의 값이 20보다 작거나 같은 값에서 20보다 큰 값으로 변경될 때를 보여줍니다.expr3은expr1과 일치할 필요가 없습니다. 즉, OVER 절의 ORDER BY 하위 절에 있는 식이 CONDITIONAL_TRUE_EVENT 함수에 있는 식과 일치할 필요가 없습니다.테이블을 쿼리합니다.
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 | +----------+-------+------------+------------------+
다음 예는 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 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 | +----------------+-------------+---------+-----------------+------------------+----------+
다음은 보다 광범위한 예입니다.
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 |
+-----+----+-----------------------------------------------------------------------------------------------------+