Oracle SQL查询优化：关联客户信息、含无购客户及计算末次购后天数

待实现需求 #

• 关联customers表，输出结果包含客户的first_name和last_name
• 必须包含无购买记录的customer_id=5，其购买相关字段（订单数、末次购买日期、最后订单ID、间隔天数）设为NULL
• 新增字段：计算客户末次购买日期至今的间隔天数（NUMBER类型）

测试环境搭建代码 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Mazzarone' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zanzone' FROM DUAL UNION ALL
SELECT 5, 'Sandy', 'Herring' FROM DUAL;

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

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;

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

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
);

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);

insert  into purchases (customer_id, product_id, quantity, purchase_date) 
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
          connect by level <= 23
union all
select 3, 100,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
          connect by level <= 60;

原尝试查询代码 #

SELECT
    customer_id, 
/*
    q.first_name,
    q.last_name,
*/
    Num_Orders,
     purchase_date AS    Last_Purchase_Date, 
order_id AS Last_Order_ID
FROM ( 
     SELECT p.*,
         COUNT( order_id ) OVER ( PARTITION BY customer_id ) AS Num_Orders,   
       ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY purchase_date DESC, 
order_id DESC ) AS rn 
   FROM purchases p
) q
/*
join purchases p on q.customer_id = p.customer_id  
*/
WHERE rn = 1
ORDER BY customer_id;

满足所有需求的最终查询代码 #

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    p.Num_Orders,
    p.Last_Purchase_Date,
    p.Last_Order_ID,
    -- 计算末次购买至今的天数，无购买记录则返回NULL
    CASE 
        WHEN p.Last_Purchase_Date IS NOT NULL 
        THEN TRUNC(SYSDATE) - TRUNC(p.Last_Purchase_Date) 
        ELSE NULL 
    END AS Days_Since_Last_Purchase
FROM customers c
LEFT JOIN (
    SELECT
        customer_id,
        COUNT(order_id) AS Num_Orders,
        MAX(purchase_date) KEEP (DENSE_RANK LAST ORDER BY purchase_date, order_id) AS Last_Purchase_Date,
        MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY purchase_date, order_id) AS Last_Order_ID
    FROM purchases
    GROUP BY customer_id
) p ON c.customer_id = p.customer_id
ORDER BY c.customer_id;

需求实现说明 #

1. 关联customers表并返回姓名：使用LEFT JOIN将customers表与聚合后的购买数据关联，直接选取first_name和last_name字段。
2. 包含无购买记录的客户：采用LEFT JOIN而非内连接，确保customers表中所有记录都被保留，无购买记录的客户（如customer_id=5）对应的购买字段自动为NULL。
3. 计算末次购买至今的天数：通过TRUNC(SYSDATE) - TRUNC(p.Last_Purchase_Date)计算日期差，用CASE处理无购买记录的情况返回NULL；TRUNC函数截断时间部分，保证天数计算的准确性。

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