在SQL中如何根据当前行值选择先前的行？

众所周知，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（如前两行）引起的歧义。