列表处理

HiveQL 列表处理

问题描述

id	keywords
2	["数据挖掘","商业智能"]
1	["人工智能","深度学习"]
1	["数据分析","大数据"]
3	["统计分析","数据可视化"]
1	["大数据","人工智能"]
2	["机器学习","数据挖掘"]

id	keywords
2	人工智能,商业智能,数据挖掘,机器学习
1	人工智能,大数据,数据分析,数据可视化,深度学习

关键步骤

1. 数据来源：表 data 中包含广告数据，且每条记录有一个 id 和一个 keywords 列。keywords 列是一个包含关键词的数组。

2. 数据聚合：

   • 收集关键词：使用 collect_list(keywords) 函数聚合每个广告主的关键词列表。该函数会为每个 id 收集所有相关的 keywords 数组。
   • 展开数组：使用 flatten 函数将嵌套数组展开成一个单层数组。因为 collect_list 聚合的结果是一个数组的数组，需要将其转换成一个简单的一维数组。
   • 去重关键词：使用 ARRAY_DISTINCT 函数去除重复的关键词，以确保每个关键词只出现一次。
   • 排序关键词：使用 SORT_ARRAY 函数对关键词进行排序，便于阅读和分析。
   • 连接关键词：使用 CONCAT_WS(',', ...) 函数将关键词数组连接成一个逗号分隔的字符串，方便展示。
3. 分组操作：通过 GROUP BY id 语句按id进行分组，以确保每个id的关键词都聚合到一行中。

参考答案

-- cte
WITH DATA AS (
    SELECT  1 AS id,
            array('数据分析', '大数据') AS keywords,
            '2024-05-01' AS p_date,
            '2024-04-25 10:00:00' AS create_time
    UNION ALL
    SELECT  2 AS id,
            array('机器学习', '数据挖掘', '人工智能') AS keywords,
            '2024-05-01' AS p_date,
            '2024-04-20 14:00:00' AS create_time
    UNION ALL
    SELECT  1 AS id,
            array('大数据', '人工智能', '数据可视化') AS keywords,
            '2024-05-01' AS p_date,
            '2024-04-28 09:00:00' AS create_time
    UNION ALL
    SELECT  3 AS id,
            array('统计分析', '数据可视化') AS keywords,
            '2024-05-01' AS p_date,
            '2024-03-30 08:00:00' AS create_time
    UNION ALL
    SELECT  1 AS id,
            array('人工智能', '深度学习', '数据可视化') AS keywords,
            '2024-05-01' AS p_date,
            '2024-04-30 11:00:00' AS create_time
    UNION ALL
    SELECT  2 AS id,
            array('数据挖掘', '商业智能') AS keywords,
            '2024-05-01' AS p_date,
            '2024-04-18 07:00:00' AS create_time
)




-- 答案
SELECT
    id,  -- 选择广告主的ID
    CONCAT_WS(
        ',',  -- 使用逗号作为分隔符
        SORT_ARRAY(ARRAY_DISTINCT(flatten(collect_list(keywords))))  -- 对关键词进行处理
    ) AS keywords  -- 将处理后的关键词结果命名为keywords
FROM
    DATA  -- 从数据源DATA表中选择数据
GROUP BY
    id  -- 按广告主ID分组
ORDER BY
    id;  -- 按广告主ID排序

本文由mdnice多平台发布