如何按product_id、年份、月份分组查询每日最高销售额

我有一张存储销售数据的表sales，结构及数据如下：

我需要编写SQL查询，返回每个（product_id、年份、月份）组合对应的单日最高销售总额。目前我已经能查询出每个产品每日的销售总额：

select product_id, date(orderdate) date, sum(amount) daily_total
from sales
group by 1, 2 

但不知道如何进一步获取每个（product_id、年份、月份）维度下的单日最大值，预期输出如下：

方法一：子查询+分组聚合（兼容性强） #

先通过子计算出每个产品的每日销售总额，再基于产品、年份、月份分组，取每组内的单日总额最大值：

select 
    product_id,
    extract(year from date(orderdate)) as year,
    extract(month from date(orderdate)) as month,
    max(daily_total) as amount
from (
    select 
        product_id,
        date(orderdate) as order_date,
        sum(amount) as daily_total
    from sales
    group by product_id, order_date
) as daily_sales
group by product_id, year, month
order by product_id, year, month;

方法二：窗口函数（灵活拓展） #

适用于支持窗口函数的SQL方言（如PostgreSQL、MySQL 8+、SQL Server等），如果需要同时查看最高总额对应的具体日期，这种方法更易调整：

with daily_sales as (
    select 
        product_id,
        date(orderdate) as order_date,
        sum(amount) as daily_total,
        extract(year from date(orderdate)) as year,
        extract(month from date(orderdate)) as month
    from sales
    group by product_id, order_date
)
select distinct
    product_id,
    year,
    month,
    max(daily_total) over (partition by product_id, year, month) as amount
from daily_sales
order by product_id, year, month;

注意事项 #

不同SQL方言的日期提取语法略有差异：

• MySQL可替换为year(date(orderdate))和month(date(orderdate))
• SQL Server可替换为DATEPART(year, date(orderdate))和DATEPART(month, date(orderdate))

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