请求编写SQL：按cat1、cat2统计1年内同店同品购买超2次的用户数

原始数据 #

date           user prod shop cat1 cat2
2022-02-01     1    a    a    ah   g
2022-02-02     1    a1   b    ah   g
2022-04-03     1    a    a    ah   g
2022-04-19     1    a    a    ah   g
2022-05-01     2    b    c    bg   g

需求描述 #

统计1年内，同一用户在同一店铺购买同一商品次数超过2次的用户数，并分别按cat1和cat2维度分组统计最终的用户数量。

期望输出 #

Table 1（按cat1维度） #

cat1   number_of_user
ah     1

Table 2（按cat2维度） #

cat2   number_of_user
g      1

已实现的总用户数统计SQL #

WITH data_product AS(
SELECT DATE(payment_time) date,
user,
CONCAT(prod, "_", shop) product_shop,
cat1,
cat2
FROM
a
WHERE
DATE(payment_time) BETWEEN "2022-01-01" AND DATE_SUB(current_date, INTERVAL 1 day)
ORDER BY 1,2,3),
purchased AS (
SELECT user, product_shop, count(product_shop) tot_purchased
FROM data_product
GROUP BY 1,2
HAVING COUNT(product_shop) > 2
)
SELECT COUNT(user) number_of_user FROM purchased

按分类维度的统计SQL #

复用已有CTE逻辑，在最终统计时加入分类维度，同时对用户去重（避免同一用户在多个商品-店铺组合满足条件时被重复统计）：

按cat1维度统计 #

WITH data_product AS(
SELECT DATE(payment_time) date,
user,
CONCAT(prod, "_", shop) product_shop,
cat1,
cat2
FROM
a
WHERE
DATE(payment_time) BETWEEN "2022-01-01" AND DATE_SUB(current_date, INTERVAL 1 day)
),
purchased AS (
SELECT user, product_shop, cat1, cat2
FROM data_product
GROUP BY user, product_shop, cat1, cat2
HAVING COUNT(product_shop) > 2
)
SELECT cat1, COUNT(DISTINCT user) AS number_of_user
FROM purchased
GROUP BY cat1;

按cat2维度统计 #

WITH data_product AS(
SELECT DATE(payment_time) date,
user,
CONCAT(prod, "_", shop) product_shop,
cat1,
cat2
FROM
a
WHERE
DATE(payment_time) BETWEEN "2022-01-01" AND DATE_SUB(current_date, INTERVAL 1 day)
),
purchased AS (
SELECT user, product_shop, cat1, cat2
FROM data_product
GROUP BY user, product_shop, cat1, cat2
HAVING COUNT(product_shop) > 2
)
SELECT cat2, COUNT(DISTINCT user) AS number_of_user
FROM purchased
GROUP BY cat2;

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