- カテゴリ：
ウィンドウ関数 （一般）
CONDITIONAL_TRUE_EVENT¶
ブール値引数 expr1 の結果に基づいて、ウィンドウパーティション内の各行のウィンドウイベント番号を返します。番号は0から始まり、 expr1 がtrueと評価される行ごとに1ずつ増加します。
この関数の用途の1つは、ウィンドウパーティションをセッション化することです。例えば、クリックストリームデータでは、最後のイベントがしきい値よりも前であったかどうかを確認することにより、ユーザーが新しいセッションを開始したかどうかを判断するために関数を使用できます。
構文¶
CONDITIONAL_TRUE_EVENT( <expr1> ) OVER ( [ PARTITION BY <expr2> ] ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )
引数¶
expr1これは、trueと評価されたときにウィンドウイベント番号の値を変更するブール式です。
expr2これは、パーティションを分割するオプションの式です。
expr3これは、各パーティション内で並べ替える式です。
使用上の注意¶
条件式
expr1にはランク関連関数 LAG および LEADを含めることができます。これにより、より表現性に優れたウィンドウを構築できます。使用する場合、これらの関数は CONDITIONAL_TRUE_EVENTと同じ OVER 仕様を使用する必要があります。
例¶
最初の例は次を示しています。
パーティション内の数値は、指定された列が TRUE （この場合はゼロ以外）になるたびに増加します。
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 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_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 |
+-----+----+-----------------------------------------------------------------------------------------------------+