PostgreSQL单查询实现多条件电商用户日聚合统计需求

数据集定义 #

建表及插入数据的SQL如下：

create table sample_events(
event_date date,
"session" varchar,
"user" varchar,
page_type varchar,
event_type varchar,
product int8);

INSERT INTO sample_events (event_date,"session","user",page_type,event_type,product) values
('2022-10-01','session1user1','user1','product_page','page_view',0),
('2022-10-01','session1user2','user2','listing_page','page_view',0),
('2022-10-01','session1user2','user2','search_listing_page','page_view',0),
('2022-10-01','session1user3','user3','product_page','page_view',0),
('2022-10-01','session2user1','user1','product_page','add_to_cart',20969597),
('2022-10-02','session2user1','user1','order_page','order',0),
('2022-10-02','session2user3','user3','product_page','add_to_cart', 34856927),
('2022-10-02','session3user3','user3','product_page','add_to_cart', 19848603),
('2022-10-04','session4user3','user3','order_page','order',0);

统计需求 #

需按日统计满足以下全部条件的用户数量：

• 首次会话仅包含商品页（product_page）的浏览（page_view）事件
• 用户仅添加过一件商品到购物车
• 在首次会话后的两天内完成下单
  要求使用单查询实现，避免表关联以提升效率。

单查询实现代码 #

SELECT
  MIN(event_date) AS stat_date,
  COUNT(DISTINCT "user") AS qualified_user_count
FROM sample_events
GROUP BY "user"
HAVING
  -- 验证首次会话的所有事件均为商品页浏览
  BOOL_AND(
    CASE
      WHEN event_date = MIN(event_date) THEN page_type = 'product_page' AND event_type = 'page_view'
      ELSE TRUE
    END
  )
  -- 验证仅添加过一件商品到购物车
  AND SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) = 1
  -- 验证存在下单事件且下单时间在首次会话后两天内
  AND MAX(CASE WHEN event_type = 'order' THEN event_date ELSE NULL END) <= MIN(event_date) + INTERVAL '2 days'
  AND MAX(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) = 1
GROUP BY stat_date;

代码说明 #

1. 按用户分组聚合：先以user为分组维度，计算每个用户的核心行为指标
2. 条件1验证：通过BOOL_AND结合CASE语句，只检查首次会话日期的所有事件是否都是商品页浏览，非首次会话的事件不干扰判断
3. 条件2验证：用SUM统计用户的add_to_cart事件总数，确保等于1
4. 条件3验证：
   • 用MAX提取用户的下单日期，确认其不晚于首次会话日期加2天
   • 额外验证用户确实存在下单事件，避免无下单记录的用户被误统计
5. 最终按日聚合：最后按首次会话日期（stat_date）分组，统计该日期下符合条件的用户数量

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