LATERAL VIEW 函数功能实现

客户需求

    客户在使用 GBase 8a 集群的过程中，提到了 HIVE 中的 LATERAL VIEW 函数，明确需要在 GBase 中使用该函数的功能，为了达到 LATERAL VIEW 函数的功能，使用GBase已有的功能对其进行了实现

LATERAL VIEW 函数说明：依据字段中的分隔符（默认为逗号），将原本汇总在一行的数据拆分成多行成为虚拟表，再与原表进行笛卡尔积，让字段数据完成行转列的效果

实现过程

1.构造测试数据

CREATE TABLE info (
   cx VARCHAR(100),
   tb TEXT,
   ziduan TEXT,
   shuliang TEXT
);
insert into info values('cx1','tb1','a1,a2,a3','11,22,33');
insert into info values('cx2','tb2','b1,b2','44,55');
insert into info values('cx3','tb3','c1','66');

测试数据如下图（ ziduan 和 shuliang 字段就是本次函数作用的对象 ）

2.创建自定义函数

DELIMITER $$
CREATE FUNCTION split_string(str VARCHAR(255), delim CHAR(1), pos INT) RETURNS VARCHAR(255)
BEGIN
   RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(str, delim, pos), delim, -1);
END$$
DELIMITER ;

3.创建demo表并插入数据

这里的demo表的数据需要根据字段中的分隔符数量来进行动态调整，最大值大于一个字段内最多的逗号数量即可

create table demo (num int);
insert into demo values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

测试数据如下图

4.Sql实现

SELECT
    i.cx,
    i.tb,
    split_string(i.ziduan, ',', n.num) AS var1,
    split_string(i.shuliang, ',', n.num) AS var2
FROM
    info i
JOIN
    (select num from demo) n
ON
    CHAR_LENGTH(i.ziduan) - CHAR_LENGTH(REPLACE(i.ziduan, ',', '')) >= n.num - 1;

查询效果如下图

info表中的 ziduan、shuliang 字段，按照逗号分隔符拆分成多行，并且按照字段内的次序成行，和原表进行了一次笛卡尔积连接

注：若原始数据量比较庞大，慎用该方法，可能会存在性能问题