如何将会话原消息的TIMEL值关联到其对应的首条回复记录中

问题背景 #

本问题是Stack Overflow上《How to identify the first response message to each previous message?》问题的延伸，由于涉及不同的处理逻辑，因此单独发起提问。
目前已在@Mikhail的帮助下获得如下数据：
#

基础代码 #

原始数据生成代码 #

WITH HAVE AS
   (SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929267942 AS TIMEL UNION ALL 
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929298918 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929307192 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929313065 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929317717 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929333779 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929337240 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929404611 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929448033 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929477379 AS TIMEL
)

已实现的首条回复标识代码 #

select * except(grp),
  if(
    row_number() over(partition by conversationid, grp order by timel) = 1
    and grp > 0, 1, 0
  ) first_message
from (
  select * except(isnew), 
    countif(isnew) over(partition by conversationid order by timel) grp
  from (
    select *,
      sentby != lag(sentby) over(partition by conversationid order by timel) isnew
    from have
  )
)

待实现需求 #

将发起会话的原始消息的TIMEL值，关联到对应的首条回复记录上。示例场景中，消费者在1631929267942（第1条记录）发起提问，坐席在1631929298918（第2条记录）给出首条回复；若消费者在坐席回复前发送了多条消息，仅取消费者发送的第一条消息的TIMEL值作为关联值。 #

实现方案 #

我们可以在现有分组逻辑的基础上，提前计算每一轮消费者发起消息的首条时间，再匹配到对应坐席的首条回复记录上，完整实现代码如下：

WITH HAVE AS
   (SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929267942 AS TIMEL UNION ALL 
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929298918 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929307192 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929313065 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929317717 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929333779 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929337240 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929404611 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929448033 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929477379 AS TIMEL
),
-- 步骤1：保留原有分组逻辑生成轮次标识grp
grouped_data AS (
  select * except(isnew), 
    countif(isnew) over(partition by conversationid order by timel) grp
  from (
    select *,
      sentby != lag(sentby, 1, sentby) over(partition by conversationid order by timel) isnew
    from have
  )
),
-- 步骤2：计算每个轮次分组的发送方和该轮次的首条消息时间
group_meta AS (
  SELECT 
    conversationid,
    grp,
    sentby as group_sender,
    MIN(timel) as group_first_timel
  FROM grouped_data
  GROUP BY conversationid, grp, group_sender
),
-- 步骤3：为每个坐席轮次匹配上一轮消费者轮次的首条消息时间
matched_data AS (
  SELECT 
    a.*,
    b.group_first_timel as consumer_question_timel
  FROM group_meta a
  LEFT JOIN group_meta b 
    ON a.conversationid = b.conversationid 
    AND a.grp = b.grp + 1 
    AND b.group_sender = 'CONSUMER'
)
-- 步骤4：关联回原始明细输出最终结果
SELECT 
  g.*,
  IF(
    row_number() over(partition by g.conversationid, g.grp order by g.timel) = 1
    and g.grp > 0, 1, 0
  ) first_message,
  m.consumer_question_timel
FROM grouped_data g
LEFT JOIN matched_data m 
  ON g.conversationid = m.conversationid 
  AND g.grp = m.grp
ORDER BY g.timel

逻辑说明 #

1. 先沿用原有逻辑生成会话轮次分组grp，同一轮次内所有消息发送方一致
2. 计算每个轮次分组的发送方和该轮次的首条消息时间
3. 为坐席所在的轮次，匹配上一轮消费者轮次的首条消息时间
4. 最终关联回原始明细后，首条回复记录（first_message=1）对应的consumer_question_timel就是需要关联的原始提问时间，即使消费者在一轮内发送多条消息，也只会取该轮的第一条时间。

内容的提问来源于stack exchange，提问作者user2337871