Redshift中按条件求和列值及合并会话保留时间的技术咨询

1. 如何在Redshift中按条件对列值进行求和？ #

在Redshift里，实现条件求和最常用的方式是结合SUM()聚合函数和CASE表达式，分两种常见场景：

场景1：全局条件求和 #

如果你想对整张表中满足特定条件的列值求和，比如只计算金额大于100的记录总和：

SELECT SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS total_high_value
FROM your_table_name;

这里CASE会把不满足条件的行值替换为0，SUM()就只会累加符合条件的数值。如果目标列有NULL值也不用担心，SUM()会自动忽略NULL。

场景2：分组后按条件求和 #

如果需要按某个维度（比如客户ID）分组，再对每组内满足条件的列值求和，比如统计每个客户金额大于100的交易总和：

SELECT 
    customerid,
    SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS customer_high_value_total
FROM your_table_name
GROUP BY customerid;

2. 生成会话表（按mindiff分组聚合） #

根据你的需求，我会用窗口函数来给同一会话的行打上标记，再聚合得到最终结果，具体步骤如下：

实现思路 #

1. 按客户ID和登录时间排序，用LAG()函数获取上一行的mindiff，判断当前行是否属于新会话（当mindiff >=20时视为会话分界）；
2. 累加新会话标记生成唯一的会话ID；
3. 按客户ID和会话ID聚合，提取会话的最早登录时间、最晚登出时间，同时求和mindiff得到会话时长。

示例SQL #

WITH session_grouping AS (
    SELECT 
        customerid,
        login_timestamp,
        logout_timestamp,
        mindiff,
        -- 标记新会话：客户的第一条记录 或 上一行mindiff≥20
        CASE 
            WHEN LAG(mindiff) OVER (PARTITION BY customerid ORDER BY login_timestamp) >= 20 
                 OR LAG(mindiff) OVER (PARTITION BY customerid ORDER BY login_timestamp) IS NULL
            THEN 1 
            ELSE 0 
        END AS is_new_session,
        -- 生成会话ID：累加新会话标记，同一会话ID相同
        SUM(CASE 
                WHEN LAG(mindiff) OVER (PARTITION BY customerid ORDER BY login_timestamp) >=20 
                     OR LAG(mindiff) OVER (PARTITION BY customerid ORDER BY login_timestamp) IS NULL
                THEN 1 
                ELSE 0 
             END) OVER (PARTITION BY customerid ORDER BY login_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM your_existing_table
)
SELECT 
    customerid,
    MIN(login_timestamp) AS session_login_time,
    MAX(logout_timestamp) AS session_logout_time,
    SUM(mindiff) AS session_duration
FROM session_grouping
GROUP BY customerid, session_id
ORDER BY customerid, session_login_time;

说明 #

• session_grouping CTE里的LAG()函数用来获取当前客户上一条记录的mindiff，以此判断是否开启新会话；
• 会话ID通过累加新会话标记生成，确保同一会话的所有行拥有相同ID；
• 最后聚合时，用MIN()取会话的最早登录时间，MAX()取最晚登出时间，SUM()累加mindiff得到会话总时长。

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