文章/答案/技术大牛

发布

社区首页 >问答首页 >选择与PostgreSQL匹配条件的最新连续记录

问选择与PostgreSQL匹配条件的最新连续记录
EN

Stack Overflow用户

提问于 2018-05-16 15:02:46

回答 2查看 2.2K关注 0票数 6

我正在寻找一个PostgreSQL查询，以查找与条件匹配的最新连续记录。让我用一个例子更好地解释一下：

|  ID  |  HEATING STATE  |  DATE       |
| ---- | --------------- |  ---------- |
|  1   |  ON             |  2018-02-19 |
|  2   |  ON             |  2018-02-20 |
|  3   |  OFF            |  2018-02-20 |
|  4   |  OFF            |  2018-02-21 |
|  5   |  ON             |  2018-02-21 |
|  6   |  OFF            |  2018-02-21 |
|  7   |  ON             |  2018-02-22 |
|  8   |  ON             |  2018-02-22 |
|  9   |  ON             |  2018-02-22 |
| 10   |  ON             |  2018-02-23 |

我需要找到最近所有的连续记录，日期为>= 2018-02-20和heating_state ON，即ID 7、8、9、10的记录。我的主要问题是，它们必须是连续的。

如有必要，请进一步澄清：

ID 1被排除在外，因为年龄大于2018-02-20。
ID 2被排除在外，因为后面跟着具有关闭加热状态的ID 3。
ID 3被排除在外，因为它已经关闭了加热状态。
ID 4被排除在外，因为它后面跟着ID 5，它已经加热了。
ID 5被排除在外，因为它已经关闭了加热状态。
ID 6被排除在外，因为它处于关闭的加热状态。

sql

postgresql

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-05-16 17:46:25

我认为最好使用windows函数和过滤聚合来解决这个问题。

对于每一行，添加具有state = 'OFF'的后行数，然后只使用计数为0的行。

您需要一个子查询，因为您不能在WHERE条件下使用窗口函数结果(WHERE在窗口函数之前进行计算)。

SELECT id, state, date
FROM (SELECT id, state, date,
             count(*) FILTER (WHERE state = 'OFF')
                OVER (ORDER BY date DESC, state DESC) AS later_off_count
      FROM tab) q
WHERE later_off_count = 0;

 id | state |    date    
----+-------+------------
 10 | ON    | 2018-02-23
  9 | ON    | 2018-02-22
  8 | ON    | 2018-02-22
  7 | ON    | 2018-02-22
(4 rows)

票数 6

Stack Overflow用户

发布于 2018-05-16 15:16:30

将LEAD函数与CASE表达式一起使用。

SQL Fiddle

查询1

SELECT id, 
       heating_state, 
       dt 
FROM   (SELECT t.*, 
               CASE 
                 WHEN dt >= timestamp '2018-02-20' 
                      AND heating_state = 'ON' 
                      AND LEAD(heating_state, 1, heating_state) 
                            OVER ( 
                                  ORDER BY dt ) = 'ON' THEN 1 
                 ELSE 0 
               END on_state 
        FROM   t) s 
WHERE  on_state = 1

结果

| id | heating_state |                   dt |
|----|---------------|----------------------|
|  7 |            ON | 2018-02-22T00:00:00Z |
|  8 |            ON | 2018-02-22T00:00:00Z |
|  9 |            ON | 2018-02-22T00:00:00Z |
| 10 |            ON | 2018-02-23T00:00:00Z |

票数 4

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/50374288

复制

相似问题

问选择与PostgreSQL匹配条件的最新连续记录
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择与PostgreSQL匹配条件的最新连续记录EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择与PostgreSQL匹配条件的最新连续记录
EN