添加每名客户的旧采购栏，以进行比较

Question

阅读一本关于Sqlite的书，并决定为了好玩而测试某些查询。

这一次让我思考。

你将如何返回的数目的连续买家谁得到了一个新的项目，价格低于他们以前的项目？

• 没有连接要求它的所有在同一表中。
• 相关表名：day：购买日期，customer，price

到目前为止，我已经找到了连续的客户，但无法检查他们以前的购买。

SELECT * FROM (SELECT * FROM ORDERS GROUP BY CUSTOMER HAVING COUNT(*)>1);

认为通过将每个客户的购买添加到相邻的一列中，由date_bought订购可能是进行实际的“每个连续客户”检查的唯一方法。

对于再现性，您可以使用以下方法：

CREATE TABLE orders (
    day DATE,
    price FLOAT
    item char
    customer char
);

INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-2 day'), 0.5,'food','Jenny');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-23 day'), 1,'food','Jenny');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-1 day'), 11,'food','Betty');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-22 day'), 7,'food','Betty');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-3 day'), 8,'food','Katy');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-4 day'), 10,'food','Mary');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-23 day'), 1,'food','Mary');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-1 day'), 2,'food','Anna');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-2 day'), 12,'food','Anna');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-3 day'), 8,'food','Anna');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-4 day'), 10,'food','Lisa');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-5 day'), 5,'food','Lisa');
INSERT INTO orders(day, price,item,customer) VALUES(date('now', 'localtime', '-8 day'), 12,'food','Jenny');

Answer 1

如果您只是想要计数，而不关心具体客户是谁，那么获取多次订购同一商品的客户数量的方法之一是使用与exists谓词相关的子查询：

select count(distinct customer) 
from orders o1
where exists (
    select customer 
    from orders o2 
    where o1.customer = o2.customer and 
          o1.item = o2.item and 
          o1.day > o2.day and 
          o1.price < o2.price
    );

这将返回不同客户的计数，其中存在具有相同项目的行，并在较早的一天返回更高的价格。

如果我明白你的问题，我想这就是你想要的。

Answer 2

我的自我加入方式会是这样的。

select your field
from orders o1 join orders o2 on o1.customer = o2.customer

where o2.price < o1.price
and o1.day = (select max(day)
from orders
where day < o2.day
and customer = o2.customer)

这是假设their previous item指的是他们最近的订单中的一个项目。如果their previous item引用以前的任何订单，则可以将子查询替换为：

and o1.day < o2.day

Answer 3

你可以用CTE。这将给你实际的细节。如果您只想要计数，只需将最后的SELECT *替换为SELECT COUNT(*)

with xxx(day,price,item,customer,previous_price,previous_date) as (
  select *,null,null from orders group by customer having min(day)
  union all
  select o.day,o.price,o.item,o.customer,x.price,x.day
    from orders o join xxx x using(customer)
    where o.price < x.price
      and o.day > x.day
  )
  select * from xxx
    where previous_price is not null;

如果与前一次购买相比较，那么这一备选方案：

with xxx(day,price,item,customer,previous_price,previous_date) as (
  select *,null,null from orders
  union all
  select o.day,o.price,o.item,o.customer,x.price,x.day
    from orders o join xxx x using(customer)
    where o.price < x.price
      and o.day > x.day
  )
  select * from xxx group by customer having max(previous_date);