postgresql - 自连接以查找 Postgres 中连续行之间的差异，尝试自连接但似乎不起作用

Question

我正在尝试使用自连接找出字段(在本例中为计数器)两个连续行之间的差异。表名是counter_table，主键是Country + State + City + dateandtime。 dateandtime 是一个时间戳字段，大约每 30 分钟插入一次数据，但有时在该间隔内根本没有插入数据，这意味着下一个数据集在 1 小时后到达，甚至更糟的是几个小时后到达，它可以变化。

我现在使用的查询如下

SELECT A.country, A.state, A.city, A.dateandtime, B.dateandtime, A.counter_1, B.counter_1, (B.counter_1 - A.counter_1), A.counter_2, B.counter_2, (B.counter_2 - A.counter_2)
FROM counter_table A, counter_table B
WHERE A.country = B.country
AND A.state = B.state
AND A.city = B.city
AND A.dateandtime > '2013-07-17 22:00:00'
AND B.dateandtime >= (A.dateandtime + interval '29 minutes')
AND B.dateandtime <= (A.dateandtime + interval '33 minutes')
ORDER BY 1,2,3,4;

结果集如下

Country         State   City    Dateandtime A   Dateandtime B   counter_1A  Counter_1B  1B-1A   Counter_2A  Counter_2B  2B-2AUnited States   Texas   Austin  7/17/2013 22:00 7/17/2013 22:30 1814166 1814291 125 1762331 1762454 123United States   Texas   Austin  7/17/2013 22:30 7/17/2013 23:00 1814291 1814389 98  1762454 1762548 94United States   Texas   Austin  7/17/2013 23:00 7/17/2013 23:30 1814389 1814489 100 1762548 1762640 92United States   Texas   Austin  7/18/2013 0:30  7/18/2013 1:00  1814647 1814708 61  1762795 1762855 60United States   Texas   Austin  7/18/2013 1:00  7/18/2013 1:30  1814708 1814758 50  1762855 1762905 50United States   Texas   Austin  7/18/2013 1:30  7/18/2013 2:00  1814758 1814829 71  1762905 1762975 70United States   Texas   Austin  7/18/2013 2:00  7/18/2013 2:30  1814829 1814892 63  1762975 1763037 62United States   Texas   Austin  7/18/2013 2:30  7/18/2013 3:00  1814892 1814977 85  1763037 1763122 85United States   Texas   Austin  7/18/2013 3:00  7/18/2013 3:30  1814977 1815056 79  1763122 1763200 78United States   Texas   Austin  7/18/2013 3:30  7/18/2013 4:00  1815056 1815105 49  1763200 1763249 49

这显然不是正确的解决方案，在这种情况下缺少 23:30 和 00:00 的记录。一些 Postgres 专家可以提供帮助吗？窗口函数是解决此问题的好方法吗？

Answer 1

窗口函数在这里是个好主意。此外，您可能应该提供原始集合的示例，以便人们能够弄清楚为什么缺少 23:30 和 00:00。