在Hive SQL中统计单日各时间点前的累计交易数

示例表结构与数据 #

CREATE TABLE table1 (ID INT, time TIME);

INSERT INTO table1 
VALUES
(1, '11:30:00'),
(1, '14:30:00'),
(1, '18:00:00')
;

CREATE TABLE table2 (ID INT, txn_time TIME, txn_val INT);

INSERT INTO table2
VALUES
(1, '10:45:13', 1),
(1, '10:50:52', 2),
(1, '11:01:20', 4),
(1, '14:32:12', 2),
(1, '16:43:20', 5),
(1, '19:22:02', 3)
;

期望结果 #

┌─────────────┬──────────────┬──────────────┐
│ ID          │        time  │    txn count │
├─────────────┼──────────────┼──────────────┤
│ 1           │     11:30:00 │            3 │
│ 1           │     14:30:00 │            3 │
│ 1           │     18:00:00 │            5 │
└─────────────┴──────────────┴──────────────┘

错误的SQL代码 #

SELECT t1.ID,
t1.time,
sum(CASE WHEN t2.txn_time < t1.time THEN 1 END) over(PARTITION BY t1.time)
FROM table1 AS t1
LEFT JOIN table2 AS t2 on t1.ID = t2.ID
GROUP BY t1.ID, t1.time
ORDER BY t1.time

解决方案 #

1. 用PARTITION BY实现正确累计计数 #

原SQL的问题在于多对多关联后产生笛卡尔积，窗口函数的PARTITION BY t1.time无法正确计算累计数。正确做法是先对table2的交易按ID分组，用窗口函数计算累计计数，再关联table1匹配对应时间点的最大值：

WITH txn_cumulative AS (
    SELECT 
        ID,
        txn_time,
        -- 按ID分组，按交易时间排序，计算累计交易数
        COUNT(*) OVER (PARTITION BY ID ORDER BY txn_time) AS cum_count
    FROM table2
)
SELECT 
    t1.ID,
    t1.time,
    -- 取所有早于当前时间点的累计数最大值，即为该时间点的累计交易数
    COALESCE(MAX(tc.cum_count), 0) AS txn_count
FROM table1 t1
LEFT JOIN txn_cumulative tc 
    ON t1.ID = tc.ID 
    AND tc.txn_time < t1.time
GROUP BY t1.ID, t1.time
ORDER BY t1.time;

2. 支持多日场景，每日计数重置 #

首先需给两张表添加日期字段（如record_date和txn_date），然后在窗口函数的PARTITION BY中加入日期，确保每日计数独立重置：

-- 先更新表结构支持多日
CREATE TABLE table1 (ID INT, record_date DATE, record_time TIME);
CREATE TABLE table2 (ID INT, txn_date DATE, txn_time TIME, txn_val INT);

-- 多日版统计SQL
WITH txn_cumulative AS (
    SELECT 
        ID,
        txn_date,
        txn_time,
        -- 按ID+日期分组，每日累计计数从0开始
        COUNT(*) OVER (PARTITION BY ID, txn_date ORDER BY txn_time) AS cum_count
    FROM table2
)
SELECT 
    t1.ID,
    t1.record_date,
    t1.record_time,
    COALESCE(MAX(tc.cum_count), 0) AS txn_count
FROM table1 t1
LEFT JOIN txn_cumulative tc 
    ON t1.ID = tc.ID 
    AND t1.record_date = tc.txn_date
    AND tc.txn_time < t1.record_time
GROUP BY t1.ID, t1.record_date, t1.record_time
ORDER BY t1.record_date, t1.record_time;

3. 优化多对多关联性能 #

原SQL的多对多关联会产生大量中间数据，可通过以下方式优化：

• 添加复合索引：给table2创建(ID, txn_date, txn_time)复合索引，给table1创建(ID, record_date, record_time)复合索引，加速过滤、排序和关联
• 避免笛卡尔积：使用LATERAL JOIN（PostgreSQL）或OUTER APPLY（SQL Server）替代预计算+GROUP BY，直接针对每个时间点计算符合条件的交易数，减少中间结果集：

以PostgreSQL为例：

SELECT 
    t1.ID,
    t1.record_date,
    t1.record_time,
    COALESCE(tc.cum_count, 0) AS txn_count
FROM table1 t1
LEFT JOIN LATERAL (
    -- 直接统计当前ID、日期下，早于当前时间点的交易数
    SELECT COUNT(*) AS cum_count
    FROM table2 t2
    WHERE t2.ID = t1.ID
      AND t2.txn_date = t1.record_date
      AND t2.txn_time < t1.record_time
) tc ON true
ORDER BY t1.record_date, t1.record_time;

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