统计不同时段在指定区域门店均消费的用户数SQL实现方法

你写的SQL无法运行、结果错误，核心问题有4个：

• 字段名错误：表中存储用户ID的字段是user_id，原SQL写了不存在的user字段
• 语法错误：多层子查询的括号没有闭合，外层子查询缺少正确的FROM结构和别名，直接违反SQL语法规则
• 关联逻辑缺失：没有关联消费日期purchases.purchase_date和门店位置记录日期stores.date，会导致匹配到错误日期的门店坐标
• 业务逻辑错误：把两个区域、两个时间段的门店筛选条件用AND拼接在同一个查询里，本质是要求同一个门店同时属于5月美国区域、6月日本区域，和你要找「用户先后在两个场景消费」的需求完全不符

实现思路是先分别筛选出两个场景下的消费用户集合，再取两个集合的交集，最后统计去重后的用户总数。

写法1：用INTERSECT取交集（逻辑最直观，支持PostgreSQL、SQL Server、新版MySQL等主流数据库） #

SELECT COUNT(DISTINCT user_id) AS user_count
FROM (
    -- 筛选2022年5月在美国区域门店消费的用户
    SELECT DISTINCT p.user_id
    FROM purchases p
    INNER JOIN stores s
        ON p.store_id = s.store_id
        AND p.purchase_date = s.date -- 必须匹配消费当日的门店位置
    WHERE p.purchase_date BETWEEN '2022-05-01' AND '2022-05-31'
      AND s.latitude BETWEEN 23 AND 50
      AND s.longitude BETWEEN -127 AND -66
    INTERSECT
    -- 筛选2022年6月在日本区域门店消费的用户
    SELECT DISTINCT p.user_id
    FROM purchases p
    INNER JOIN stores s
        ON p.store_id = s.store_id
        AND p.purchase_date = s.date
    WHERE p.purchase_date BETWEEN '2022-06-01' AND '2022-06-30'
      AND s.latitude BETWEEN 30 AND 45
      AND s.longitude BETWEEN 130 AND 150
) AS valid_user;

写法2：用INNER JOIN取交集（兼容不支持INTERSECT的老版本数据库） #

SELECT COUNT(DISTINCT a.user_id) AS user_count
FROM (
    -- 2022年5月美国消费用户集合
    SELECT DISTINCT p.user_id
    FROM purchases p
    INNER JOIN stores s
        ON p.store_id = s.store_id
        AND p.purchase_date = s.date
    WHERE p.purchase_date BETWEEN '2022-05-01' AND '2022-05-31'
      AND s.latitude BETWEEN 23 AND 50
      AND s.longitude BETWEEN -127 AND -66
) AS a
INNER JOIN (
    -- 2022年6月日本消费用户集合
    SELECT DISTINCT p.user_id
    FROM purchases p
    INNER JOIN stores s
        ON p.store_id = s.store_id
        AND p.purchase_date = s.date
    WHERE p.purchase_date BETWEEN '2022-06-01' AND '2022-06-30'
      AND s.latitude BETWEEN 30 AND 45
      AND s.longitude BETWEEN 130 AND 150
) AS b
ON a.user_id = b.user_id;

• 经纬度范围、日期区间可以根据实际统计需求替换，不需要改动整体结构
• 不要省略p.purchase_date = s.date的关联条件，否则会把门店其他日期的位置当成消费当日位置，导致统计结果错误
• 如果数据量较大，可以给purchases(purchase_date, store_id, user_id)、stores(date, store_id, latitude, longitude)加联合索引提升查询速度

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