sql - 在SQL中如何根据当前行值选择前一行？

我有一个简单的 SQL 表，看起来像这样-

CREATE TABLE msg (
    from_person character varying(10),
    from_location character varying(10),
    to_person character varying(10),
    to_location character varying(10),
    msglength integer,
    ts timestamp without time zone
);

我想找出表中的每一行在过去 3 分钟内是否有不同的“from_person”和“from_location”与当前行中的“to_person”进行了交互。

例如，在上表中，对于第 4 行，除了来自孟买的 Mary(当前行)之外，来自 NYC 的 nancy 和来自巴塞罗那的 bob 也在过去 3 分钟内向 charlie 发送了消息，因此计数为 2。

同样，对于第 2 行，除了来自巴塞罗那的 bob(当前行)之外，只有来自纽约的 nancy 向 ca(当前行)中的 charlie 发送了消息，因此计数为 1

示例所需的输出 -

0
1
0
2

我尝试使用窗口函数，但似乎在框架子句中我可以指定前后的行数，但我不能指定时间本身。

最佳答案

众所周知，Postgres 中的每个表都有一个主键。或者至少应该有。如果您有一个定义预期行顺序的主键，那就太好了。

示例数据:

create table msg (
    id int primary key,
    from_person text,
    to_person text,
    ts timestamp without time zone
);

insert into msg values
(1, 'nancy',   'charlie', '2016-02-01 01:00:00'),
(2, 'bob',     'charlie', '2016-02-01 01:00:00'),
(3, 'charlie', 'nancy',   '2016-02-01 01:00:01'),
(4, 'mary',    'charlie', '2016-02-01 01:02:00');

查询:

select m1.id, count(m2)
from msg m1
left join msg m2
on m2.id < m1.id
and m2.to_person = m1.to_person
and m2.ts >= m1.ts- '3m'::interval
group by 1
order by 1;

 id | count
----+-------
  1 |     0
  2 |     1
  3 |     0
  4 |     2
(4 rows)

在缺少主键的情况下，您可以使用函数 row_number() ，例如:

with msg_with_rn as (
    select *, row_number() over (order by ts, from_person desc) rn
    from msg
    )
select m1.id, count(m2)
from msg_with_rn m1
left join msg_with_rn m2
on m2.rn < m1.rn
and m2.to_person = m1.to_person
and m2.ts >= m1.ts- '3m'::interval
group by 1
order by 1;

请注意，我已使用 row_number() over (order by ts, from_person desc) 来获取您在问题中提出的行序列。当然，您应该自己决定如何解决由 ts 列的相同值引起的歧义(如前两行)。

关于sql - 在SQL中如何根据当前行值选择前一行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35232556/