用户在IM首次发送消息，到接收消息的时效问题

用户在IM首次发送消息，到接收消息的时效问题？

场景：响应时效问题

示例

解决办法:

• 按照group组 和 type 分组，选取rn=1就可以圈定 首次发送和首次接收问题；
• 使用lead函数，即可获取下一次回复时间

答案如下

  select 
  ds
  ,type
  ,msg_timedate 
  ,msgtimestamp
  ,sign_symbol
  ,sign_goal --沟通目标对象
  ,sing_body -- 沟通内容
  ,group_im 
  ,rn 
  ,rn2 
  -- 有一个小细节，如果用户多次询问的问题   
  ,lead(msg_timedate,1,to_date(to_char(GETDATE(),'yyyymmdd'),'yyyymmdd')) 
          OVER(PARTITION BY group_im ORDER BY msgtimestamp asc ) AS next_im_time 

  ,lead(type,1,to_date(to_char(GETDATE(),'yyyymmdd'),'yyyymmdd')) 
          OVER(PARTITION BY group_im ORDER BY msgtimestamp asc ) AS next_im_type 

  ,lead(sing_body,1,to_date(to_char(GETDATE(),'yyyymmdd'),'yyyymmdd')) 
          OVER(PARTITION BY group_im ORDER BY msgtimestamp asc ) AS next_im_body 

  ,lead(sing_body,1,to_date(to_char(GETDATE(),'yyyymmdd'),'yyyymmdd')) 
          OVER(PARTITION BY group_im,type ORDER BY msgtimestamp asc ) AS next_im_body_type

  from tmp_p2p_message_info
  where ds = '20200331'
  and (rn = 1  or rn2 = 1 )
  and sign_symbol = 'e5073dc3b7b82c2d8b12e05b64426378'
  and sign_goal = 'a182a1a17df5ffad4f96f49b70a71696'