首页 > 试题广场 >

统计每个产品的销售情况

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

热度指数：28909 时间限制：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

算法知识视频讲解

yummy爱赚钱

with t1 as #计算 前5个字段
    (select 
        p.product_id
        ,sum(quantity*unit_price) total_sales
        ,unit_price
        ,sum(quantity) total_quantity
        ,round(sum(quantity*unit_price)/12,2) avg_monthly_sales
    from products p join orders o on p.product_id=o.product_id
    group by p.product_id,unit_price)
,t2 as #计算“产品的 最大 月销售数量”
    (select 
        product_id
        ,month(order_date) month
        ,sum(quantity) monthly_quantity
    from orders
    group by product_id,month)
,t3 as #计算“产品的 最大 月销售数量”
    (select 
        product_id
        ,max(monthly_quantity) max_monthly_quantity 
    from t2
    group by product_id)
,t4 as #计算顾客"年龄段"、“产品购买数”
    (select 
        product_id
        ,o.customer_id
        ,sum(quantity)  某顾客购买该产品的数量
        ,row_number()over(partition by product_id order by sum(quantity) desc,customer_age) c_quantity_rk
        ,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 customer_age_group 
    from customers c join orders o on c.customer_id=o.customer_id
    group by o.customer_id,product_id,customer_age_group)
,t5 as #计算 购买该产品的数量最多的顾客的年龄段
    (select 
        product_id,customer_age_group from t4
    where c_quantity_rk=1)
select 
    t1.product_id
    ,total_sales
    ,unit_price
    ,total_quantity
    ,avg_monthly_sales
    ,max_monthly_quantity
    ,customer_age_group
from t1
    join t3 on t1.product_id=t3.product_id
    join t5 on t1.product_id=t5.product_id
order by total_sales desc,product_id

发表于 2025-03-23 16:35:05 回复(1)

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)

东方宸星

什么破题，又臭又长

WITH initial_data AS (
    SELECT
        product_id,
        SUM(quantity * unit_price) AS total_sales,
        unit_price,
        SUM(quantity) AS total_quantity,
        ROUND(SUM(quantity * unit_price) / 12, 2) AS avg_monthly_sales
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023
    GROUP BY
        product_id
),
monthly_data AS (
    SELECT 
        product_id,
        MONTH(order_date),
        SUM(quantity) AS monthly_quantity,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY SUM(quantity) DESC) AS rk_monthly
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023
    GROUP BY
        product_id,
        MONTH(order_date)
),
age_group_data AS (
    SELECT 
        product_id,
        CASE 
            WHEN customer_age >= 1 AND customer_age <= 10 THEN '1-10'
            WHEN customer_age >= 11 AND customer_age <= 20 THEN '11-20'
            WHEN customer_age >= 21 AND customer_age <= 30 THEN '21-30'
            WHEN customer_age >= 31 AND customer_age <= 40 THEN '31-40'
            WHEN customer_age >= 41 AND customer_age <= 50 THEN '41-50'
            WHEN customer_age >= 51 AND customer_age <= 60 THEN '51-60'
            ELSE '61+'
        END AS age_group,
        CASE 
            WHEN customer_age >= 1 AND customer_age <= 10 THEN 5
            WHEN customer_age >= 11 AND customer_age <= 20 THEN 15
            WHEN customer_age >= 21 AND customer_age <= 30 THEN 25
            WHEN customer_age >= 31 AND customer_age <= 40 THEN 35
            WHEN customer_age >= 41 AND customer_age <= 50 THEN 45
            WHEN customer_age >= 51 AND customer_age <= 60 THEN 55
            ELSE 65
        END AS age_gap,
        quantity
    FROM 
        orders
        LEFT JOIN products USING (product_id)
        LEFT JOIN customers USING (customer_id)
    WHERE 
        YEAR(order_date) = 2023

),
age_quantity_data AS (
    SELECT
        product_id,
        age_group,
        SUM(quantity) AS age_group_quantity,
        RANK() OVER (PARTITION BY product_id ORDER BY SUM(quantity) DESC, age_gap) AS rk_age
    FROM 
        age_group_data
    GROUP BY 
        product_id,
        age_group,
        age_gap
)
SELECT
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    monthly_quantity AS max_monthly_quantity,
    age_group AS customer_age_group
FROM 
    initial_data
    LEFT JOIN monthly_data USING (product_id)
    LEFT JOIN age_quantity_data USING (product_id)
WHERE 
    rk_monthly = 1
    AND rk_age = 1
ORDER BY 
    total_sales DESC,
    product_id

发表于 2025-07-01 12:33:19 回复(0)

Sqlite

我真的也叫乐杨

#计算商品总销售额，单价，总数，月均销售额
with t1 as
(select o.product_id as product_id, unit_price*sum(quantity) as total_sales, unit_price, sum(quantity) as total_quantity, round(unit_price*sum(quantity)/12,2) as avg_monthly_sales from orders o left join products p on o.product_id = p.product_id
group by product_id),
#计算最大月销售量
t2 as 
(select product_id, month, month_quantity as max_monthly_quantity from
(select product_id, month(order_date) as month, sum(quantity) as month_quantity, row_number()over(partition by product_id order by sum(quantity) desc) as rk from orders
group by product_id, month(order_date)) s1
where rk = 1),
#处理年龄
t3 as
(select product_id, customer_age, 
(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 >= 61 then '61+'
end) customer_age_group
from
(select product_id,o.customer_id as customer_id, sum(quantity) as sq, customer_age, row_number()over(partition by product_id order by sum(quantity) desc, customer_age) as rk from orders o left join customers c on o.customer_id = c.customer_id
group by product_id, o.customer_id) s2
where rk = 1)
#连接表
select t1.product_id as 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 t3 on t1.product_id = t3.product_id
order by total_sales desc, product_id

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

专注当下喲

with t as (select product_id,min(customer_age) as kk from (select d.product_id,customer_age
from orders d
inner join customers e
inner join (select product_id,max(quantity) as max_q from orders 
group by product_id) as l
on d.customer_id=e.customer_id
and d.product_id=l.product_id
where quantity=max_q) aa
group by aa.product_id),
y as (select product_id,max(monthly_quantity) as max_monthly_quantity from (select product_id,sum(quantity) as monthly_quantity from orders 
group by product_id,DATE_FORMAT(order_date, '%Y-%m') ) bb
group by product_id)

select q.product_id,total_sales,unit_price,total_quantity,round(avg_monthly_sales,2) as avg_monthly_sales,y.max_monthly_quantity,(case when t.kk>=1 and t.kk<=10 then '1-10' when t.kk>=11 and t.kk<=20 then '11-20' when t.kk>=21 and t.kk<=30 then '21-30' when t.kk>=31 and t.kk<=40 then '31-40' when t.kk>=41 and t.kk<=50 then '41-50' when t.kk>=51 and t.kk<=60 then '51-60' else '61+' end) as customer_age_group
from(
select a.product_id,sum(quantity) *unit_price as total_sales,unit_price,
sum(quantity) as total_quantity,(sum(quantity) *unit_price)/12 as avg_monthly_sales
from orders a
inner join products b
inner join customers c
on a.customer_id=c.customer_id
and a.product_id=b.product_id
group by a.product_id,unit_price
order by total_sales desc,a.product_id) q
left join t on q.product_id=t.product_id
left join y on q.product_id=y.product_id

难吐了，4小时，你知道我这四小时是怎么过的吗？ 
啊啊啊啊啊啊啊！！！1

发表于 2025-03-19 17:53:48 回复(4)

FishAndBird

with t1 as (
    ## 计算商品最大月销量和总销量，后续关联商品表计算总销售额，月平均销售额，单月最高销量
    select
        product_id,
        max(month_quantity) as max_monthly_quantity,
        sum(month_quantity) as total_quantity
    from(
        select 
            product_id, 
            month(order_date) as date_month,
            sum(quantity) as month_quantity
        from orders
        group by product_id, month(order_date)) a
    group by product_id),
t2 as (
    ## 关联订单表和用户表，使用开窗函数计算产品 购买量最多的客户年龄段
    select
        product_id,
        customer_age_group
    from(
        select
            product_id,
            customer_age_group,
            sum(quantity) as total_quantity,
            row_number() over (partition by product_id order by sum(quantity) desc, customer_age_group asc) as rank_p
        from(
            select
                o.product_id as product_id,
                o.quantity as quantity,
                case 
                    when c.customer_age between 1 and 10 then '1-10'
                    when c.customer_age between 11 and 20 then '11-20'
                    when c.customer_age between 21 and 30 then '21-30'
                    when c.customer_age between 31 and 40 then '31-40'
                    when c.customer_age between 41 and 50 then '41-50'
                    when c.customer_age between 51 and 60 then '51-60'
                    else '61+' 
                end as customer_age_group
            from orders o join customers c
            on o.customer_id = c.customer_id) a
        group by product_id, customer_age_group) b
    where rank_p = 1
)
## 关联t1,t2和产品表，汇总最终结果
select
    t3.product_id as product_id,
    round(t1.total_quantity * t3.unit_price, 2) as total_sales,
    t3.unit_price as unit_price,
    t1.total_quantity as total_quantity,
    round(t1.total_quantity * t3.unit_price / 12, 2) as avg_monthly_sales,
    t1.max_monthly_quantity as max_monthly_quantity,
    t2.customer_age_group as customer_age_group
from products t3 
join t1 
on t3.product_id = t1.product_id
join t2 
on t1.product_id = t2.product_id
order by total_sales desc, product_id asc

发表于 2025-11-28 17:09:12 回复(0)

B20220304327

with
    oc as (
        select
            customer_id,
            product_id,
            customer_age_group
        from
            (
                select
                    s.customer_id,
                    product_id,
                    if(
                        customer_age < 61,
                        concat(
                            truncate
                                ((customer_age -1) / 10, 0) * 10 + 1,
                                '-',
                            truncate
                                ((customer_age -1) / 10, 0) * 10 + 10
                        ),
                        '61+'
                    ) customer_age_group,
                    row_number() over (
                        partition by
                            s.product_id
                        order by
                            quantity desc,
                            customer_age
                    ) as rk
                from
                    (
                        select
                            orders.customer_id,
                            orders.product_id,
                            sum(quantity) quantity,
                            customer_age
                        from
                            orders
                            join customers on orders.customer_id = customers.customer_id
                        group by
                            orders.product_id,
                            orders.customer_id,
                            customer_age
                    ) s
            ) s
        where
            rk <= 1
    ),
    mo as (
        select
            product_id,
            max(month_quantity) month_quantity
        from
            (
                select
                    product_id,
                    sum(quantity) month_quantity
                from
                    orders
                group by
                    product_id,
                    month(order_date)
            ) s
        group by
            product_id
    )
select
    o.product_id,
    unit_price * sum(quantity) total_sales,
    max(unit_price) unit_price,
    sum(quantity) total_quantity,
    round(unit_price * sum(quantity) / 12, 2) avg_monthly_sales,
    max(month_quantity) max_monthly_quantity,
    customer_age_group
from
    orders o
    join oc on o.product_id = oc.product_id
    join products p on o.product_id = p.product_id
    join mo on mo.product_id = o.product_id
group by
    o.product_id,
    customer_age_group
order by
    total_sales desc,
    o.product_id

发表于 2025-10-17 11:48:37 回复(0)

牛客234432993号

select product_id,max(total_sales)as total_sales,max(unit_price)as unit_price,max(total_quantity)as total_quantity,max(avg_monthly_sales)as avg_monthly_sales,max(max_monthly_quantity)as max_monthly_quantity, max(customer_age_group)as customer_age_group from

(select p.product_id,sum(quantity*unit_price)over(partition by product_id)as total_sales,unit_price,

sum(quantity)over(partition by product_id)as total_quantity,round(sum(quantity*unit_price)over(partition by product_id)/12,2) as avg_monthly_sales,max(quantity)over(partition by (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'

when customer_age >= 61 then '61+'

end as customer_age_group

from customers c join orders o on c.customer_id=o.customer_id join products p on p.product_id=o.product_id)z

group by product_id

order by max(total_sales)desc

哪位大佬告诉我，为什么年龄会有错误呀怎么解决呢

发表于 2025-10-14 11:15:41 回复(1)

弥米尔

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

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

max(a.quantity) as max_monthly_quantity,

max(case when d.age between 1 and 10 then '1-10'

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

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

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

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

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

when d.age >60 then '61+'

end) as customer_age_group

from orders a join products b on a.product_id = b.product_id

join customers c on a.customer_id = c.customer_id

join (select product_id,age from (

select a.product_id as product_id ,c.customer_age as age ,sum(a.quantity),ROW_NUMBER()over(partition by a.product_id order by a.product_id,sum(a.quantity) desc,c.customer_age) as rn

from orders a join customers c on a.customer_id = c.customer_id

group by a.product_id,c.customer_age

)a1

where rn = 1 ) d on a.product_id = d.product_id

group by a.product_id

order by total_sales desc

发表于 2025-08-11 17:27:32 回复(0)

牛客428980258号

有没有佬帮我看看问题出在哪TT

with t1 as 
(select product_id,customer_age_group
from
(select product_id,rank()over(partition by product_id order by quantity desc) as rk,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 as customer_age_group
from orders join customers using(customer_id)
where year(order_date) = 2023)t0
where rk = 1),
t2 as 
(select product_id,month(order_date) as month,sum(quantity) as sum_quantity
from products join orders using(product_id)
where year(order_date) = 2023
group by product_id,month)
select o.product_id,sum(quantity*unit_price) as total_sales,any_value(unit_price) as unit_price,sum(quantity) as total_quantity,round(sum(quantity*unit_price)/12,2) as avg_monthly_sales,max(sum_quantity) as max_monthly_quantity,any_value(customer_age_group) as customer_age_group
from products p right join orders o on p.product_id = o.product_id
left join t1 on o.product_id = t1.product_id
left join t2 on o.product_id = t2.product_id 
and month(o.order_date) = t2.month
where year(order_date) = 2023
group by o.product_id
order by total_sales desc,o.product_id

发表于 2025-07-23 16:45:00 回复(0)

Sqlite

汪星鱼

select

a.product_id,

total_sales,

unit_price,

total_quantity,

round(avg_monthly_sales, 2),

max_monthly_quantity,

customer_age_group

from

(

select

p.product_id,

unit_price,

sum(quantity) total_quantity,

sum(quantity) * unit_price total_sales,

sum(quantity) * unit_price / 12 avg_monthly_sales

from

products p

join orders o on p.product_id = o.product_id

where

year (order_date) = 2023

group by

p.product_id,

unit_price

) a

join (

select

product_id,

concat ('-', year (order_date), month (order_date)) yf,

sum(quantity) max_monthly_quantity,

row_number() over (

partition by

product_id

order by

sum(quantity) desc

) rk

from

orders

where

year (order_date) = 2023

group by

product_id,

concat ('-', year (order_date), month (order_date))

) b on a.product_id = b.product_id

join (

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

end as customer_age_group,

row_number() over (

partition by

product_id

order by

sum(quantity) desc,

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

end

) rk1

from

orders o

join customers c on o.customer_id = c.customer_id

where

year (order_date) = 2023

group by

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

end

) c on a.product_id = c.product_id

where

rk = 1

and rk1 = 1

order by

total_sales desc,

product_id

发表于 2025-06-11 11:35:00 回复(0)

牛客164735395号

with 
a as(select product_id , month(order_date) month, sum(quantity) quan,
row_number()over(partition by product_id order by sum(quantity) desc) ro
from orders group by product_id , month(order_date)),
b as (select o.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) ag , sum(quantity) cnt,
    row_number()over(partition by product_id order by sum(quantity) desc) ra
    from customers c
    join orders o on o.customer_id = c.customer_id 
    group by product_id,ag)
select o.product_id,
sum(p.unit_price*o.quantity) total_sales,
p.unit_price,
sum(o.quantity) total_quantity,
round(sum(p.unit_price*o.quantity)/12,2) avg_monthly_sales,
a.quan max_monthly_quantity,
b.ag customer_age_group
from orders o 
join products p on p.product_id = o.product_id
join a on a.product_id = o.product_id
join b on b.product_id = o.product_id
where a.ro = 1 and b.ra = 1
group by o.product_id,a.quan,b.ag
order by total_sales desc,product_id

前4条数据简单,5和6分别通过a表和b表用窗口函数来进行排序,其实拆开并不难,就是东西多了杂到一起就晕了

发表于 2025-05-02 22:58:03 回复(0)

4fbd81e

没什么说的，关键是耐心。

with t1 as(
    select p.*, o.order_id,o.customer_id,o.quantity,o.order_date,
    c.customer_name,  c.customer_email, c.customer_age,
    ELT(INTERVAL(c.customer_age,1,11,21,31,41,51,61),'1-10','11-20','21-30','31-40','41-50','51-60','61+') as age_group,
    month(o.order_date) as order_month
    from products p
    left join orders o
    on o.product_id=p.product_id
    left join customers c
    on c.customer_id=o.customer_id
    where year(o.order_date)=2023
)
,
result1 as(
    select product_id, max(total_quantity) as max_monthly_quantity
    from (
        select product_id,
        sum(quantity) as total_quantity
        from t1 
        group by product_id,order_month
    ) tmp1
    group by product_id
)
,
t3 as (
    select product_id,
    sum(quantity) as total_quantity,
    age_group
    from t1 
    group by product_id,age_group
)
,
result2 as(
    select product_id,age_group
    from  (
            select *, row_number() over(partition by product_id order by total_quantity desc, field(age_group,'1-10','11-20','21-30','31-40','41-50','51-60','61+') ) as rn from t3 
          ) tmp2
    where rn=1
)
,
result0 as(
    select 
    product_id,
    sum(quantity*unit_price) as total_sales,
    unit_price,
    sum(quantity) as total_quantity,
    round(sum(quantity*unit_price) /12.0, 2) as avg_monthly_sales
    from t1
    group by product_id
)

select r0.*,
r1.max_monthly_quantity,
r2.age_group as customer_age_group
from result0 r0
join result1 r1
on r0.product_id=r1.product_id
join result2 r2
on r0.product_id=r2.product_id
order by total_sales desc, product_id asc

发表于 2025-04-15 14:17:39 回复(0)

牛客806321860号

select t2.product_id, total_sales,unit_price, total_quantity,

avg_monthly_sales,max_monthly_quantity,customer_age_group

from(

select p.product_id,sum(quantity) *unit_price as total_sales,unit_price,sum(quantity) as total_quantity,round(sum(quantity) *unit_price/12,2) as avg_monthly_sales

from products p join orders o

on p.product_id=o.product_id

group by p.product_id,unit_price)t1

join

(select product_id,quantity 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 '61+ '

end as customer_age_group

from(

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

from customers c join orders o

on c.customer_id=o.customer_id)t

where rk=1)t2

on t1.product_id=t2.product_id

order by total_sales desc,product_id;

select t2.product_id, total_sales,unit_price, total_quantity,
avg_monthly_sales,max_monthly_quantity,customer_age_group
from(
select p.product_id,sum(quantity) *unit_price as total_sales,unit_price,sum(quantity) as total_quantity,round(sum(quantity) *unit_price/12,2) as avg_monthly_sales
from products p join orders o
on p.product_id=o.product_id
group by p.product_id,unit_price)t1
join
(select product_id,quantity 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 '61+ '
end as customer_age_group
from(
select product_id,quantity,customer_age,row_number() over(partition by product_id order by quantity desc,customer_age) as rk
from customers c join orders o
on c.customer_id=o.customer_id)t
where rk=1)t2
on t1.product_id=t2.product_id
order by total_sales desc,product_id;

发表于 2025-04-13 16:10:01 回复(1)

牛客695261949号

购买量最多的客户年龄段（customer_age_group）
1、每个产品每个用户购买数量
2、根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
3、排名第一的用户年龄
4、处理年龄区间

select product_id , total_sales , unit_price, 
         total_quantity,
        round(avg_monthly_sales,2) as avg_monthly_sales,
         max_monthly_quantity,
        case when maxq_age>=1 and maxq_age<=10 then '1-10'
        when maxq_age>=11 and maxq_age<=20 then'11-20'
        when maxq_age>=21 and maxq_age<=30 then'21-30'
        when maxq_age>=31 and maxq_age<=40 then'31-40'
        when maxq_age>=41 and maxq_age<=50 then'41-50'
        when maxq_age>=51 and maxq_age<=60 then'51-60'
        when maxq_age>=61 then'61+'
        end as customer_age_group
from (
    select product_id ,sum(sales) as total_sales , max(unit_price ) as unit_price, 
        sum(quantity) aS total_quantity,
        sum(sales)/12 aS avg_monthly_sales,
        max(mon_quantity) as max_monthly_quantity,
        排名第一的用户年龄
        max(case when rn =1 then customer_age end ) as maxq_age
    from (

        select customer_id , product_id ,quantity, sales, order_date,unit_price,customer_age,
            cus_quantity,
            mon_quantity,
            根据每个产品每个用户购买数量和用户年龄，统计购买数量降序年龄升序排名
            row_number()over(partition by product_id order by cus_quantity desc,customer_age) as rn
        from (
            select o.customer_id , o.product_id ,quantity, quantity*p.unit_price as sales, order_date,unit_price,c.customer_age,
            每个产品每个用户购买数量
                sum(quantity)over(partition by o.product_id, o.customer_id) as cus_quantity,
                月购买数量
                sum(quantity)over(partition by o.product_id, substr(order_date,1,7)) as mon_quantity
            from orders o inner join products p on o.product_id= p.product_id
            inner join customers c on o.customer_id= c.customer_id
            where substr(order_date,1,4)='2023'
        ) t 
    ) t
     group by product_id
)t

order by 2 desc ,1

发表于 2025-04-07 13:40:06 回复(1)

讲道理的熊熊整顿职场

#关联products和orders求得前六项
with a as(
    select p.product_id,
        round(sum(quantity*unit_price),2) total_sales,
        unit_price,
        sum(quantity) total_quantity,
        round((sum(quantity*unit_price))/12,2) avg_monthly_sales,
        max(quantity) max_monthly_quantity
    from products p 
    join orders o using(product_id)
    where year(order_date)=2023 
    group by p.product_id
),
#关联customers和orders分类，并用窗口函数根据product_id分组customer_age升序排序rk
 b as(
    select *,
    rank() over(partition by product_id order by customer_age) rk
    from 
    (select c.customer_id,product_id,customer_age,
    max(sum(quantity)) over(partition by product_id ) num,
    sum(quantity) um,
        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 customer_age_group
    from customers c left join orders o using(customer_id)
    group by c.customer_id,product_id)d
    where num=um 
)

	#关联a、b表，并得购买量都是最高时最小年龄段 

select a.product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from a join b using(product_id)
where rk=1
order by total_sales desc,product_id

发表于 2025-03-26 17:57:49 回复(1)

牛客461283432号

with t1 as(
    select
        a.product_id,
        (sum(a.quantity) * b.unit_price) as total_sales,
        b.unit_price,
        sum(a.quantity) as total_quantity,
        (sum(a.quantity) * b.unit_price)/12 as avg_monthly_sales
    from orders a inner join products b on a.product_id = b.product_id
    where year(a.order_date) = '2023'
    group by a.product_id, b.unit_price
)
, t2 as(
    select
        product_id,
        month(order_date) as month,
        sum(quantity) as monthly_quantity
    from orders
    where year(order_date) = '2023'
    group by product_id, month(order_date)
)
, t3 as(
    select
        product_id,
        max(monthly_quantity) as max_monthly_quantity
    from t2
    group by product_id
)
, t4 as(
    select
        a.product_id,
        a.customer_id,
        b.customer_age,
        sum(a.quantity) as quantity
    from orders a inner join customers b on a.customer_id = b.customer_id
    where year(a.order_date) = '2023'
    group by a.product_id, a.customer_id, b.customer_age
)
, t5 as(
    select
        product_id,
        customer_id,
        customer_age,
        row_number() over(partition by product_id order by quantity desc,customer_age asc) as rk
    from t4
)
, t6 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'
            when customer_age >= 61 then '61+'
        end as customer_age_group
    from t5
    where rk = 1
)
select
    a.product_id,
    round(t1.total_sales ,2) as total_sales,
    round(a.unit_price ,2) as unit_price,
    t1.total_quantity as total_quantity,
    round(t1.avg_monthly_sales, 2) as avg_monthly_sales,
    t3.max_monthly_quantity as max_monthly_quantity,
    t6.customer_age_group
from products a left join t1 on a.product_id = t1.product_id 
    left join t3 on a.product_id = t3.product_id left join t6 on a.product_id = t6.product_id
order by total_sales desc,product_id asc;

发表于 2025-12-17 15:06:19 回复(0)

在做ppt的小鱼干很想中彩票

这道题50分钟才写出来,正常吗?.......

select distinct *
from
(select product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,month_q max_monthly_quantity
from
(select product_id,min(unit_price) unit_price,sum(unit_price*quantity) as total_sales,sum(quantity) total_quantity,round(sum(unit_price*quantity)/12,2) avg_monthly_sales
from products t1
right join orders t2
using(product_id)
where order_date like '2023%'
group by product_id) t3
inner join
(select product_id,month_q
from
(select product_id,month_q,rank() over (partition by product_id order by month_q desc) as rk
from
(select product_id,sum(quantity) month_q
from orders 
group by product_id,month(order_date)) t4) t5
where rk = 1) t6
using(product_id)) nt
left join
(select *
from
(select product_id,case when row_number() over (partition by product_id order by quantity desc) = 1 then age_group else null end customer_age_group
from 
(select product_id,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 '61+' end as age_group
from customers
right join orders
using(customer_id)) nt2) nt3
where customer_age_group is not null) nt4
using(product_id)
order by total_sales desc,product_id;

发表于 2025-12-13 18:47:00 回复(0)

牛客36131829号

with

#年龄...

t as (select *,case when customer_age>=1 and customer_age<=10 then '1-10'

when customer_age>=11 and customer_age<=20 then '11-20'

when customer_age>=21 and customer_age<=30 then '21-30'

when customer_age>=31 and customer_age<=40 then '31-40'

when customer_age>=41 and customer_age<=50 then '41-50'

when customer_age>=51 and customer_age<=60 then '51-60'

when customer_age>=61 then '60+' end age

from customers

#求每个订单——要求的字段

#产品ID，总销售额，产品的单价，总销量，月平均销售额，当月销量，该年龄段购买的数量，客户年龄段

t2 as (

select o.product_id product_id,

sum(unit_price*quantity) over (partition by o.product_id,unit_price) total_sales,

unit_price,

sum(quantity) over (partition by o.product_id,unit_price) total_quantity,

round(sum(unit_price*quantity) over (partition by o.product_id,unit_price)/12,2) month_sales,

max(quantity) over (partition by o.product_id,month(order_date)) monthly_quantity,t.age age,

sum(quantity) over (partition by o.product_id,t.age) age_quantity

from orders o join t on o.customer_id=t.customer_id

join products p on o.product_id=p.product_id

where year(order_date)=2023

order by total_sales desc,age_quantity

#字段拎出来

t3 as (select product_id,total_sales,unit_price,total_quantity,month_sales avg_monthly_sales,monthly_quantity,age_quantity,age

from t2 ),

#给序号，后续只要rown=1的——该产品年龄数量max,每月销量max

t4 as (select * ,row_number() over (partition by product_id order by monthly_quantity desc,age_quantity desc) rown

from t3)

#得到结果...

select product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,monthly_quantity max_monthly_quantity,age customer_age_group

from t4

where rown=1

order by total_sales desc

发表于 2025-12-12 20:06:08 回复(0)

MCMN

SELECT
prod_stats.product_id,
prod_stats.total_sales,
prod_stats.unit_price,
prod_stats.total_quantity, -- 产品总购买量
prod_stats.avg_monthly_sales,
prod_stats.max_monthly_quantity,
CASE
WHEN t3.customer_age BETWEEN 1 AND 10 THEN '1-10'
WHEN t3.customer_age BETWEEN 11 AND 20 THEN '11-20'
WHEN t3.customer_age BETWEEN 21 AND 30 THEN '21-30'
WHEN t3.customer_age BETWEEN 31 AND 40 THEN '31-40'
WHEN t3.customer_age BETWEEN 41 AND 50 THEN '41-50'
WHEN t3.customer_age BETWEEN 51 AND 60 THEN '51-60'
ELSE '61+'
END AS customer_age_group
FROM customers t3
JOIN (
-- 先计算产品总体统计数据
SELECT
t1.product_id,
t1.unit_price,
SUM(t2.quantity) AS total_quantity, -- 产品总购买量
SUM(t2.quantity * t1.unit_price) AS total_sales,
ROUND(SUM(t2.quantity * t1.unit_price) / 12, 2) AS avg_monthly_sales,
MAX(t2.quantity) AS max_monthly_quantity,
-- 再找出每个产品购买量最大的客户
(
SELECT customer_id
FROM (
SELECT
customer_id,
SUM(quantity) as cust_total_qty,
RANK() OVER (ORDER BY SUM(quantity) DESC) as rnk
FROM orders
WHERE product_id = t1.product_id
GROUP BY customer_id
) AS top_cust
WHERE rnk = 1
LIMIT 1
) AS top_customer_id
FROM products t1
JOIN orders t2 ON t1.product_id = t2.product_id
GROUP BY t1.product_id, t1.unit_price -- 只按产品分组，得到产品总量
) AS prod_stats ON t3.customer_id = prod_stats.top_customer_id
ORDER BY prod_stats.total_sales DESC

发表于 2025-12-12 11:28:52 回复(0)

提交观点

问题信息

SQL

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

难度：

182条回答 698收藏 1602浏览

统计每个产品的销售情况

查询要求

排序规则

计算说明

示例说明

输入

输出

问题信息

热门推荐

相关试题