如何用窗口函数实现HAS_BEEN_TRUE逻辑（优先Redshift）

基础表结构与数据 #

CREATE TABLE test (
  id INT
  , is_true BOOL
);
INSERT INTO test (id,is_true) VALUES (1,FALSE);
INSERT INTO test (id,is_true) VALUES (2,FALSE);
INSERT INTO test (id,is_true) VALUES (3,TRUE);
INSERT INTO test (id,is_true) VALUES (4,FALSE); 

查询原数据语句：

SELECT 
    is_true
FROM test; 

原查询结果：

FALSE
FALSE
TRUE
FALSE

需求说明 #

需按id排序实现两种窗口函数逻辑：

1. HAS_BEEN_TRUE：判断当前行之前是否出现过TRUE，预期结果：

FALSE -- 从未出现过TRUE
FALSE -- 从未出现过TRUE
FALSE -- 当前行是TRUE，但之前从未出现过
TRUE  -- 之前已经出现过TRUE

2. HAS_BEEN_OR_IS_TRUE：判断当前行及之前是否出现过TRUE，预期结果：

FALSE
FALSE
TRUE
TRUE 

Redshift 实现方案 #

1. 实现 HAS_BEEN_TRUE #

通过窗口SUM()结合CASE统计当前行之前的TRUE数量，判断是否大于0：

SELECT
    id,
    is_true,
    CASE 
        WHEN SUM(CASE WHEN is_true THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > 0 
        THEN TRUE 
        ELSE FALSE 
    END AS has_been_true
FROM test
ORDER BY id;

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定窗口为当前行之前的所有行，统计其中TRUE的数量即可判断是否出现过。

2. 实现 HAS_BEEN_OR_IS_TRUE #

用MAX()窗口函数更简洁高效，直接取窗口内的最大值（只要有TRUE就返回TRUE）：

SELECT
    id,
    is_true,
    MAX(is_true) OVER (ORDER BY id) AS has_been_or_is_true
FROM test
ORDER BY id;

也可以用SUM()统计当前行及之前的TRUE数量，逻辑与MAX()一致：

SELECT
    id,
    is_true,
    CASE 
        WHEN SUM(CASE WHEN is_true THEN 1 ELSE 0 END) OVER (ORDER BY id) > 0 
        THEN TRUE 
        ELSE FALSE 
    END AS has_been_or_is_true
FROM test
ORDER BY id;

其他数据库通用实现 #

大部分支持窗口函数的数据库（PostgreSQL、MySQL 8+、SQL Server等）可复用以下逻辑：

HAS_BEEN_TRUE #

除了窗口函数方式，也可用关联子查询实现：

-- 窗口函数方式（与Redshift一致）
SELECT
    id,
    is_true,
    CASE 
        WHEN SUM(CASE WHEN is_true THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > 0 
        THEN TRUE 
        ELSE FALSE 
    END AS has_been_true
FROM test
ORDER BY id;

-- 关联子查询方式
SELECT
    id,
    is_true,
    EXISTS (
        SELECT 1 
        FROM test t2 
        WHERE t2.id < t1.id AND t2.is_true = TRUE
    ) AS has_been_true
FROM test t1
ORDER BY id;

HAS_BEEN_OR_IS_TRUE #

通用MAX()窗口函数实现：

SELECT
    id,
    is_true,
    MAX(is_true) OVER (ORDER BY id) AS has_been_or_is_true
FROM test
ORDER BY id;

内容的提问来源于stack exchange，提问作者BigBoy1337