SQL进阶技巧：如何不使用union all进行行转列？【三种方法实现】

会飞的一十六 2024-08-17

“

行转列一直是SQL开发常见的数据结构转换方式，一般最普遍的方法就是采用union all的形式，但这种方式代码重复片段太多，写法不够优雅。本文采用hive sql的形式给出了三种行转列的方法，分别利用hive中的explode()、inline()、及stack()函数进行转换。

这里首先声明，行转列与列转行的定义，主要是以矩阵转置的概念来看，我们把多列组成的一行数据称为行向量，把该行向量变换为多行一列（列向量）的过程称为行转列，相反的把列向量（多行一列）转换为行向量（多列一行）的过程称为列转行。那么有同学会问在hive中，经常会把多行数据按照指定的分隔符合并成一列，这种也属于列转行吗？为了区分，我们统一把这种操作方式叫多行转一行，或者叫数据合并过程（collect_list()），而把该过程的逆过程称为一行转多行，或称为数据展开过程（explode()，flatmap()）。

”

—

需求描述

已知学生成绩表如下，分别有三个科目英语、数学、历史，现按照如下规则对表进行转换。保持英语、数学、历史顺序不变。

—

数据准备

with base as (
  select 'tom' as name, 80 as english, 90 as math, 100 as history
  union all
  select 'jery' as name, 30 as english, 60 as math, 70 as history
)

—

数据分析

3.1 lateral view explode(array()) 方法

explode()函数使用这里不再赘述，仅提供一种SQL数据分析的思维方法

with base as (
  select 'tom' as name, 80 as english, 90 as math, 100 as history
  union all
  select 'jery' as name, 30 as english, 60 as math, 70 as history
)
 
select name
     , subject
     , case when subject='english' then english
            when subject='math' then math
            when subject='history' then history
       end as score
from (select name
           , english
           , math
           , history
      from base) t lateral view explode(array('english', 'math', 'history')) tmp as subject

3.2 使用stack()方法

从上面的该函数的使用来看，stack()函数本质就是一个行转列函数，该函数也可以与lateral view 连用。下面我们就用该函数来实现。

方法1：lateral view stack()

with base as (
select 'tom' as name, 80 as english, 90 as math, 100 as history
union all
select 'jery' as name, 30 as english, 60 as math, 70 as history
)
 
select
name,
subject,
score
from base
lateral view stack(3, 'english',english, 'math',math,'history',history) tmp as subject,score

方法2：直接使用stack()函数

由于英语、数学、历史科目可枚举，所以可以直接用stack()进行转换。stack()函数就是将矩阵转置函数

with base as (
  select 'tom' as name, 80 as english, 90 as math, 100 as history
  union all
  select 'jery' as name, 30 as english, 60 as math, 70 as history
)
select stack(3,
             name, 'english', english,
             name, 'math', math,
             name, 'history', history
       ) as (name, subject, score)
from base;

3.3 lateral view inline(array(struct<>))

注意，inline函数的输入参数要求为array(struct<>)类型

类似于explode()函数，只是输入参数形式不一样，inline要求数组内部结构中必须为结构体才行，条件比较苛刻。（不常用）

with base as (
  select 'tom' as name, 80 as english, 90 as math, 100 as history
  union all
  select 'jery' as name, 30 as english, 60 as math, 70 as history
)
 
select name,
       subject,
       score
from (select name
           , array(struct('history', history), struct('math', math), struct('history', history)) as scores
      from base) t
         lateral view inline(scores) tmp as subject, score

—

小结

本文通过具体的案例来分析hivesql中具体行转列的技巧和方法，文中具体给出了三种方法，分别利用explode()函数、stack()函数以及inline()函数来完成，解决了传统union all进行行转列代码片段重复的问题，实现方式上较为优雅。

SQL进阶技巧：如何计算先进先出库龄问题？

SQL进阶技巧：如何计算重叠区间合并问题？

数仓建模：DWS层该如何建设？如何设计通用数据模型？

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。