sql - Oracle SQL 中的 FIRST 和 LAST 值

Question

我在查询某些数据时遇到问题。我试图从中提取数据的表是一个 LOG 表，我想在其中查看彼此相邻的值的变化（下面的示例）

桌子：

+-----------+----+-------------+----------+------- -----+
| UNIQUE_ID | 身份证 | 姓名 | 城市 | 日期 |
+-----------+----+-------------+----------+------- -----+
| xa220 | 1 | 约翰·史密斯 | 柏林 | 2020.05.01 |
| xa195 | 1 | 约翰·史密斯 | 柏林 | 2020.03.01 |
| xa111 | 1 | 约翰·史密斯 | 慕尼黑 | 2020.01.01 |
| xa106 | 2 | 詹姆斯布朗 | 亚特兰大 | 2018.04.04 |
| xa100 | 2 | 詹姆斯布朗 | 波士顿 | 2017.12.10 |
| xa76 | 3 | 艾米莉狼 | 上海 | 2016.11.03 |
| xa20 | 3 | 艾米莉狼 | 上海 | 2016.07.03 |
| xa15 | 3 | 艾米莉狼 | 东京 | 2014.02.22 |
| xa12 | 3 | 艾米莉狼 | 空 | 2014.02.22 |
+-----------+----+-------------+----------+------- -----+

期望的结果：

+----+-------------+----------+----------------+
| 身份证 | 姓名 | 城市 | PREVIOUS_CITY |
+----+-------------+----------+----------------+
| 1 | 约翰·史密斯 | 柏林 | 慕尼黑 |
| 2 | 詹姆斯布朗 | 亚特兰大 | 波士顿 |
| 3 | 艾米莉狼 | 上海 | 东京 |
| 3 | 艾米莉狼 | 东京 | 空 |
+----+-------------+----------+----------------+

我一直在尝试使用 FIRST 和 LAST 值，但是无法获得预期的结果。

选择不同的 id，
姓名，
城市，
first_value(city) over (partition by id order by city) as previous_city
从测试

任何帮助表示赞赏！谢谢！

标签： sqloraclewindow-functions

Answer 1

使用LAG函数获取上一个日期的城市，只显示当前城市和滞后结果不同的行：

WITH cte AS (
    SELECT t.*, LAG(CITY, 1, CITY) OVER (PARTITION BY ID ORDER BY "DATE") LAG_CITY
    FROM yourTable t
)

SELECT ID, NAME, CITY, LAG_CITY AS PREVIOUS_CITY
FROM cte
WHERE
    CITY <> LAG_CITY OR
    CITY IS NULL AND LAG_CITY IS NOT NULL OR
    CITY IS NOT NULL AND LAG_CITY IS NULL
ORDER BY
    ID, "DATE" DESC;

演示

LAG关于如何使用和检查其值的一些评论是有保证的。我们在这里使用三参数版本LAG。第二个参数表示要回顾的记录数，在本例中为 1（默认值）。第三个参数表示如果每个ID分区的给定记录是第一个则使用的默认值。在这种情况下，我们使用默认值作为相同的CITY值。这意味着第一条记录永远不会出现在结果集中。

对于上述WHERE子句，匹配记录是 city 和 lag city 不同的记录，或者两者之一是NULL而另一个不是的记录NULL。这是将一个NULL城市和一些非NULL城市价值视为不同所需的逻辑。