使用 LAG() 和 PARTITION BY 在日期的 10 天内返回行

debugcn 发表于 Dev

猴子在Arock

我有下表称为fruits：

id  fruit_bought   quantity   date
1 | orange       | 100      | 2018-01-10
2 | apple        | 50       | 2018-02-05
3 | orange       | 75       | 2018-03-07
4 | orange       | 200      | 2018-03-15
5 | apple        | 10       | 2018-03-17
6 | orange       | 20       | 2018-03-20

我想返回有行fruit_bought的orange ，如果在过去10天内的任何时间任何橘子被买了，开始日期2018年3月20日（一行id6）。

例如：

从开始2018-03-20，在此日期购买了橙子（行id 6）
在此之前 10 天是否有任何橙色购买？是：在“2018-03-15”（行id 4）
从该日期起 10 天前是否有任何橙色购买？是：在“2018-03-07”（行id 3）
从该日期起 10 天前是否有任何橙色购买？不。

最后，我尝试创建的查询将返回id3、4 和 6（但不是 1）的行。

到目前为止，我的查询如下：

SELECT *, LAG(date, 1) OVER (PARTITION BY fruit_bought) FROM fruits
WHERE fruit_bought = 'orange';

这将返回is 的每一行，并添加一个额外的列。fruit_boughtorangelag

忘了它

这个答案基于Gordon Linoff 的想法，但有一些调整：

FILTER 没有在 Postgresql 11（目前）中为像 Lead() 或 lag() 这样的纯窗口函数实现。所以WHERE fruit_bought='orange'用作整个内部 SELECT 的条件。
要保证选择具有最后日期的行，请使用LEAD(date, 1, '-infinity'). 这使得默认值next_date等于-infinity时间戳。因此date >= next_date - interval '10 day'最后日期为 TRUE。
让我们将 10 天内的行称为集群。要仅从最后一个集群中选择行，请计算一个累积总和，计算condFALSE 的次数（因为 FALSE 值将集群分开）：
```
SUM(CASE WHEN cond IS TRUE THEN 0 ELSE 1 END) OVER (ORDER BY date DESC) AS cluster_num
```
并仅选择 cluster_num 等于 0 的行。由于我们ORDER BY date DESC，第 0 个集群是最后一个集群。

SELECT *
FROM (
    SELECT *, SUM(CASE WHEN cond IS TRUE THEN 0 ELSE 1 END) OVER (ORDER BY date DESC) AS cluster_num
    FROM (
        SELECT *, date >= next_date - interval '10 day' AS cond
        FROM (
            SELECT id, fruit_bought, date, 
                LEAD(date, 1, '-infinity') 
                OVER (PARTITION BY fruit_bought ORDER BY date) AS next_date 
            FROM fruits 
            WHERE fruit_bought='orange'
            -- restrict date here to specify an "initial date"
            AND date <= '2018-04-01'  
        ) t1
    ) t2
) t3
WHERE cond AND cluster_num = 0
ORDER BY date ASC

产量

| id | fruit_bought |       date |  next_date | cond | cluster_num |
|----+--------------+------------+------------+------+-------------|
|  3 | orange       | 2018-03-07 | 2018-03-15 | t    |           0 |
|  4 | orange       | 2018-03-15 | 2018-03-20 | t    |           0 |
|  6 | orange       | 2018-03-20 |  -infinity | t    |           0 |

设置：

CREATE TABLE fruits (
    fruitid INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id INT,
    fruit_bought TEXT,
    quantity INT,
    date DATE);

INSERT INTO fruits (id, fruit_bought, quantity, date)
VALUES (1,'orange',100,'2018-01-10')
, (2,'apple',50,'2018-02-05')
, (3,'orange',75,'2018-03-07')
, (4,'orange',200,'2018-03-15')
, (5,'apple',10,'2018-03-17')
, (6,'orange',20,'2018-03-20')
, (7,'orange',20,'2018-01-09');

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。