카테고리:

윈도우 함수 (일반)

CONDITIONAL_TRUE_EVENT

부울 인자 expr1 의 결과를 기반으로 윈도우 파티션 내의 각 행에 대한 윈도우 이벤트 번호를 반환합니다. 숫자는 0부터 시작하며 expr1 이 true로 평가되는 각 행에 대해 1씩 증가합니다.

이 함수의 한 가지 용도는 윈도우 파티션을 세션화하는 것입니다. 예를 들어, 클릭 스트림 데이터에서 이는 마지막 이벤트가 임계값보다 더 오래되었는지 여부를 확인함으로써 사용자가 새 세션을 시작했는지 여부를 판별하는 데 사용할 수 있습니다.

구문

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

인자

expr1

true로 평가될 때 윈도우 이벤트 번호 값을 변경하는 부울 식입니다.

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보다 큰 값으로 변경될 때를 보여줍니다.

  • expr3expr1 과 일치할 필요가 없습니다. 즉, 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 |
+-----+----+-----------------------------------------------------------------------------------------------------+
맨 위로 이동