sql - 在第 2 期与第 1 期购买更多商品的某一类别中的独家客户

Question

我有两个表:产品和订单

CREATE TABLE product (
   product_id INTEGER,
   product_name CHARACTER VARYING(20),
   category_name CHARACTER VARYING(20)
);

CREATE TABLE  orders (
   order_id  INTEGER,
   date_id INTEGER,
   customer_id INTEGER,
   product_id INTEGER,
   sales INTEGER   
 ); 

这是一些示例数据:

INSERT INTO product VALUES (1,'widget','thingamabobs'); 
INSERT INTO product VALUES (2,'sprocket','thingamabobs'); 
INSERT INTO product VALUES (3,'gizmo','doodads'); 

-- 10 orders across 2 dates to 5 customers

INSERT INTO orders VALUES (1,1,1,1,2); 
INSERT INTO orders VALUES (1,1,1,3,2); 
INSERT INTO orders VALUES (2,1,2,2,3); 
INSERT INTO orders VALUES (3,1,3,1,1); 
INSERT INTO orders VALUES (4,1,4,1,2);
INSERT INTO orders VALUES (5,1,5,1,1);
INSERT INTO orders VALUES (6,2,1,1,3); 
INSERT INTO orders VALUES (6,2,1,3,2); 
INSERT INTO orders VALUES (7,2,2,2,3); 
INSERT INTO orders VALUES (8,2,3,1,1); 
INSERT INTO orders VALUES (8,2,3,2,1); 
INSERT INTO orders VALUES (9,2,4,1,1); 
INSERT INTO orders VALUES (10,2,5,1,2);

根据以上内容，我需要确定 thingamabob 客户，他们只购买了小部件，并且在最近一段时间内购买了比之前更多的东西。

我写了下面的查询:

WITH otherprod AS (SELECT DISTINCT customer_id
FROM orders o
INNER JOIN product p
ON o.product_id=p.product_id 
WHERE category_name='thingamabobs' AND o.product_id<>1 ),
prod AS (SELECT DISTINCT customer_id
FROM orders o
WHERE o.product_id=1 
),
solus AS (SELECT p.customer_id
FROM prod p
LEFT JOIN otherprod op
ON p.customer_id=op.customer_id
),
prodpp AS (SELECT customer_id,SUM(sales) AS ppsales
FROM orders o
WHERE o.product_id=1 
AND date_id<2
GROUP BY customer_id
),
prodtp AS (SELECT customer_id,SUM(sales) AS sales
FROM orders o
WHERE o.product_id=1 
AND date_id>1
GROUP BY customer_id
)
SELECT pp.customer_id
FROM prodpp pp
INNER JOIN prodtp tpp
ON pp.customer_id=tpp.customer_id
INNER JOIN solus s
ON pp.customer_id=s.customer_id
WHERE ppsales<sales;

返回正确的结果:

客户编号

       5
       1

(2 行)

但是有没有更高效的方法呢？

Answer 1

我想你可以这样写查询:

select customer_id,
       sum(case when date_id = 1 then sales else 0 end) as sales_1,
       sum(case when date_id = 2 then sales else 0 end) as sales_2
from (select o.*, p.*,
             min(p.product_name) over (partition by o.customer_id) as minpn,
             max(p.product_name) over (partition by o.customer_id) as maxpn
      from orders o join
           products p
           on o.product_id = p.product_id
      where p.category_name = 'thingamabobs'
     ) op
where minpn = maxpn and minpn = 'widget'
group by custoemr_id
having sales_2 > sales_1;

这是基于您的描述，而不是您的代码。

注意事项:

• 子查询计算“thingamabobs”组中每个客户的最小和最大产品。
• 当最小值和最大值相同并且它们具有您正在查看的值时，它们是相同的。
• 对于从日期到句点的转换，我不遵循日期逻辑。或许在外部 case 表达式中应该有更复杂的日期逻辑。