按月份统计新增用户数的SQL代码正确性咨询（附自写代码）

嘿，让我帮你看看这段SQL和你的需求匹配度如何～

首先拆解下你写的SQL逻辑：

• 子查询SELECT Customer_Id, DATE(MIN(Order_Date)) AS order_date FROM Orders GROUP BY Customer_Id：这部分是对的！它能准确找出每个用户的首次下单日期——也就是这个用户成为新增用户的关键日期。
• 外层查询SELECT order_date, COUNT (Customer_Id) As new_usercount FROM ... GROUP BY order_date：这里就和你的需求有偏差啦，它是按具体某一天统计新增用户数，但你要的是按月份统计，所以得调整下日期的维度。

要实现按月份统计，核心是把用户的首次下单日期转换成「年月」格式，再按这个维度分组统计。不同数据库的日期格式化函数不一样，我给你几个常见数据库的版本：

MySQL 版本 #

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS order_month,
    COUNT(Customer_Id) AS new_usercount
FROM (
    SELECT 
        Customer_Id,
        MIN(Order_Date) AS order_date
    FROM Orders
    GROUP BY Customer_Id
) AS ch
GROUP BY order_month
ORDER BY order_month;

PostgreSQL 版本 #

SELECT 
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    COUNT(Customer_Id) AS new_usercount
FROM (
    SELECT 
        Customer_Id,
        MIN(Order_Date) AS order_date
    FROM Orders
    GROUP BY Customer_Id
) AS ch
GROUP BY order_month
ORDER BY order_month;

SQL Server 版本 #

SELECT 
    FORMAT(order_date, 'yyyy-MM') AS order_month,
    COUNT(Customer_Id) AS new_usercount
FROM (
    SELECT 
        Customer_Id,
        MIN(Order_Date) AS order_date
    FROM Orders
    GROUP BY Customer_Id
) AS ch
GROUP BY FORMAT(order_date, 'yyyy-MM')
ORDER BY order_month;

我特意加上了ORDER BY order_month，这样统计结果会按月份顺序排列，看起来更清晰直观～

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