- 카테고리:
윈도우 함수 (일반)
CONDITIONAL_CHANGE_EVENT¶
현재 행의 expr1 인자 값이 이전 행의 expr1 값과 다른 경우, 윈도우 파티션 내의 각 행에 대한 윈도우 이벤트 번호를 반환합니다. 윈도우 이벤트 번호는 0에서 시작하고 해당 윈도우 내에서 지금까지의 변경 사항 수를 나타내기 위해 1씩 증가합니다.
구문¶
CONDITIONAL_CHANGE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )
인자¶
expr1이전 행의 식과 비교되는 식입니다.
expr2분할하는 선택적 식입니다.
expr3각 파티션 내에서 순서를 지정하는 식입니다.
사용법 노트¶
CONDITIONAL_CHANGE_EVENT (expr1) OVER (window_frame)식은 다음과 같이 계산됩니다.CONDITIONAL_TRUE_EVENT( <식1> != LAG(<식1>) OVER(window_frame)) OVER(window_frame)CONDITIONAL_TRUE_EVENT에 대한 자세한 내용은 CONDITIONAL_TRUE_EVENT 를 참조하십시오.
예¶
이는 정전되었다가 다시 돌아온 횟수(즉, 전압이 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 |
+-----+----+--------------------------------------------------------------+