快捷導(dǎo)航

Oracle查詢實例之訂單金額占比與排名分析

更新時間：2025年11月06日 09:57:43 作者：A耿耿

這篇文章主要介紹了Oracle查詢實例之訂單金額占比與排名分析的相關(guān)資料,文中通過代碼介紹了兩種方法來查詢每個客戶在每個日期的訂單金額、占比以及排名,需要的朋友可以參考下

題目

假設(shè)有一張表格 orders，記錄了不同日期的訂單記錄，包括訂單號（order_id）、訂單日期（order_date）、客戶 ID（customer_id）、商品 ID（product_id）、商品數(shù)量（quantity）、商品價格（price）。請編寫SQL 查詢語句，查詢出每個客戶在每個日期的訂單金額和該客戶在當(dāng)天的訂單金額占比（百分比）以及該客戶在當(dāng)天的訂單金額占比排名。

建表語句

-- 建表
-- 創(chuàng)建訂單表 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,          -- 商品數(shù)量
    price NUMBER(10,2) NOT NULL           -- 商品單價
);

-- 插入數(shù)據(jù)
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. 計算每個客戶每天的訂單金額占比

使用上一步的結(jié)果：

占比 = 客戶當(dāng)天訂單金額 / 當(dāng)天總訂單金額

4. 計算每個客戶在當(dāng)天的訂單金額占比排名

按 order_date 分組
在每個分組內(nèi)，按 訂單金額占比 降序排名（使用 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) 當(dāng)天訂單總金額
    from t1
),
t3 as(
    select
        order_date,
        customer_id,
        用戶訂單金額,
        round(用戶訂單金額/當(dāng)天訂單總金額,2) 當(dāng)天訂單金額占比
    from t2
)
select
    order_date,
    customer_id,
    用戶訂單金額,
    當(dāng)天訂單金額占比*100||'%' 占比,
    row_number() over (partition by order_date order by 當(dāng)天訂單金額占比) 排序
from t3

思路二：

1.基礎(chǔ)數(shù)據(jù)分組聚合

目的：計算每個客戶在每個日期的總訂單金額

按 order_date 和 customer_id 分組
對每個分組計算：SUM(price * quantity)

2.計算當(dāng)日訂單金額占比

關(guān)鍵技巧：窗口函數(shù)中的聚合函數(shù)嵌套

SUM(SUM(quantity * price)) over(partition by order_date) 的含義：
- 內(nèi)層 SUM(quantity * price)：每個客戶當(dāng)天的訂單金額
- 外層 SUM(...) over(...)：對所有這些客戶金額按日期求和，得到當(dāng)天總金額
- 相當(dāng)于：客戶當(dāng)天金額 / 當(dāng)天所有客戶總金額

3.計算當(dāng)日排名

排名邏輯：

partition by order_date：在每個日期內(nèi)獨立排名
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 當(dāng)日訂單金額占比
    ,rank() over (partition by order_date order by sum(price * quantity)desc) as 當(dāng)日訂單金額排名
FROM orders
group by order_date,customer_id
order by order_date,customer_id