Hive中的explode()函数和collect_set()函数

「爱情、让人受尽委屈。」 2021-09-14 10:48 540阅读 0赞

最近在Hive中查询测试数据用到explode()函数比较多，所以今天整理一下，方便以后查阅。

# 1.substr()函数 #

substr()是字符串截取函数，其语法为: substr(string A, int start, int len)，返回值为 string类型。说明：返回字符串A从start位置开始，长度为len的字符串。这里需要注意的是初始位置是从1开始。

# 2.explode()函数 #

explode()的功能是行转列。多说无益，直接上例子。

hive> SELECT xd, pt1, COUNT(1) AS num FROM (
        > SELECT CASE
        > WHEN SUBSTR(answer, 1, 1) IN ('A','B','C','D','E','F') THEN '小学' 
        > WHEN SUBSTR(answer, 1, 1) IN ('G','H','I') THEN '初中' 
        > WHEN SUBSTR(answer, 1, 1) IN ('J','K','L') THEN '高中' END xd, u.userId FROM userdata AS u WHERE u.questionId = 29 ) a 
        > INNER JOIN ( SELECT u.userId, CASE 
        > WHEN pt='A' THEN '利用互联网查找资料，以更好地备课' 
        > WHEN pt='B' THEN '给学生布置预习类的学习任务'
        > WHEN pt='C' THEN '利用互联网学习平台提升课堂教学互动效果'
        > WHEN pt='D' THEN '从教的视角出发准备数字资源，促进教学中更好地实现以学生为中心的教学活动实施'
        > WHEN pt='E' THEN '在教学流程中课前、课中和课后连续使用网络平台，改变课堂教与学的方式，如翻转课堂'
        > WHEN pt='F' THEN '从学的视角出发准备数字资源，支持学生依据需要进行自主、个性化学习'
        > WHEN pt='G' THEN '对学生学习过程进行监督和管理，保障教学的高效开展'
        > WHEN pt='H' THEN '对学生学习效果进行诊断和评价'
        > END pt1 FROM answer u
        > lateral view explode(split(u.answer, '')) c AS pt 
        > WHERE u.questionId = 37 AND pt IN ('A','B','C','D','E','F','G','H')) b 
        > ON a.userId = b.userId 
        > GROUP BY xd, pt1 
        > ORDER BY xd, num;

这里需要注意的是lateral view explode(split(u.answer, '')) c AS pt 这条语句中的c是虚表的名称，这个字段是必须要有的。说明：这条SQL语句查询出来的是一个问卷调查中某题各选项的选择人数，因为是多选题，所以首先要对结果进行split，然后再对split后的函数进行行转列操作。

# 3.collect\_set()函数 #

说到explode()函数就不得不说一下collect\_set()函数。collect\_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。例如，要统计每种no下的score，直接对no分组后对score进行collect\_set操作，如下：

select no，collect_set(score) from tablss group by no;

这样，就实现了将列转行的功效，但是注意只限同列基本数据类型，函数只能接受一列参数。

**附：concat\_ws()函数例子**

--hive合并所有电话号码相同的问题内容，用冒号分割
    SELECT B.LDHM, concat_ws(':',collect_set(b.WTNR))
      FROM (SELECT A.LDHM, A.DJRQ, A.WTNR
              FROM TEST1_12366 A
             WHERE A.LDHM IS NOT NULL
               AND LENGTH(A.LDHM) > 5
             ORDER BY A.LDHM, A.DJRQ) B
     GROUP BY B.LDHM;