SQL如何查询新增客户数最高、最低的年月及对应客户量

你之前尝试的查询存在两个问题：一是分组粒度为完整的create_date字段，不是按年月聚合；二是嵌套使用MAX(count(*))的写法在大多数SQL方言中不被支持，且无法匹配对应的年月维度信息。

解决方案1：排序+Limit（简单场景适用） #

如果仅需要取单条最高/最低值（不考虑并列情况），可以直接对聚合后的结果按客户数排序后取第一条：

仅查询最高值对应年月

select count(name) AS CUSTOMER, 
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
from x
group by extract(year from create_date), 
         extract(month from create_date)
order by CUSTOMER desc
limit 1;

同时查询最高值和最低值对应年月

-- 取最高值记录
(select count(name) AS CUSTOMER, 
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon,
       '最高值' as data_type
from x
group by extract(year from create_date), 
         extract(month from create_date)
order by CUSTOMER desc
limit 1)
union all
-- 取最低值记录
(select count(name) AS CUSTOMER, 
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon,
       '最低值' as data_type
from x
group by extract(year from create_date), 
         extract(month from create_date)
order by CUSTOMER asc
limit 1);

解决方案2：窗口函数（兼容并列场景） #

如果存在多个年月的新增客户数并列最高/最低的情况，推荐使用窗口函数实现，可以返回所有符合条件的记录：

with monthly_customer as (
    -- 先统计每个年月的新增客户数
    select count(name) AS CUSTOMER, 
           extract(year from create_date) as yr, 
           extract(month from create_date) as mon
    from x
    group by extract(year from create_date), 
             extract(month from create_date)
),
rank_customer as (
    -- 分别按客户数正序、倒序排名
    select CUSTOMER, yr, mon,
           rank() over (order by CUSTOMER desc) as rk_desc,
           rank() over (order by CUSTOMER asc) as rk_asc
    from monthly_customer
)
-- 筛选排名第一的最高、最低值记录
select CUSTOMER, yr, mon,
       case when rk_desc = 1 then '最高值' else '最低值' end as data_type
from rank_customer
where rk_desc = 1 or rk_asc = 1;

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