Oracle查询实例之订单金额占比与排名分析_Oracle

题目

假设有一张表格 orders，记录了不同日期的订单记录，包括订单号（order_id）、订单日期（order_date）、客户 id（customer_id）、商品 id（product_id）、商品数量（quantity）、商品价格（price）。请编写sql 查询语句，查询出每个客户在每个日期的订单金额和该客户在当天的订单金额占比（百分比）以及该客户在当天的订单金额占比排名。

建表语句

-- 建表
-- 创建订单表 orders
create table orders (
    order_id number primary key,          -- 订单编号，主键
    customer_id number not null,          -- 客户编号
    order_date date not null,             -- 订单日期
    product_id number not null,           -- 商品编号
    quantity number(5) not null,          -- 商品数量
    price number(10,2) not null           -- 商品单价
);

-- 插入数据
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (1, 101, to_date('2024-04-01', 'yyyy-mm-dd'), 1, 2, 50.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (2, 101, to_date('2024-04-01', 'yyyy-mm-dd'), 2, 1, 100.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (3, 102, to_date('2024-04-01', 'yyyy-mm-dd'), 3, 3, 30.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (4, 103, to_date('2024-04-01', 'yyyy-mm-dd'), 1, 1, 50.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (5, 101, to_date('2024-04-02', 'yyyy-mm-dd'), 2, 2, 100.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (6, 102, to_date('2024-04-02', 'yyyy-mm-dd'), 3, 1, 30.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (7, 103, to_date('2024-04-02', 'yyyy-mm-dd'), 1, 2, 50.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (8, 104, to_date('2024-04-02', 'yyyy-mm-dd'), 2, 1, 100.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (9, 101, to_date('2024-04-03', 'yyyy-mm-dd'), 1, 3, 50.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (10, 102, to_date('2024-04-03', 'yyyy-mm-dd'), 2, 2, 100.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (11, 103, to_date('2024-04-03', 'yyyy-mm-dd'), 3, 1, 30.00);
insert into orders (order_id, customer_id, order_date, product_id, quantity, price) values (12, 104, to_date('2024-04-03', 'yyyy-mm-dd'), 1, 1, 50.00);

思路一：

1. 计算每个客户每天的订单金额

按 order_date 和 customer_id 分组
对每个分组计算：sum(quantity * price)

2. 计算每天的总订单金额

按 order_date 分组
对每个分组计算：sum(quantity * price)

3. 计算每个客户每天的订单金额占比

使用上一步的结果：

占比 = 客户当天订单金额 / 当天总订单金额

4. 计算每个客户在当天的订单金额占比排名

按 order_date 分组
在每个分组内，按 订单金额占比 降序排名（使用 row_number() 或 rank()）

图片分析

最终代码

with t1 as (
    select
        distinct
        order_date,
        customer_id,
        sum(price * quantity)over(partition by order_date, customer_id ) 用户订单金额
    from orders
),
t2 as (
    select
        order_date,
        customer_id,
        用户订单金额,
        sum(用户订单金额) over (partition by order_date) 当天订单总金额
    from t1
),
t3 as(
    select
        order_date,
        customer_id,
        用户订单金额,
        round(用户订单金额/当天订单总金额,2) 当天订单金额占比
    from t2
)
select
    order_date,
    customer_id,
    用户订单金额,
    当天订单金额占比*100||'%' 占比,
    row_number() over (partition by order_date order by 当天订单金额占比) 排序
from t3

思路二：

1.基础数据分组聚合

目的：计算每个客户在每个日期的总订单金额

按 order_date 和 customer_id 分组
对每个分组计算：sum(price * quantity)

2.计算当日订单金额占比

关键技巧：窗口函数中的聚合函数嵌套

sum(sum(quantity * price)) over(partition by order_date) 的含义：
- 内层 sum(quantity * price)：每个客户当天的订单金额
- 外层 sum(...) over(...)：对所有这些客户金额按日期求和，得到当天总金额
- 相当于：客户当天金额 / 当天所有客户总金额

3.计算当日排名

排名逻辑：

partition by order_date：在每个日期内独立排名
order by sum(price * quantity) desc：按订单金额降序排列
使用 rank()：允许并列排名（如两个客户金额相同则排名相同）

最终代码

select
    order_date as 交易日期
    ,customer_id as 客户id
    ,sum(price * quantity) as 订单金额
    ,round(sum(price * quantity) / sum(sum(quantity * price)) over(partition by order_date),2) as 当日订单金额占比
    ,rank() over (partition by order_date order by sum(price * quantity)desc) as 当日订单金额排名
from orders
group by order_date,customer_id
order by order_date,customer_id