如何在Presto中转换文本为Map并计算余弦相似度？

下面是完整的Presto SQL实现，能将文本转换为符合COSINE_SIMILARITY要求的词频Map，并输出你期望的结果表：

WITH text_data AS (
    -- 替换为你的实际数据表
    SELECT 1 AS id, 'a b b' AS text1, 'b c' AS text2
),
text1_word_counts AS (
    -- 拆分text1并统计每个单词的出现次数
    SELECT 
        id,
        text1,
        text2,
        word,
        COUNT(*) AS freq
    FROM text_data
    CROSS JOIN UNNEST(SPLIT(text1, ' ')) AS t(word)
    GROUP BY id, text1, text2, word
),
text2_word_counts AS (
    -- 拆分text2并统计每个单词的出现次数
    SELECT 
        id,
        word,
        COUNT(*) AS freq
    FROM text_data
    CROSS JOIN UNNEST(SPLIT(text2, ' ')) AS t(word)
    GROUP BY id, word
),
all_unique_words AS (
    -- 获取两个文本的所有唯一单词，生成统一维度的单词数组
    SELECT 
        id,
        ARRAY_AGG(DISTINCT word ORDER BY word) AS all_unique_words
    FROM (
        SELECT id, word FROM text1_word_counts
        UNION ALL
        SELECT id, word FROM text2_word_counts
    ) combined_words
    GROUP BY id
),
text1_map_data AS (
    -- 生成text1的词频数组和Map：不存在的单词用0填充
    SELECT 
        td.id,
        td.text1,
        td.text2,
        auw.all_unique_words,
        ARRAY_AGG(COALESCE(t1wc.freq, 0) ORDER BY auw.word) AS map1,
        MAP(auw.all_unique_words, ARRAY_AGG(COALESCE(t1wc.freq, 0) ORDER BY auw.word)) AS text1_freq_map
    FROM text_data td
    JOIN all_unique_words auw ON td.id = auw.id
    LEFT JOIN text1_word_counts t1wc 
        ON td.id = t1wc.id 
        AND t1wc.word = UNNEST(auw.all_unique_words)
    GROUP BY td.id, td.text1, td.text2, auw.all_unique_words
),
text2_map_data AS (
    -- 生成text2的词频数组和Map：不存在的单词用0填充
    SELECT 
        td.id,
        ARRAY_AGG(COALESCE(t2wc.freq, 0) ORDER BY auw.word) AS map2,
        MAP(auw.all_unique_words, ARRAY_AGG(COALESCE(t2wc.freq, 0) ORDER BY auw.word)) AS text2_freq_map
    FROM text_data td
    JOIN all_unique_words auw ON td.id = auw.id
    LEFT JOIN text2_word_counts t2wc 
        ON td.id = t2wc.id 
        AND t2wc.word = UNNEST(auw.all_unique_words)
    GROUP BY td.id, auw.all_unique_words
)
-- 最终计算余弦相似度并输出结果
SELECT 
    t1md.id,
    t1md.text1,
    t1md.text2,
    t1md.all_unique_words,
    t1md.map1,
    t2md.map2,
    ROUND(COSINE_SIMILARITY(t1md.text1_freq_map, t2md.text2_freq_map), 2) AS similarity
FROM text1_map_data t1md
JOIN text2_map_data t2md ON t1md.id = t2md.id;

关键步骤说明： #

• 文本拆分与词频统计：用SPLIT(text, ' ')将文本按空格拆分为单词数组，通过UNNEST展开为多行后分组统计词频。
• 生成统一维度的单词数组：合并两个文本的单词列表并去重，确保两个词频Map的key完全一致，保证余弦相似度计算的维度匹配。
• 生成词频Map：通过左关联将唯一单词与对应文本的词频绑定，用COALESCE将不存在的单词词频设为0，再用MAP(keys, values)生成符合要求的Map类型。
• 计算余弦相似度：调用COSINE_SIMILARITY函数传入两个词频Map，用ROUND保留两位小数得到最终结果。

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