MySQL的group by 语句中能否对count的元素进行筛选在count1的同时也对某个字段count符合条件的数量

本文链接：https://blog.youkuaiyun.com/xiamu_CDA/article/details/142490513

MySQL中的GROUP BY与COUNT高级用法：同时统计多个条件下的记录数量

当你需要从数据库中获取特定分组的信息，并且不仅仅满足于简单的计数，而是希望能够对某些字段的计数添加额外的筛选条件时，事情就开始变得有趣了。例如，在一个电商系统中，我们可能想要知道每个城市的用户总数，但同时也想知道这些城市中有多少用户在过去的一个月内进行了购物。这就涉及到在同一查询中对不同的数据进行聚合统计的需求。本文将深入探讨MySQL中的GROUP BY语句如何与COUNT函数巧妙结合，以满足这种复杂场景下的需求。

1. 基础概念回顾

在开始之前，让我们快速复习一下GROUP BY和COUNT的基本使用方法。

GROUP BY

GROUP BY子句用于将数据表中的行按一个或多个列的值进行逻辑分组，以便可以针对每个组执行聚合函数。例如：

SELECT city, COUNT(*) FROM users GROUP BY city;

这条查询语句会返回每个城市对应的用户数量。

COUNT

COUNT是一个聚合函数，用来计算表中行的数量。当不带参数时，COUNT(*)会计算所有行，包括那些包含NULL值的行；如果带参数，则只计算该列非NULL的行数。

2. 高级COUNT用法

当涉及到需要根据某个条件来筛选COUNT的结果时，我们可以利用HAVING或WHERE子句加上嵌套的CASE表达式来实现更复杂的统计逻辑。

使用HAVING子句

假设我们有一个订单表orders，包含用户的ID(user_id)和订单日期(order_date)，现在想找出每个月有多少个用户下单以及其中有多少是上个月也有下单的重复客户。

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCT CASE WHEN DATE_FORMAT(LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date), '%Y-%m') = DATE_FORMAT(order_date, '%Y-%m') THEN user_id END) AS repeat_users
FROM orders
GROUP BY month;

这里使用了窗口函数LEAD()来查找每个用户的下一条记录（即下一个订单），并通过比较当前月份和下一次订单的月份来确定是否为重复客户。

利用嵌套CASE表达式

另一种方法是通过嵌套CASE表达式直接在COUNT内部应用条件过滤：

SELECT city,
       COUNT(*) AS total_users,
       COUNT(CASE WHEN last_purchase > DATE_SUB(NOW(), INTERVAL 1 MONTH) THEN 1 ELSE NULL END) AS recent_shoppers
FROM users
GROUP BY city;

这段代码统计了每个城市的所有用户数以及过去一个月内有过购物行为的用户数。

3. 实际案例分析

让我们通过一个具体的例子来看看如何将上述技术应用于实际问题中。假设有这样一个需求：统计每种产品类别下的总销售额及其中销售额超过平均值的产品个数。

WITH product_sales AS (
    SELECT category, SUM(price * quantity) AS total_sales
    FROM sales
    GROUP BY product_id
),
avg_sales_per_category AS (
    SELECT category, AVG(total_sales) AS avg_sales
    FROM product_sales
    GROUP BY category
)
SELECT
    ps.category,
    SUM(ps.total_sales) AS total_sales,
    COUNT(CASE WHEN ps.total_sales > a.avg_sales THEN 1 ELSE NULL END) AS above_avg_count
FROM product_sales ps
JOIN avg_sales_per_category a ON ps.category = a.category
GROUP BY ps.category;

在这个解决方案中，我们首先创建了一个名为product_sales的临时表来计算每个产品的销售总额，然后在另一个名为avg_sales_per_category的CTE中计算每个类别的平均销售额。最后，主查询连接这两个表，并使用嵌套CASE表达式来计算每个类别中销售表现高于平均水平的产品数量。