根据时间戳搜索数据，在 Postgres 中具有多个联接和时间戳之间的依赖关系

Question

我有两个表格prod_replay_in和prod_replay_out如下。

对于msg_typeprod_replay_in表中CDST010prod_replay_out表中确认为CDST01C。对于msg_type也是如此，因为CDST100prod_replay_out表中的确认消息是CDST10C。在下表中，cdsx_time很重要。例如，第二条CDST100消息将具有基于cdsx_time的第二条CDST10C消息。

注意：2 个表之间的 JOIN 基于cdsx_id，因为它在两个表之间很常见。CDST010 和CDST01C将分别在prod_replay_in和prod_replay_out表中对同一cdsx_id只有一个条目。此外，CDST100和CDST10C消息的数量因cdsx_id而异。

我想搜索什么？

我想按照以下条件在表中搜索prod_replay_inCDST100邮件的数量：

• 如果 CDST100bancs_msg中的第四个字符与msg_body中的第四个字符匹配，则获得第 1 CDST100 CDST01C
• 如果 CDST100bancs_msg中的第四个字符与第四个字符匹配，则获得第 2 CDST100 第1 CDST10Cmsg_body中的角色
• 如果 CDST100 bancs_msg 中的第四个字符与第四个字符匹配，则获得第 3 CDST100msg_body第二CDST10C

上面的模式就像用第一个CDST10C检查第 2 个CDST100，用第 2 个CDST10C检查第 3 CDST100，依此类推。

prod_replay_in

id(serial) | msg_type(varchar) | cdsx_time(timestamp)  | cdsx_id(varchar)    | bancs_msg(text)
------------------------------------------------------------------------------------------
8334698    | CDST010           | 2020-02-24 14:23:01.0 | T202005518525        | ABCD
8341809    | CDST100           | 2020-02-24 14:47:38.0 | T202005518525        | ANOC
8342732    | CDST100           | 2020-02-24 14:51:53.0 | T202005518525        | PHLM
8344890    | CDST100           | 2020-02-24 15:15:14.0 | T202005518525        | JKQO

prod_replay_out

id(serial) | msg_type(varchar) | cdsx_time(timestamp)    | cdsx_id(varchar)     | msg_body(text)
------------------------------------------------------------------------------------------
42164527   | CDST01C           | 2020-02-24 14:23:08.016 | T202005518525        | AQRS
42176068   | CDST10C           | 2020-02-24 14:47:47.056 | T202005518525        | STUM
42177522   | CDST10C           | 2020-02-24 14:52:00.031 | T202005518525        | XYZK
42245814   | CDST10C           | 2020-02-24 15:30:00.045 | T202005518525        | ASQO

我试过什么？

我尝试创建 sql 查询以匹配第一个CDST100和CDST01C但不确定如何与后续CDST100进行比较CDST10C？

SELECT count(T.id) FROM prod_replay_in T JOIN prod_replay_out O ON T.CDSX_ID = O.CDSX_ID
JOIN prod_replay_out K ON K.CDSX_ID = O.CDSX_ID
WHERE T.MSG_TYPE = 'CDST100'
and O.msg_type = 'CDST01C'
and K.msg_type = 'CDST10C'
and ( (
min(T.cdsx_time) > O.cdsx_time
and substr(T.bancs_msg,4,1) = substr(O.msg_body,4,1)
)
);

Answer 1

我已经尝试了以下代码。根据我的知识，它应该可以工作。如果缺少任何场景，请随时提出建议。

Select count(T.*) from PROD_REPLAY_IN T, PROD_REPLAY_OUT O
Where T.msg_type = 'CDST100'
AND O.msg_type IN ('CDST01C', 'CDST10C')
AND T.cdsx_id = O.cdsx_id
AND substr(T.bancs_msg,4,1) = substr(O.msg_body,4,1)
AND (O.cdsx_id, O.cdsx_time) IN (Select Y.cdsx_id, Max(Y.cdsx_time) from PROD_REPLAY_OUT Y
Where Y.msg_type IN ('CDST01C', 'CDST10C')
AND O.cdsx_id = Y.cdsx_id
AND T.cdsx_time > Y.cdsx_time
GROUP BY Y.cdsx_id
)

Answer 2

只要您知道每个prod_replay_in行都会有一个时间戳对应的prod_replay_out行，您就可以将其设置为使用窗口函数在重播开始时避开不同的消息类型：

with interleave as (
select msg_type, cdsx_time, cdsx_id, bancs_msg as msg, 'in' as direction
from prod_replay_in
union all
select msg_type, cdsx_time, cdsx_id, msg_body as msg, 'out' as direction
from prod_replay_out
), match_cdst100 as (
select cdsx_id, msg,
lag(msg) over (partition by cdsx_id
order by cdsx_time) as last_msg
from interleave
where msg_type = 'CDST100'
)
select cdsx_id, count(*) 
from match_cdst100
where substr(msg, 4, 1) = substr(last_msg, 4, 1)
group by cdsx_id;