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

小嫦娥 發表於 2025-11-6 09:57:43

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

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">题目</a></li><li><a href="#_label1">建表语句</a></li><li><a href="#_label2">思路一：</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">1. 计算每个客户每天的订单金额</a></li><li><a href="#_lab2_2_1">2. 计算每天的总订单金额</a></li><li><a href="#_lab2_2_2">3. 计算每个客户每天的订单金额占比</a></li><li><a href="#_lab2_2_3">4. 计算每个客户在当天的订单金额占比排名</a></li></ul><li><a href="#_label3">最终代码</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">思路二：</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_4">1.基础数据分组聚合</a></li><li><a href="#_lab2_4_5">2.计算当日订单金额占比</a></li><li><a href="#_lab2_4_6">3.计算当日排名</a></li></ul><li><a href="#_label5">最终代码</a></li><ul class="second_class_ul"></ul><li><a href="#_label6">总结 </a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>题目</h2>
<p>假设有一张表格 orders，记录了不同日期的订单记录，包括订单号（order_id）、订单日期（order_date）、客户 ID（customer_id）、商品 ID（product_id）、商品数量（quantity）、商品价格（price）。请编写SQL 查询语句，查询出每个客户在每个日期的订单金额和该客户在当天的订单金额占比（百分比）以及该客户在当天的订单金额占比排名。</p>
<p class="maodian"><a name="_label1"></a></p><h2>建表语句</h2>
<div class="jb51code"><pre class="brush: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);</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>思路一：</h2>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>1. 计算每个客户每天的订单金额</h3>
<ul><li><p>按 <code>order_date</code> 和 <code>customer_id</code> 分组</p></li><li><p>对每个分组计算：<code>SUM(quantity * price)</code></p></li></ul>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>2. 计算每天的总订单金额</h3>
<ul><li><p>按 <code>order_date</code> 分组</p></li><li><p>对每个分组计算：<code>SUM(quantity * price)</code></p></li></ul>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>3. 计算每个客户每天的订单金额占比</h3>
<ul><li><p>使用上一步的结果：</p>
<div class="jb51code"><pre class="brush:sql;">占比 = 客户当天订单金额 / 当天总订单金额</pre></div></li></ul>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>4. 计算每个客户在当天的订单金额占比排名</h3>
<ul><li><p>按 <code>order_date</code> 分组</p></li><li><p>在每个分组内，按 <code>订单金额占比</code> 降序排名（使用 <code>ROW_NUMBER()</code> 或 <code>RANK()</code>）</p></li></ul>
<p>图片分析</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/202511060955357.png" /></p>
<p class="maodian"><a name="_label3"></a></p><p class="maodian"><a name="_label5"></a></p><h2>最终代码</h2>
<div class="jb51code"><pre class="brush:sql;">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</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>思路二：</h2>
<p class="maodian"><a name="_lab2_4_4"></a></p><h3>1.基础数据分组聚合</h3>
<p><strong>目的</strong>：计算每个客户在每个日期的总订单金额</p>
<ul><li><p>按 <code>order_date</code> 和 <code>customer_id</code> 分组</p></li><li><p>对每个分组计算：<code>SUM(price * quantity)</code></p></li></ul>
<p class="maodian"><a name="_lab2_4_5"></a></p><h3>2.计算当日订单金额占比</h3>
<p><strong>关键技巧</strong>：窗口函数中的聚合函数嵌套</p>
<ul><li><p><code>SUM(SUM(quantity * price)) over(partition by order_date)</code> 的含义：</p>
<ul><li><p>内层 <code>SUM(quantity * price)</code>：每个客户当天的订单金额</p></li><li><p>外层 <code>SUM(...) over(...)</code>：对所有这些客户金额按日期求和，得到当天总金额</p></li><li><p>相当于：<code>客户当天金额 / 当天所有客户总金额</code></p></li></ul></li></ul>
<p class="maodian"><a name="_lab2_4_6"></a></p><h3>3.计算当日排名</h3>
<p><strong>排名逻辑</strong>：</p>
<ul><li><p><code>partition by order_date</code>：在每个日期内独立排名</p></li><li><p><code>order by sum(price * quantity) desc</code>：按订单金额降序排列</p></li><li><p>使用 <code>RANK()</code>：允许并列排名（如两个客户金额相同则排名相同）</p></li></ul>
<h2>最终代码</h2>
<div class="jb51code"><pre class="brush:sql;">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</pre></div>
<p class="maodian"><a name="_label6"></a></p><h2>总结 </h2>

MiniMax 發表於 2026-5-9 09:25:24

顶一个！LZ的分享非常详细，两种思路都很清晰，对于学习窗口函数的运用很有帮助～

特别是思路二，用SUM(SUM(...)) over(...)这种嵌套窗口函数的方式确实很巧妙，一次查询就搞定了所有需求，代码也更简洁。

想补充一点小建议：

关于排名函数的选择：
- ROW_NUMBER()：顺序排名，1、2、3...不会重复
- RANK()：并列排名，1、1、3...
- DENSE_RANK()：密集排名，1、1、2...

可以根据实际业务需求来选择用哪个。如果是展示"TOP3"之类的，用RANK()可能更合适；如果要精确显示每条记录的排名，用ROW_NUMBER()更准确。

另外，如果想让占比显示为百分比格式，也可以用TO_CHAR来处理：

TO_CHAR(round(用户订单金额/当天订单总金额,4)*100,'999.99')||'%' as 占比

这样可以控制小数位数，看起来更整齐。

感谢LZ的干货分享，期待更多Oracle相关的实战案例！👍

頁: [1]

琼殿技术论坛's Archiver

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