快捷導(dǎo)航

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

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

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

題目

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

建表語(yǔ)句

-- 建表
-- 創(chuàng)建訂單表 ORDERS
CREATE TABLE ORDERS (
    order_id NUMBER PRIMARY KEY,          -- 訂單編號(hào)，主鍵
    customer_id NUMBER NOT NULL,          -- 客戶編號(hào)
    order_date DATE NOT NULL,             -- 訂單日期
    product_id NUMBER NOT NULL,           -- 商品編號(hào)
    quantity NUMBER(5) NOT NULL,          -- 商品數(shù)量
    price NUMBER(10,2) NOT NULL           -- 商品單價(jià)
);

-- 插入數(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. 計(jì)算每個(gè)客戶每天的訂單金額

按 order_date 和 customer_id 分組
對(duì)每個(gè)分組計(jì)算：SUM(quantity * price)

2. 計(jì)算每天的總訂單金額

按 order_date 分組
對(duì)每個(gè)分組計(jì)算：SUM(quantity * price)

3. 計(jì)算每個(gè)客戶每天的訂單金額占比

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

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

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

按 order_date 分組
在每個(gè)分組內(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ù)分組聚合

目的：計(jì)算每個(gè)客戶在每個(gè)日期的總訂單金額

按 order_date 和 customer_id 分組
對(duì)每個(gè)分組計(jì)算：SUM(price * quantity)

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

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

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

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

排名邏輯：

partition by order_date：在每個(gè)日期內(nèi)獨(dú)立排名
order by sum(price * quantity) desc：按訂單金額降序排列
使用 RANK()：允許并列排名（如兩個(gè)客戶金額相同則排名相同）

最終代碼

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