SQL 关键词匹配

    SELECT 
    user_id,
    CONCAT_WS(',', COLLECT_LIST(CAST(search_query AS STRING))) AS search_queries,
    CONCAT_WS(',', COLLECT_LIST(CAST(matched_word AS STRING))) AS matched_word,
    CONCAT_WS(',', COLLECT_LIST(CAST(b_list AS STRING))) AS b_lists
    FROM (
    SELECT 
        s.user_id AS user_id,
        s.keyword AS search_query,
        d.game_name AS matched_word,
        d.b_list AS b_list,
        LENGTH(d.game_name) AS word_length,
        ROW_NUMBER() OVER (
            PARTITION BY s.user_id, s.keyword -- 按用户和每个搜索词分组，确保为每个用户的每个搜索词选最长匹配
            ORDER BY LENGTH(d.game_name) DESC
        ) AS rn
    FROM 
        search_query_table s
    JOIN 
        dictionary_table d
    ON 
        LOCATE(LOWER(d.game_name), LOWER(s.keyword)) > 0 -- 加入大小写转换
    ) ranked_matches
    WHERE rn = 1
    GROUP BY user_id