首页 > 试题广场 >

统计每个产品的销售情况

[编程题]统计每个产品的销售情况

热度指数：28719 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

数据查询需求说明

为了对每个产品的营销进行新的策划，需要统计2023年每个产品的销售情况。现有三个原始数据表格：customers（顾客）、products（产品）和orders（订单），其结构如下：

customers（顾客）

字段名	数据类型	说明
customer_id（顾客ID）	整数	顾客的唯一标识符
customer_name（顾客姓名）	字符串（最大长度50）	顾客的姓名
customer_email（顾客邮箱）	字符串（最大长度50）	顾客的电子邮箱地址
customer_age（顾客年龄）	整数	顾客的年龄
PRIMARY KEY (customer_id)	-	将customer_id设置为主键，确保每个顾客ID的唯一性。

products（产品）

字段名	数据类型	说明
product_id（产品ID）	整数	产品的唯一标识符
product_name（产品名称）	字符串（最大长度50）	产品的名称
unit_price（单价）	十进制数（保留两位小数）	产品的单价
PRIMARY KEY (product_id)	-	将product_id设置为主键，确保每个产品ID的唯一性。

orders（订单）

字段名	数据类型	说明
order_id（订单ID）	整数	订单的唯一标识符
customer_id（顾客ID）	整数	顾客的ID，对应`customers`表格中的`customer_id`
product_id（产品ID）	整数	产品的ID，对应`products`表格中的`product_id`
quantity（数量）	整数	产品的数量
order_date（订单日期）	日期	订单的日期
PRIMARY KEY (order_id)	-	将order_id设置为主键，确保每个订单ID的唯一性。

查询要求

根据上述表格，查询2023年每个产品的以下信息：

产品ID（product_id）：产品的ID。
总销售额（total_sales）：该产品的2023年总销售额。
单价（unit_price）：产品的单价。
总销量（total_quantity）：该产品的2023年总销售数量。
月平均销售额（avg_monthly_sales）：2023年该产品的月均销售额。
单月最高销量（max_monthly_quantity）：2023年该产品的最大月销售数量。
购买量最多的客户年龄段（customer_age_group）：2023年购买该产品数量最多的顾客的年龄段（1-10，11-20，21-30，31-40，41-50，51-60，61+）

排序规则

按照每个产品的总销售额降序排列。
如果总销售额一致，则按照产品的ID升序排列。
当存在两个客户年购买量都是最高时，customer_age_group展示年龄小的顾客的年龄段。

计算说明

总销售额 = 总销量 × 单价
月平均销售额 = 总销售额 / 12
所有计算结果保留两位小数。

【示例】

customers（顾客）表格

products（产品）表格

orders（订单）表格

按要求查询出来的结果

示例说明

假设产品104的2023年销售总量是6，单价是120.00，则：

总销售额 = 6 × 120 = 720.00
月平均销售额 = 720 / 12 = 60.00
购买量最大的客户ID是2的Bob，年龄是30，所在年龄段是21-30。

示例1

输入

drop table if exists customers ;
drop table if exists products ;
drop table if exists orders ;
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(50),
    customer_email VARCHAR(50),
    customer_age INT,
    PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, customer_email, customer_age) VALUES
(1, 'Alice', 'alice@example.com', 25),
(2, 'Bob', 'bob@example.com', 30),
(3, 'Charlie', 'charlie@example.com', 22),
(4, 'David', 'david@example.com', 18),
(5, 'Eve', 'eve@example.com', 35);

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50),
    unit_price DECIMAL(10, 2),
    PRIMARY KEY (product_id)
);

INSERT INTO products (product_id, product_name, unit_price) VALUES
(101, 'Product A', 50.00),
(102, 'Product B', 75.00),
(103, 'Product C', 100.00),
(104, 'Product D', 120.00),
(105, 'Product E', 90.00);

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    PRIMARY KEY (order_id)
);

INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 1, 101, 2, '2023-01-15'),
(2, 2, 102, 3, '2023-02-20'),
(3, 3, 103, 1, '2023-03-10'),
(4, 4, 104, 2, '2023-04-05'),
(5, 5, 105, 4, '2023-05-12'),
(6, 1, 102, 2, '2023-06-18'),
(7, 2, 103, 3, '2023-07-22'),
(8, 3, 104, 1, '2023-08-30'),
(9, 4, 105, 2, '2023-09-14'),
(10, 5, 101, 4, '2023-10-25'),
(11, 1, 103, 2, '2023-11-08'),
(12, 2, 104, 3, '2023-12-19');

输出

product_id|total_sales|unit_price|total_quantity|avg_monthly_sales|max_monthly_quantity|customer_age_group
104|720.00|120.00|6|60.00|3|21-30
103|600.00|100.00|6|50.00|3|21-30
105|540.00|90.00|6|45.00|4|31-40
102|375.00|75.00|5|31.25|3|21-30
101|300.00|50.00|6|25.00|4|31-40

算法知识视频讲解

Mysql

牛客940606850号

select temp1.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,case when customer_age < concat(left(customer_age,1),1) then concat(left(customer_age,1)-1,1,'-',left(customer_age,1),0)
else concat(left(customer_age,1),1,'-',left(customer_age,1)+1,0) end as customer_age_group
from 
(SELECT o.product_id,round(sum(unit_price * quantity),2) as total_sales,unit_price,sum(quantity) as total_quantity,
round(sum(unit_price * quantity)/12,2) as avg_monthly_sales
from orders o
left join products p
using (product_id)
where year(order_date) = '2023'
group by o.product_id) temp1
left join 
(select product_id,max(cnt) as max_monthly_quantity
from
(select product_id,sum(quantity) over(partition by product_id,month(order_date)) as cnt
from orders) t group by product_id) temp2
on temp1.product_id = temp2.product_id
left join 
(select product_id,customer_age
from
(select customer_id,product_id,customer_age,dense_rank() over(partition by product_id order by cnt desc,customer_age) as rk
from
(select customer_id,product_id,sum(quantity) as cnt,customer_age
from customers c
left join orders o 
using(customer_id)
group by customer_id,product_id) t) t1
where rk = 1) temp3
on temp2.product_id = temp3.product_id
order by 2 desc,1

发表于 2025-11-28 19:41:03 回复(0)

Mysql

刷题仔a

select

zzy1.product_id

,total_sales

,unit_price

,total_quantity

,avg_monthly_sales

,max_monthly_quantity

,customer_age_group

from

(select

o.product_id

,sum(o.quantity*p.unit_price) total_sales

,p.unit_price

,sum(quantity) total_quantity

,round(sum(o.quantity*p.unit_price)/12,2) avg_monthly_sales

from

orders o

join products p on o.product_id=p.product_id

group by 1)zzy1

join

(select

product_id

,max(x) max_monthly_quantity

from

(select

product_id

,sum(quantity) over(partition by product_id,month(order_date)) x

from

orders)z

group by 1)zzy2

on zzy1.product_id=zzy2.product_id

join

(select

product_id

,customer_age_group

from

(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'

WHEN customer_age > 60 THEN '61+'

ELSE '未知' -- 处理可能的 NULL 或异常值

END AS customer_age_group

,sum(quantity) s

,row_number() over(partition by product_id order by sum(quantity) desc) rk

FROM

orders o

join customers c on o.customer_id=c.customer_id

GROUP BY

1,2)zy

where rk=1)zzy3

on zzy3.product_id=zzy1.product_id

order by total_sales desc;

发表于 2025-11-02 23:43:46 回复(0)

Mysql

像初雪一样遇见你

with t1 as (select x.product_id,x.unit_price,
sum(unit_price*quantity) as total_sales,
sum(quantity) as total_quantity,
round(sum(unit_price*quantity)/12,2) as avg_monthly_sales
from products x
join orders y
using(product_id)
where year(order_date)=2023
group by 1,2),t2 as (
select distinct x.product_id,max(sum(quantity)) over(partition by x.product_id) as max_monthly_quantity
from products x
join orders y
using(product_id)
where year(order_date)=2023
group by x.product_id,left(order_date,7)),t3 as (
select 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,product_id,
sum(quantity) as qs
from customers
join orders
using(customer_id)
where year(order_date)=2023
group by 1,2),t4 as (
select product_id,customer_age_group
from (select product_id,customer_age_group,row_number() over(partition by product_id order by qs desc,customer_age_group) as rk
from t3) e
where rk=1)
select t1.product_id,total_sales,unit_price,total_quantity
,avg_monthly_sales,max_monthly_quantity,customer_age_group
from t1  
join t2
on t1.product_id=t2.product_id
join t4 
on t1.product_id=t4.product_id
order by 2 desc,1

发表于 2025-10-16 21:35:53 回复(0)

Mysql

你好李知恩

select product_id
,total_sales
,unit_price
,total_quantity
,round(avg_monthly_sales,2) as avg_monthly_sales
,quantity as max_monthly_quantity
,customer_age_group
from (
    select t1.product_id
    ,sum(unit_price*quantity) over(partition by t1.product_id) as total_sales
    ,unit_price
    ,sum(quantity) over(partition by t1.product_id) as total_quantity
    ,month(order_date)
    ,quantity
    ,rank() over(partition by t1.product_id order by quantity desc,customer_age asc) as monthly_quantity_rk
    ,sum(unit_price*quantity/12) over(partition by t1.product_id) as avg_monthly_sales
    ,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 orders t1
    left join products t2
    on t1.product_id = t2.product_id
    left join customers t3
    on t1.customer_id = t3.customer_id
) t4
where monthly_quantity_rk = 1
order by total_sales desc,product_id

想不到怎么用传统方法聚合，直接开窗吧。

第一层查询：

1、把三张表连在一起；

2、sum开窗计算各产品的销售额；

3、sum开窗算各产品的总销售数量；

4、按产品分块，排序模式为销售数量倒序+客户年龄正序，开窗算rank排名；

5、sum开窗算总销量/12作为月均销售金额；

6、case把具体年龄转为年龄组文本。

第二层查询：

1、把前面查出来的内容全部select一遍；

2、处理一下avg_monthly_sales，弄成两位小数；

3、第一层查询中按照每月最大销售量进行降序排序，这里where限定一下rk = 1，就能保证取出每月最大值；

4、按要求order by一下。

发表于 2025-08-13 14:56:55 回复(5)

Mysql

牛客155605705号

select
t1.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from
(
    select
    product_id,
    round(sum(mon_sales),2) total_sales,
    round(avg(unit_price),2) unit_price,
    sum(quantity) total_quantity,
    round(sum(mon_sales)/12,2) avg_monthly_sales,  
    max(quantity) max_monthly_quantity
    from
    (
        select
        b.product_id,
        month(b.order_date) mon,
        sum(a.unit_price*b.quantity) mon_sales,
        avg(a.unit_price) unit_price,
        sum(b.quantity) quantity
        from products a
        join orders b
        on a.product_id=b.product_id
        where year(b.order_date)=2023
        group by b.product_id,month(b.order_date)
    ) t
    group by product_id
) t1
join
(
    select
    product_id,
    customer_age_group
    from
    (
        select
        product_id,
        customer_age_group,
        cnt,
        row_number() over(partition by product_id order by cnt desc, customer_age_group) rn
        from
        (
            select
            b.product_id,a.customer_age_group,
            sum(b.quantity) cnt
            from
            (
                select
                customer_id,
                case when customer_age between 1 and 10 then '1-10'
                     when customer_age between 21 and 30 then '21-30'
                     when customer_age between 11 and 20 then '11-20'
                     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'
                     when customer_age > 60 then '61+'
                end as customer_age_group
                from customers
            ) a
            join orders b
            on a.customer_id=b.customer_id
            where year(b.order_date)=2023
            group by b.product_id,a.customer_age_group
        ) t
    ) r
    where rn=1
) t2
on t1.product_id=t2.product_id
order by total_sales desc, product_id

发表于 2025-07-28 12:32:04 回复(0)

Mysql

s_moon

虽然占了很多内存，但是是我自己辛辛苦苦敲出来的，纪念一下

with b as (
select product_id, customer_id, customer_age, quantity,
unit_price * quantity as sum_sales,
sum(unit_price * quantity) over(partition by product_id) as total_sales,
unit_price,
sum(quantity) over(partition by product_id order by product_id asc ) as total_quantity,
max(quantity) over(partition by month_order_date,product_id) as max_monthly_quantity
from 
(
select
c.customer_id as customer_id, 
c.customer_age as customer_age,
p.product_id as product_id, 
p.unit_price as unit_price,
o.quantity as quantity, 
o.order_date as order_date,
month(o.order_date) as month_order_date
from orders o 
join customers c
on o.customer_id=c.customer_id
join products p 
on o.product_id=p.product_id and year(o.order_date) = '2023'
) as a
)


select product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group 
from (
select *,
round(total_sales/12,2) as avg_monthly_sales,
rank() over(partition by product_id order by quantity desc ,customer_age asc ) as rn,
(
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 '51-60'
else '60+' 
end) as customer_age_group 
from b 
) as c 
where rn =1
order by total_sales desc, product_id asc

发表于 2025-07-10 16:47:20 回复(0)

Mysql

ke1ke2

select

product_id,

total_sales,

unit_price,

total_quantity,

avg_monthly_sales,

max(max_monthly_quantity) over(partition by product_id) as max_monthly_quantity,

max(customer_age_group) over(partition by product_id) as customer_age_group

from (

select

product_id,

sum(quantity) over(partition by product_id, year(order_date)) as total_quantity,

unit_price,

(sum(quantity) over(partition by product_id, year(order_date))) * unit_price as total_sales,

round((sum(quantity) over(partition by product_id, year(order_date))) * unit_price / 12, 2) as avg_monthly_sales,

max(quantity) over(partition by product_id, month(order_date)) as max_monthly_quantity,

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 '60+'

end as customer_age_group,

rank() over(partition by product_id order by quantity desc, customer_age) as rk

from (

select

o.product_id,

unit_price,

quantity,

order_date,

customer_age

from orders as o

join customers as c on o.customer_id = c.customer_id

join products as p on o.product_id = p.product_id

) as b

where year(order_date) = 2023

) as a

where rk = 1

order by total_sales desc, product_id asc

发表于 2025-06-25 13:32:33 回复(0)

Mysql

牛客653287588号

select a.*,b.customer_age_group
from (
select product_id
,sum(total_sales)total_sales
,unit_price
,sum(quantity)total_quantity
,round(sum(total_sales)/12,2) avg_monthly_sales
,max(max_sale_month) max_monthly_quantity
from (
select a.product_id
,quantity*unit_price total_sales
,unit_price
,quantity 
,sum(quantity)over(partition by month(order_date),product_id ) max_sale_month
from orders a 
left join products b 
on a.product_id = b.product_id
where year(order_date)='2023'
)t 
group by product_id,unit_price
)a 
left join (

select product_id
,case when customer_age<=10 then '1-10'
    when customer_age<=20 then '11-20'
    when customer_age<=30 then '21-30'
    when customer_age<=40 then '31-40'
    when customer_age<=50 then '41-50'
    when customer_age<=60 then '51-60'
    else '61+' end customer_age_group
,sum(quantity) quantity
,row_number()over(partition by product_id order by sum(quantity) desc ,min(customer_age_group)) rn
from orders a 
left join  customers c 
on a.customer_id = c.customer_id
where year(order_date)='2023'
group by product_id
,case when customer_age<=10 then '1-10'
    when customer_age<=20 then '11-20'
    when customer_age<=30 then '21-30'
    when customer_age<=40 then '31-40'
    when customer_age<=50 then '41-50'
    when customer_age<=60 then '51-60'
    else '61+' end
    
)b 
on a.product_id = b.product_id and b.rn = 1
order by total_sales desc ,a.product_id

发表于 2025-06-18 22:27:33 回复(0)

Mysql

牛客917810122号

这道题的题目有问题吧

104的customer_age_group是21-30

根据原表购买104的用户分别是2，3，4，购买量都是1，那么
年龄最小的分段不应该是11-20吗？

发表于 2025-05-10 15:00:53 回复(0)

Mysql

盘永帅

--我燃尽了，为什么这个说：您的程序输出的格式不符合要求（比如空格和换行与要求不一致）。但是结果又是对的

select

product_id,

total_sales,

unit_price,

total_quantity,

avg_monthly_sales,

max_monthly_quantity,

customer_age_group

from

(select

a.product_id,

a.unit_price,

round(sum(a.unit_price*b.quantity),2) as total_sales,

sum(b.quantity) as total_quantity,

round(sum(a.unit_price*b.quantity)/12,2) as avg_monthly_sales,

c.max_monthly_quantity,

row_number() over(partition by b.product_id order by c.max_monthly_quantity desc ) as rk,

case

when d.customer_age between 1 and 10 then '1 - 10'

when d.customer_age between 11 and 20 then '11 - 20'

when d.customer_age between 21 and 30 then '21 - 30'

when d.customer_age between 31 and 40 then '31 - 40'

when d.customer_age between 41 and 50 then '41 - 50'

when d.customer_age between 51 and 60 then '51 - 60'

else '61+'

end as customer_age_group

from products a

join orders b

on a.product_id=b.product_id

join (

select

o.product_id,

max(o.quantity) as max_monthly_quantity,

o.customer_id

from

products p

join orders o

on p.product_id=o.product_id

group by

o.product_id,

o.customer_id

) c

on c.product_id=b.product_id

join customers d

on c.customer_id=d.customer_id

group by

a.product_id,

a.unit_price,

c.max_monthly_quantity,

customer_age_group,

d.customer_age

) e

where rk=1

order by

total_sales desc,

product_id asc

发表于 2025-04-29 17:07:31 回复(1)

Mysql

SG9

with sales as(select 
        product_id,
        unit_price,
        sum(quantity) as total_quantity,
        round(sum(quantity * unit_price), 2) as total_sales,
        round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales
    from orders o
    join products p using (product_id)
    group by product_id
),
monthly_quantity as
( 	select product_id,
	max(monthly_quantity) as max_monthly_quantity
    from
	(select 
		product_id,
		sum(quantity) as monthly_quantity
	from orders o
    join products p using (product_id)
    group by month(order_date), product_id) as a
    group by product_id
),
customers_max as(
	select
		customer_id,
        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,
        quantity_customer as product_customers_max
	from(
		select customer_id,
		product_id,
        customer_age,
		sum(quantity) as quantity_customer,
        row_number() over (partition by product_id order by sum(quantity) desc) as rk
		from orders o
		join  customers c using (customer_id)
		group by product_id, o.customer_id
		order by product_id, customer_age asc) as b
	where rk = 1
)
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from sales join monthly_quantity using(product_id)
join customers_max using (product_id)
order by total_sales desc, product_id asc

写了一整个小时，好崩溃

发表于 2025-04-16 19:00:56 回复(0)

Mysql

小小老牛

学习数仓的小伙伴可以看我的代码，根据数仓分层构建

dwd层清洗数据统一字段，dws按产品id，月份，消费者组初步聚合，ads层进一步聚合，最后筛选一下

with dwd as (select distinct
    a.product_id,
    unit_price,
    quantity,
    left(order_date,7) date,
    case when customer_age between 1 and 10 then "01-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"
         when customer_age >60 then "60+"   
         end age_group          
from products a 
inner join orders b on a.product_id=b.product_id inner join customers c on b.customer_id=c.customer_id),

dws as (select distinct
    *,
    sum(quantity) over(partition by product_id,date) month_num,
    sum(quantity*unit_price) over(partition by product_id,date) month_total,
    sum(quantity) over(partition by product_id,age_group) age_num
from dwd),

ads as(
select distinct
    product_id,
    sum(month_total) over(partition by product_id) total_sales,
    unit_price,
    sum(month_num) over(partition by product_id) total_quantity,
    round(sum(month_total) over(partition by product_id)/12,2) avg_monthly_sales,
    max(month_num) over(partition by product_id) max_monthly_quantity,
    age_group,
    row_number() over(partition by product_id order by age_num desc,age_group) flag
from dws
order by 2 desc ,1)
select
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    age_group customer_age_group
from ads where flag=1

发表于 2025-04-08 19:14:20 回复(0)

提交观点

问题信息

SQL

来自：2024年春招-淘天集...

难度：

15条回答 698收藏 1594浏览

统计每个产品的销售情况

查询要求

排序规则

计算说明

示例说明

输入

输出

问题信息

热门推荐

相关试题