查询各门店开业首30天内的独立购物者数量SQL实现

现有数据表 #

shopper表 #

store表 #

需求 #

获取各门店开业首30天内到访的独立购物者总数。

现有SQL代码（存在问题） #

SELECT
    store_id,
    opening_date,
    COUNT( shopper_id ) OVER ( PARTITION BY store_id ORDER BY opening_date )

FROM
    store AS s
    INNER JOIN shoppers AS sp ON s.store_id = sp.store_id

WHERE
    opening_date <= opening_date + 30

代码问题分析 #

1. WHERE条件无效：opening_date <= opening_date + 30 永远成立，根本没筛选出开业30天内的到访记录，应该判断购物者的到访日期sp.date是否在门店开业日期s.opening_date到s.opening_date + 30天区间内。
2. 统计逻辑错误：需求是统计独立购物者总数，用窗口函数会返回每一行的累计值，而非最终总数，应该用COUNT(DISTINCT shopper_id)配合分组实现。
3. 表名不匹配：代码里写的是shoppers，但给定的表名是shopper，需要统一。

修正后的SQL代码 #

SELECT
    s.store_id,
    s.opening_date,
    COUNT(DISTINCT sp.shopper_id) AS unique_shopper_count
FROM
    store AS s
INNER JOIN shopper AS sp 
    ON s.store_id = sp.store_id
WHERE
    sp.date BETWEEN s.opening_date AND s.opening_date + INTERVAL '30 days'
GROUP BY
    s.store_id, s.opening_date;

代码说明 #

• 用BETWEEN精准筛选出购物者到访日期在门店开业首30天内的记录
• 借助COUNT(DISTINCT shopper_id)统计每个门店的独立购物者数量
• 按store_id和opening_date分组，保证每个门店仅返回一行统计结果

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