十亿级events表：统计购前看过商品曝光的客户数最优方案咨询

任务与表结构 #

需求：统计在购买某商品前看过该商品曝光（impression）的客户数量

events表结构：

原方案错误修正与优劣对比 #

方案1（EXISTS版）错误修正 #

原方案存在语法冗余（EXISTS子查询无需返回字段）、逻辑错误（时间戳顺序搞反），修正后SQL：

WITH cteProdsClients AS (
    SELECT e1.product_id, e1.client_id
    FROM events AS e1
    WHERE e1.event_type = 3  -- 筛选购买事件
      AND EXISTS (
          SELECT 1  -- EXISTS只需判断存在性，无需返回字段
          FROM events AS e2
          WHERE e2.event_type = 1  -- 筛选曝光事件
            AND e1.product_id = e2.product_id
            AND e1.client_id = e2.client_id
            AND e2.event_ts < e1.event_ts  -- 曝光时间早于购买时间
      )
)
SELECT COUNT(DISTINCT client_id) AS qualified_client_count
FROM cteProdsClients;

方案2（JOIN版）错误修正 #

原方案存在语法错误（子查询无法直接引用外部表字段）、逻辑错误（时间戳顺序反+未过滤无匹配的购买事件），修正后SQL：

WITH cteProdsClients AS (
    SELECT e1.product_id, e1.client_id
    FROM events AS e1
    INNER JOIN (
        SELECT product_id, client_id, event_ts
        FROM events
        WHERE event_type = 1
    ) AS e2
        ON e1.product_id = e2.product_id
        AND e1.client_id = e2.client_id
        AND e2.event_ts < e1.event_ts
    WHERE e1.event_type = 3
)
SELECT COUNT(DISTINCT client_id) AS qualified_client_count
FROM cteProdsClients;

方案优劣对比 #

• 方案1（EXISTS）更优：EXISTS是半连接逻辑，找到第一个匹配的曝光记录就停止扫描，不会返回所有匹配行，大幅减少数据处理量；而JOIN会返回所有购买-曝光匹配对，后续还需额外去重，性能远低于EXISTS方案。
• 方案2（JOIN）劣势：当同一客户多次购买同一商品且多次曝光时，会生成大量重复记录，增加内存和CPU开销，仅在需要获取所有匹配明细时适用。

十亿级数据表的高效优化方案 #

针对100亿条记录的超大规模表，需从索引、分区、计算逻辑、引擎多维度优化：

1. 索引优化 #

创建覆盖查询的复合索引，避免回表扫描：

-- 覆盖曝光和购买事件的查询字段
CREATE INDEX idx_event_type_product_client_ts ON events(event_type, product_id, client_id, event_ts);

该索引可以直接满足WHERE过滤、JOIN关联、时间戳比较的所有字段需求，无需访问主表数据。

2. 数据分区 #

按event_ts做时间分区（按天/月），利用分区裁剪只扫描有购买和曝光记录的分区，避免全表扫描。如果业务允许，可进一步添加event_type作为二级分区，缩小扫描范围。

3. 逻辑优化：提前去重 #

先对曝光事件按product_id+client_id去重，减少后续关联的数据量：

WITH unique_impressions AS (
    -- 保留每个客户对每个商品的首次曝光时间（或仅标记存在曝光）
    SELECT product_id, client_id, MIN(event_ts) AS first_impression_ts
    FROM events
    WHERE event_type = 1
    GROUP BY product_id, client_id
),
qualified_clients AS (
    SELECT DISTINCT e1.client_id
    FROM events AS e1
    JOIN unique_impressions AS ui
        ON e1.product_id = ui.product_id
        AND e1.client_id = ui.client_id
        AND ui.first_impression_ts < e1.event_ts
    WHERE e1.event_type = 3
)
SELECT COUNT(DISTINCT client_id) AS qualified_client_count
FROM qualified_clients;

4. 计算引擎选择 #

放弃单机关系型数据库，改用Spark SQL、Presto、Hive等分布式计算引擎，利用集群资源并行处理大规模数据，避免单机内存/CPU瓶颈。

5. 预计算与物化视图 #

如果该统计是定期执行的需求，创建物化视图定期刷新预计算结果，比如每天凌晨计算累计合格客户数，避免每次查询都全表扫描。

6. 时间范围过滤 #

如果业务不需要统计全量历史数据，添加时间范围条件（如event_ts >= '2023-01-01'），大幅减少扫描的数据量。

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