题解 | 统计每个产品的销售情况

with year as (
     select
        p.product_id,
         sum(p.unit_price * o.quantity) as total_sales,
         sum(o.quantity) as total_quantity
    from
        products p
        left join orders o on p.product_id = o.product_id
    where
        year(o.order_date) = '2023'
    group by
        p.product_id
),
month as (
    select
        product_id,
        sum(monthly_sales)/12 as avg_monthly_sales,
        max(monthly_quantity) as max_monthly_quantity
    from (
        select
            p.product_id,
            month(order_date) as month,
            sum(p.unit_price * o.quantity) as monthly_sales,
            sum(o.quantity) as monthly_quantity
        from
            products p
            left join orders o on p.product_id = o.product_id
        where
            year(o.order_date) = '2023'
        group by
            p.product_id,
            month(order_date)
            ) sub
    group by product_id
),
customer1 as (
    select
        product_id,
        customer_id,
        customer_age,
        row_number() over (
                partition by
                product_id
                order by
                    cnt desc,
                    customer_age asc
             ) as rk
    from
         (select
            p.product_id,
            c.customer_id,
            c.customer_age,
            sum(o.quantity) as cnt
        from
            products p
            left join orders o on p.product_id = o.product_id
            left join customers c on o.customer_id = c.customer_id
        where
            year(o.order_date) = '2023'
        group by
            p.product_id,
            c.customer_id,
            c.customer_age) sub
),
age as (
    select
        product_id,
        case
            when customer_age between 1 and 10  then '1-10'
            when customer_age between 11 and 20  then '11-20'
            when customer_age between 21 and 30  then '21-30'
            when customer_age between 31 and 40  then '31-40'
            when customer_age between 41 and 50  then '41-50'
            when customer_age between 51 and 60  then '51-60'
            else '61+'
        end as customer_age_group
    from
        customer1
    where
        rk = 1
)
select
    p.product_id,
    round(y.total_sales,2) total_sales,
    round(p.unit_price,2) unit_price,
    round(y.total_quantity,2) total_quantity,
    round(m.avg_monthly_sales,2) avg_monthly_sales,
    round(m.max_monthly_quantity,2) max_monthly_quantity,
    a.customer_age_group
from
    products p
    left join year y on p.product_id = y.product_id
    left join month m on p.product_id=m.product_id
    left join age a on p.product_id=a.product_id
order by
    total_sales desc,
    p.product_id asc

一个一个表拆开来写就还好，混在一起就很复杂

算月平均销售额用全年销售额/12，不要统计单独的每个月然后直接取平均，因为某几个月可能没有产品卖出