sql - 如何从 Postgres 中的其他两行生成新行？

Question

我在 Postgres 表中有一些数据，如下所示:

1 apple datetime1
2 orange datetime2
3 apple datetime3
4 orange datetime4
5 apple datetime5
6 orange datetime6
.

日期时间总是按升序排列，大多数情况下首先插入苹果行，然后插入橙色行，但有一些异常(exception)情况我必须捕捉并消除。

我实际需要的是一个只配对苹果和橙子的 Postgres 查询:

1 apple datetime1 2 orange datetime2
3 apple datetime3 4 orange datetime4
5 apple datetime5 6 orange datetime6

苹果不应与其他苹果搭配，橙子不应与其他橙子搭配。

有几个条件:

1) 在新生成的行中，apple 应该总是第一，orange 第二。

2) 始终将 apple 和 orange 行与最接近的日期时间配对并忽略其他行。

例如，如果我有这样的原始数据:

1 apple datetime1
2 apple datetime2
3 orange datetime3
4 orange datetime4

一对

2 apple datetime2 3 orange datetime3

并忽略行

1 apple datetime1
4 orange datetime4

知道如何在 Postgres 中执行此操作吗？

Answer 1

The datetime is always in ascending order and the majority of times the apple rows are inserted first and orange second with some exceptions that I have to catch and eliminate.

如果我没理解错的话，你想找是否有任意两行连续的水果是一样的，对吧？

如果是这样，你可以这样做:

WITH Q AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY datetime) R
    FROM YOUR_TABLE
)
SELECT *
FROM Q Q1 JOIN Q Q2 ON Q1.R = Q2.R - 1
WHERE Q1.fruit = Q2.fruit;

用简单的英语来说:将每一行与下一行连接起来(其中“下一个”的含义由 datetime 顺序决定)并检查它们的结果是否匹配。你可以在这个 SQL Fiddle 中使用它.

顺便说一句，这不是一个好的数据库设计。您应该考虑重新设计，这样您上面描述的“无效”情况自然会被数据库结构本身阻止。