- 类别：
:doc:`/sql-reference/functions-window`（通用）
CONDITIONAL_TRUE_EVENT¶
根据布尔实参 expr1 的结果，返回窗口分区内每行的窗口事件编号。该数字从 0 开始，对于 expr1 计算结果为 true 的每一行，该数字加 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 |
+-----+----+-----------------------------------------------------------------------------------------------------+