优化最高采购额商品查询及关联客户数据展示的技术问询

测试表结构与数据 #

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;

CREATE TABLE purchases(
    ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    CUSTOMER_ID NUMBER, 
    PRODUCT_ID NUMBER, 
    QUANTITY NUMBER, 
    PURCHASE_DATE TIMESTAMP
);

INSERT INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) 
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 5;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

ALTER TABLE purchases 
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);

问题1：不使用两个CTE且不显示rnk列，重写查询 #

注意：原查询的CTE中GROUP BY包含p.quantity是错误的，会导致同一商品按采购数量拆分分组，无法正确统计总采购量和金额。正确分组应仅按商品维度（i.product_id, i.product_name, i.price）。

优化方案1：子查询内嵌窗口函数 #

SELECT product_id, product_name, price, total_qty, total_amt
FROM (
    SELECT
        i.product_id,
        i.product_name,
        i.price,
        SUM(p.quantity) AS total_qty,
        SUM(p.quantity * i.price) AS total_amt,
        RANK() OVER (ORDER BY SUM(p.quantity * i.price) DESC) AS rnk
    FROM purchases p
    JOIN customers c ON p.customer_id = c.customer_id
    JOIN items i ON p.product_id = i.product_id
    GROUP BY i.product_id, i.product_name, i.price
)
WHERE rnk = 1;

将聚合计算与窗口函数合并到单个子查询中，外层过滤掉排名非1的行，不输出rnk列，无需额外CTE。

优化方案2：使用FETCH FIRST ... WITH TIES（Oracle 12c+） #

SELECT
    i.product_id,
    i.product_name,
    i.price,
    SUM(p.quantity) AS total_qty,
    SUM(p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
GROUP BY i.product_id, i.product_name, i.price
ORDER BY SUM(p.quantity * i.price) DESC
FETCH FIRST 1 ROWS WITH TIES;

这是更简洁高效的写法，WITH TIES会自动返回所有总金额并列最高的商品，无需窗口函数和子查询。

问题2：展示高金额商品的客户采购明细（预期3行） #

需要先定位总金额最高的商品，再按客户分组统计该商品的采购数据，关联客户表获取信息：

SELECT
    i.product_id,
    i.product_name,
    i.price,
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(p.quantity) AS total_qty,
    SUM(p.quantity * i.price) AS total_amt
FROM purchases p
JOIN customers c ON p.customer_id = c.customer_id
JOIN items i ON p.product_id = i.product_id
WHERE i.product_id = (
    -- 子查询获取总金额最高的商品ID
    SELECT product_id
    FROM (
        SELECT
            i.product_id,
            RANK() OVER (ORDER BY SUM(p.quantity * i.price) DESC) AS rnk
        FROM purchases p
        JOIN items i ON p.product_id = i.product_id
        GROUP BY i.product_id
    )
    WHERE rnk = 1
)
GROUP BY i.product_id, i.product_name, i.price, c.customer_id, c.first_name, c.last_name
ORDER BY c.customer_id;

子查询先筛选出总金额最高的商品ID，外层查询聚焦该商品的采购记录，按客户分组统计，返回3个购买过该商品的客户明细。

内容的提问来源于stack exchange，提问作者Pugzly