题目
假设有一张表格 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
总结
到此这篇关于Oracle查询实例之订单金额占比与排名分析的文章就介绍到这了,更多相关Oracle订单金额占比与排名内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
