SQL中通过子查询/Join筛选行：统计仅使用指定产品的用户数（可指定月份）

现有一张包含user、product、month字段的USERS表，建表语句及测试数据如下：

CREATE TABLE USERS (user VARCHAR(1),product VARCHAR(1),month INT);
INSERT INTO USERS (user,product,month) VALUES 
("A","X",9),("A","X",10),("A","X",10),("A","Y",11),
("B","Y",12),("B","X",10),("C","X",10),("D","Z",11),("D","X",9);

需求是通过SQL子查询或Join关联的方式，统计仅使用某一指定产品的用户数量，以及限定统计月份的实现方式。

我们以统计仅使用产品X的用户数为例给出示例代码，替换代码中对应的产品值、月份范围即可适配其他统计需求。

1. 不限定统计月份 #

子查询实现 #

思路：先查询出所有使用过非X产品的用户，外层过滤掉这些用户后去重计数即可。

SELECT COUNT(DISTINCT user) AS only_x_user_count
FROM USERS
WHERE user NOT IN (
    SELECT DISTINCT user FROM USERS WHERE product != 'X'
);

运行结果为1，符合测试数据中仅用户C从未使用过X以外产品的情况。

Join关联实现 #

思路：左连接同表关联非X产品的同用户记录，若关联结果为空则说明该用户仅使用过X。

SELECT COUNT(DISTINCT u1.user) AS only_x_user_count
FROM USERS u1
LEFT JOIN USERS u2
ON u1.user = u2.user AND u2.product != 'X'
WHERE u1.product = 'X' AND u2.user IS NULL;

运行结果和子查询方式一致。

2. 限定统计月份 #

若只统计指定月份范围内的用户行为，只需在子查询/Join的条件中都加上月份过滤规则即可，我们以限定统计9、10两个月的结果为例：

子查询实现 #

SELECT COUNT(DISTINCT user) AS only_x_user_count
FROM USERS
WHERE month IN (9,10)
AND user NOT IN (
    SELECT DISTINCT user FROM USERS 
    WHERE month IN (9,10) 
    AND product != 'X'
);

运行结果为4，对应测试数据中9-10月范围内A、B、C、D都仅使用了X，范围外的使用记录不纳入统计。

Join关联实现 #

SELECT COUNT(DISTINCT u1.user) AS only_x_user_count
FROM USERS u1
LEFT JOIN USERS u2
ON u1.user = u2.user 
AND u2.month IN (9,10)
AND u2.product != 'X'
WHERE u1.month IN (9,10)
AND u1.product = 'X' 
AND u2.user IS NULL;

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