sql - 如何通过 id 和自定义类型的下一行进行选择？

Question

我有一张这样的 table : /image/qyyKD.png

id, external_id, type, timestamp
694534804,685112085,FASTLY,2019-03-13 15:51:51.272000
694534790,685112085,FASTLY,2019-03-13 14:14:43.814000
694534789,685112085,FASTLY,2019-03-13 14:11:32.138000
694534788,685112085,FASTLY,2019-03-13 14:10:54.681000
694534787,685112085,FASTLY,2019-03-13 14:10:39.444000
694534786,685112085,FASTLY,2019-03-13 14:10:21.359000
694534785,685112085,FASTLY,2019-03-13 14:10:20.849000
694527409,685112085,FASTLY,2019-03-13 13:22:05.733000
694527408,685112085,FASTLY,2019-03-13 13:20:29.546000
694527407,685112085,FASTLY,2019-03-13 13:20:13.528000
694527406,685112085,SLOWLY,2019-03-13 13:19:44.476000
694515187,685112085,FASTLY,2019-03-11 14:32:04.805000
694515186,685112085,FASTLY,2019-03-11 14:31:41.592000
694303908,685112085,FASTLY,2019-03-01 16:03:19.720000
694303907,685112085,FASTLY,2019-03-01 16:03:19.560000
694295217,685112085,FASTLY,2019-02-28 23:01:29.801000
694295216,685112085,FASTLY,2019-02-28 22:55:06.978000
686123194,685112085,FASTLY,2019-01-21 15:21:08.029000
685653586,685112085,FASTLY,2019-01-18 15:04:54.975000
685653585,685112085,SLOWLY,2019-01-18 15:04:46.627000
685108390,685112085,FASTLY,2019-01-16 00:08:46.439000
685108387,685112085,SLOWLY,2019-01-15 22:11:14.562000
694527391,1846930,FASTLY,2019-03-12 16:37:21.586000
694527390,1846930,FASTLY,2019-03-12 16:36:43.122000
694527389,1846930,FASTLY,2019-03-12 16:36:21.071000
693947087,1846930,SLOWLY,2019-02-05 10:22:21.698000
1846934,1846930,FASTLY,2018-12-05 20:48:29.100000
1846931,1846930,SLOWLY,2018-12-05 20:48:28.961000

我想得到一对值:输入 ID 和 ID 前面或等于他的类型 = SLOWLY 对于相同的 external_id。像这样:

select * from table where id in (694534804, 694527408, 694527406, 694515187, 685653585, 685108390, 685108387, 694527390, 693947087, 1846934, 1846931)  

id        | prev_or_eq_slowly_id
694534804 | 694527406
694527408 | 694527406
694527406 | 694527406
694515187 | 685653585
685653585 | 685653585
694527390 | 693947087 
693947087 | 693947087
1846934   | 1846931
1846931   | 1846931

Answer 1

要是你可以使用 filter 和 lag() 就好了:

select id,
       (case when type = 'SLOWLY' then id
             else lag(id) filter (where type = 'SLOWLY') over (partition by external_id order by timestamp)
        end) as prev_slowly_id
from t;

相反，使用横向连接可能更容易:

select t.id, t2.prev_slowly_id as 
from t left join lateral
     (select t2.id
      from t t2
      where t2.external_id = t.external_id and
            t2.timestamp <= t.timestamp and
            t2.type = 'SLOWLY'
      order by t2.timestmp desc
     ) t2;

但最好的方法是累积最大值。 . .假设 id 随着时间戳增加:

select t.*,
       max(id) filter (where type = 'SLOWLY') over (partition by external_id order by id) as prev_slowly_id
from t;