MATCH_RECOGNIZE中FIRST()与LAST()函数取值异常问题求助

问题根源 #

你的SIMILAR定义依赖LAG(TWEET)，导致相似序列的第一条基准推文无法被SIMILAR匹配（第一条记录没有前序值）。PATTERN (SIMILAR+)只能匹配从第二条开始的连续相似记录，因此FIRST()和LAST()仅作用于这个截断的子序列，而非完整的相似推文序列。

修正后的查询方案 #

方案1：包含起始记录的统一匹配定义

SELECT 
  USERID
  , NUM_OF_TWEETS
  , FIRST_TWEET
  , LAST_TWEET
  , FIRST_TWEET_ID
  , LAST_TWEET_ID
FROM SCRATCH.SAQIB_ALI.TWEETS
MATCH_RECOGNIZE(
  PARTITION BY USERID
  ORDER BY TWEETID ASC
  MEASURES
    FIRST(SEQ.TWEET) AS FIRST_TWEET,
    LAST(SEQ.TWEET) AS LAST_TWEET,
    FIRST(SEQ.TWEETID) AS FIRST_TWEET_ID,
    LAST(SEQ.TWEETID) AS LAST_TWEET_ID,
    COUNT(SEQ.*) AS NUM_OF_TWEETS
    
  ONE ROW PER MATCH
  PATTERN (SEQ+)
  DEFINE
    SEQ AS (
      -- 允许第一条记录匹配，后续记录需与前一条相似度>90
      LAG(TWEET) IS NULL 
      OR JAROWINKLER_SIMILARITY(TWEET, LAG(TWEET)) > 90
    )
    -- 过滤掉仅单条推文的序列
    AND COUNT(SEQ.*) OVER (MATCH) >= 2
);

方案2：明确区分起始与相似推文的模式

SELECT 
  USERID
  , NUM_OF_TWEETS
  , FIRST_TWEET
  , LAST_TWEET
  , FIRST_TWEET_ID
  , LAST_TWEET_ID
FROM SCRATCH.SAQIB_ALI.TWEETS
MATCH_RECOGNIZE(
  PARTITION BY USERID
  ORDER BY TWEETID ASC
  MEASURES
    FIRST(START_TWEET.TWEET) AS FIRST_TWEET,
    LAST(SIMILAR_TWEET.TWEET) AS LAST_TWEET,
    FIRST(START_TWEET.TWEETID) AS FIRST_TWEET_ID,
    LAST(SIMILAR_TWEET.TWEETID) AS LAST_TWEET_ID,
    COUNT(*) AS NUM_OF_TWEETS
    
  ONE ROW PER MATCH
  PATTERN (START_TWEET SIMILAR_TWEET+)
  DEFINE
    SIMILAR_TWEET AS JAROWINKLER_SIMILARITY(TWEET, LAG(TWEET)) > 90
);

方案说明 #

• 方案1通过调整匹配规则，让第一条记录也能纳入序列，同时过滤掉单条推文的无效序列，确保返回的都是至少包含2条相似推文的组。
• 方案2通过PATTERN明确拆分起始推文和后续相似推文，直接通过START_TWEET获取序列的第一条记录，逻辑更直观。

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