如何有条件地获取SELECT中的上一行？

In this question there are several ways to visit previous row in a SELECT statement. However, I cannot figure out how to do it conditionally.

在这个问题中,有几种方法可以访问SELECT语句中的上一行。但是,我无法弄清楚如何有条件地做到这一点。

For example, suppose we have a Transactions table:

例如,假设我们有一个Transactions表:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-Grain
3            13 Apr         Rice              Jasmine

I'd like to find out how long a customer changed his/her mind after buying an item, expected output is:

我想知道顾客购买物品后多久改变了主意,预计产量是:

customer_id  merchandise_name  days
------------------------------------
1            Jasmine           6
1            Basmati           1

Customer 1 bought Jasmine rice then bought Basmati rice 6 days later, so "days" in the first record is 6. Following code is able to do this:

顾客1买了茉莉香米,然后6天后买了巴斯马蒂米,所以第一张纪录中的“天”是6.以下代码可以做到这一点:

select customer_id, merchandise_name,
       purchase_date - LAG(purchase_date) over (order by purchase_date) as days
from Transactions

However, it won't work when there are other types of merchandise:

但是,当有其他类型的商品时,它将无法运作:

customer_id  purchase_date  merchandise_type  merchandise_name
-----------------------------------------------------------------
1            12 Apr         Rice              Jasmine
1            13 Apr         Cafe              Maxwell
1            18 Apr         Rice              Basmati 
1            19 Apr         Rice              Long-grain
1            19 Apr         Cafe              Nescafe
3            13 Apr         Rice              Jasmine
3            14 Apr         Cafe              Nescafe

Is it possible to get a previous row with some condition? something like:

是否有可能获得前一行的某些条件?就像是:

...
order by purchase_date
where LAG(merchandise_type) = merchandise_type

1 个解决方案

#1

What you are looking for is the PARTITION BY clause in your OVER function:

你要找的是你的OVER函数中的PARTITION BY子句:

select customer_id, merchandise_name,
       purchase_date - 
         LAG(purchase_date) over (partition by customer_id, merchandise_type
                                  order by purchase_date) as days
from Transactions

Without this clause you will get any previous value for the purchase_date.

如果没有此子句,您将获得purchase_date的任何先前值。

#1