主页 / dba / 问题 / 140475
Accepted
Eldamir
Asked: 2016-06-06 22:20:31 +0800 CST

选择行并添加它们之前的行

  • 772

我有一个表结构，其中包含另一个表的历史数据。就像是

id      | name        | date
----------------------------------------
1       | John        | 2016-01-01 14:38.123123
1       | John Smith  | 2016-01-03 16:46.123123
2       | ...         | 2016-01-01 14:38.123123
3       | ...         | 2016-01-01 14:38.123123

我想进行一个获取每一行的查询，对于每一行，还获取具有相同 ID 的上一行（行日期之前的最新日期），例如类似

SELECT id, name, date, previous.id, previous.name, previous.date
FROM ..?

这可能吗？这是一个非常笼统的问题，但在我的具体情况下，我使用的是 PostgreSQL，所以如果有一些 postgres 功能可以实现这一点，那很好。

postgresql

3 个回答

  1. Best Answer

    这正是LAG()窗口函数所做的，从 8.4 版开始可用：

    SELECT id, name, date, 
       LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name, 
       LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
FROM table_name ;

    还可以选择使用LATERAL连接（从 9.3 开始提供）。如果您想要所有列（上一行的）可能会更好。这假设对 有一个UNIQUE约束(id, date)：

    SELECT t.id, t.name, t.date, 
       p.name AS previous_name, p.date AS previous_date
FROM table_name AS t
  LEFT JOIN LATERAL
    ( SELECT p.*
      FROM table_name AS p
      WHERE p.id = t.id
        AND p.date < t.date
      ORDER BY p.date DESC
      LIMIT 1
    ) AS p ON TRUE ;

    索引(id, date)对这两个查询都有帮助。

    • 10

  2. 只需添加替代解决方案。

    在日期实际上是日期范围的情况下，可以使用 postgres 邻接运算符

       SELECT t1.id, t1.name, t1.date, t2.id, t2.name, t2.date
     FROM my_table t1
LEFT JOIN my_table t2
       ON t1.id = t2.id
      AND t2.date < t1.date
      AND t2.date -|- t1.date

    这读作“左加入同一个表，其中日期范围在此日期范围之前并直接与其相邻”

    这假设范围实际上是相邻的并且从不重叠，这就是我的具体实现的情况。

    • 2

  3. 我也会补充一点：自我加入ROW_NUMBER：

    With MyTable as (
  select 
    id,
    name,
    row_number() OVER (id ORDER BY date DESC) as RowNo 
  from table_name 
)

select 
  A.id,
  A.name as CurrentName,
  B.name as PreviousName,
  A.date as LastChangedOnDate,
  B.date as PreviouslyChangedOnDate
from MyTable A
left join MyTable B 
   on A.id = B.id and a.RowNo + 1 = B.RowNo
where A.RowNo = 1
    • 1

相关问题