CONDITIONAL_CHANGE_EVENT
CONDITIONAL_CHANGE_EVENT
CONDITIONAL_CHANGE_EVENT는 윈도우 파티션 안에서 각 행에 대해 윈도우 이벤트 번호를 반환하는 윈도우 함수예요. 현재 행의 expr1 인자 값이 이전 행의 expr1 값과 다를 때 이벤트 번호가 증가해요. 이벤트 번호는 0에서 시작하며, 지금까지 그 윈도우 안에서 발생한 변경 횟수를 나타내도록 1씩 증가해요.
본문
카테고리: 윈도우 함수 (General)
구문 (Syntax)
CONDITIONAL_CHANGE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )
인자 (Arguments)
<expr1> — 이전 행의 표현식과 비교되는 표현식이에요.
<expr2> — 파티션을 나누는 선택적 표현식이에요.
<expr3> — 각 파티션 안에서 정렬하는 표현식이에요.
사용 시 유의사항 (Usage notes)
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를 참고해요.
예시 (Examples)
이 예시는 전원이 꺼졌다 다시 켜진 횟수(즉 전압이 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)
;
전압이 0이었던 샘플을 보여줘요. 그 0볼트 이벤트들이 같은 정전에 속했는지 다른 정전에 속했는지는 구분하지 않아요.
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 |
+-----+----+--------------------------------------------------------------+