Snowflake/SQL实现管道分隔标签提取与各标签使用次数统计

实现逻辑 #

• 先过滤无标签的无效记录（即标记为*no tags*的视频条目）
• 对有效标签字符串按管道符分割为标签数组，自动识别连续分隔符避免产生空值
• 通过横向表函数将数组展开为逐行的单个标签条目，通过多层裁剪去掉每个标签前后的多余空格
• 对清洗后的单个标签做分组计数，按使用次数降序排序输出

可直接运行的SQL实现 #

假设原始存储视频和标签的表名为video_tags，包含video_name、tags两个字段：

WITH base_clean AS (
    -- 清洗原始标签字段，过滤无标签记录
    SELECT TRIM(tags) AS raw_tag_content
    FROM video_tags
    WHERE TRIM(tags) <> '*no tags*'
),
tag_split AS (
    -- 分割字符串并展开为逐行标签
    SELECT
        -- 可根据需求调整大小写转换逻辑：
        -- 1. 统一为全大写：UPPER(TRIM(tag_element.value))  -- 输出格式为NBA、USA，匹配示例
        -- 2. 统一为首字母大写：INITCAP(TRIM(tag_element.value)) -- 输出格式为Nba、Usa
        -- 3. 不做大小写转换：TRIM(tag_element.value) -- 精准匹配原始标签大小写
        UPPER(TRIM(tag_element.value)) AS clean_tag
    FROM base_clean,
    LATERAL FLATTEN(INPUT => STRTOK_TO_ARRAY(raw_tag_content, '|')) tag_element
    WHERE TRIM(tag_element.value) <> '' -- 二次过滤分割产生的空标签
)
-- 按要求格式输出统计结果
SELECT
    CONCAT(clean_tag, ' - ', COUNT(*)) AS tag_usage_stat
FROM tag_split
GROUP BY clean_tag
ORDER BY COUNT(*) DESC, clean_tag;

适配说明 #

• 无需提前预置所有可能出现的标签值，自动适配任意数量的标签条目
• 兼容标签数量不固定的场景，可处理每个视频带0个、1个或多个标签的情况
• 自动兼容脏数据场景：包括标签前后带多余空格、连续管道符分隔、标签字段前后带冗余空白字符等
• 对应用户提供的示例测试数据，统一为全大写格式时输出为：

  NBA - 2
  USA - 1
  JORDAN - 1
  AMERICA - 1
  

  注：用户给出的示例输出遗漏了America的统计结果，该标签在第一个视频的标签列表中存在。

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