用于按顺序提取记录的 SQL 查询

Question

PostgreSQL 9.4

我想根据日期提取顺序为 ON > WAIT > OFF 的记录。在这种情况下，结果应该只返回键为 1 的记录。

表格

    key  state  date
    1    ON     1/1/2018
    1    WAIT   1/2/2018
    2    ON     1/1/2018
    2    OFF    1/2/2018
    1    OFF    1/3/2018

Answer 1

您可以使用 window functions例如 LEAD 和 LAG 在这种情况下:

SELECT  "key"
FROM
        (
            SELECT  "key", "state", "date",
                    LAG("state") OVER (PARTITION BY "key" ORDER BY date ASC) LAG_STATE,
                    LEAD("state") OVER (PARTITION BY "key" ORDER BY date ASC) LEAD_STATE
            FROM    Table1
        ) AS a
WHERE   LAG_STATE = 'ON'
        AND state = 'WAIT'
        AND LEAD_STATE = 'OFF'

这是一个 DEMO .