GaussDB(DWS)实现动态行转列探究
【摘要】 对于经典的行转列问题，如果行数不定导致输出的列数不定，标准的答案就是使用动态SQL, 但GaussDB(DWS)不支持PIVOT和UNPIVOT，那该怎么办，一起来探究下吧。
数据准备
基础表创建
/* 学生表 */
CREATE TABLE student (
sid VARCHAR(16),
sname VARCHAR(20)
);
/* 课程表 */
CREATE TABLE course (
cid VARCHAR(20),
cname VARCHAR(100)
);
/* 成绩表 */
CREATE TABLE score (
sid VARCHAR(16),
cid VARCHAR(20),
val FLOAT
);
数据导入
/* 学生表数据 */
insert into student (sid, sname) values('1001', '张三');
insert into student (sid, sname) values('1002', '李四');
insert into student (sid, sname) values('1003', '赵二');
insert into student (sid, sname) values('1004', '王五');
insert into student (sid, sname) values('1005', '刘青');
insert into student (sid, sname) values('1006', '周明');
/* 课程表数据 */
insert into course (cid, cname) values('C001', '大学语文');
insert into course (cid, cname) values('C002', '新视野英语');
insert into course (cid, cname) values('C003', '离散数学');
insert into course (cid, cname) values('C004', '概率论与数理统计');
insert into course (cid, cname) values('C005', '线性代数');
insert into course (cid, cname) values('C006', '高等数学(一)');
insert into course (cid, cname) values('C007', '高等数学(二)');
/*成绩表数据*/
insert into score(sid, cid, val) values('1001', 'C001', 67);
insert into score(sid, cid, val) values('1002', 'C001', 68);
insert into score(sid, cid, val) values('1003', 'C001', 69);
insert into score(sid, cid, val) values('1004', 'C001', 70);
insert into score(sid, cid, val) values('1005', 'C001', 71);
insert into score(sid, cid, val) values('1006', 'C001', 72);
insert into score(sid, cid, val) values('1001', 'C002', 87);
insert into score(sid, cid, val) values('1002', 'C002', 88);
insert into score(sid, cid, val) values('1003', 'C002', 89);
insert into score(sid, cid, val) values('1004', 'C002', 90);
insert into score(sid, cid, val) values('1005', 'C002', 91);
insert into score(sid, cid, val) values('1006', 'C002', 92);
insert into score(sid, cid, val) values('1001', 'C003', 83);
insert into score(sid, cid, val) values('1002', 'C003', 84);
insert into score(sid, cid, val) values('1003', 'C003', 85);
insert into score(sid, cid, val) values('1004', 'C003', 86);
insert into score(sid, cid, val) values('1005', 'C003', 87);
insert into score(sid, cid, val) values('1006', 'C003', 88);
insert into score(sid, cid, val) values('1001', 'C004', 88);
insert into score(sid, cid, val) values('1002', 'C004', 89);
insert into score(sid, cid, val) values('1003', 'C004', 90);
insert into score(sid, cid, val) values('1004', 'C004', 91);
insert into score(sid, cid, val) values('1005', 'C004', 92);
insert into score(sid, cid, val) values('1006', 'C004', 93);
insert into score(sid, cid, val) values('1001', 'C005', 77);
insert into score(sid, cid, val) values('1002', 'C005', 78);
insert into score(sid, cid, val) values('1003', 'C005', 79);
insert into score(sid, cid, val) values('1004', 'C005', 80);
insert into score(sid, cid, val) values('1005', 'C005', 81);
insert into score(sid, cid, val) values('1006', 'C005', 82);
insert into score(sid, cid, val) values('1001', 'C006', 77);
insert into score(sid, cid, val) values('1002', 'C006', 78);
insert into score(sid, cid, val) values('1003', 'C006', 79);
insert into score(sid, cid, val) values('1004', 'C006', 80);
insert into score(sid, cid, val) values('1005', 'C006', 81);
insert into score(sid, cid, val) values('1006', 'C006', 82);
静态行转列
select st.sid, st.sname,
sum(CASE c.cid WHEN '大学语文' THEN s.val ELSE 0 END ) "大学语文",
sum(CASE c.cid WHEN '新视野英语' THEN ifnull(s.val,0) ELSE 0 END ) "新视野英语",
sum(CASE c.cid WHEN '离散数学' THEN ifnull(s.val,0) ELSE 0 END ) "离散数学",
sum(CASE c.cid WHEN '概率论与数理统计' THEN ifnull(s.val,0) ELSE 0 END ) "概率论与数理统计",
sum(CASE c.cid WHEN '线性代数' THEN ifnull(s.val,0) ELSE 0 END ) "线性代数",
sum(CASE c.cid WHEN '高等数学(一)' THEN ifnull(s.val,0) ELSE 0 END ) "高等数学(一)",
sum(CASE c.cid WHEN '高等数学(二)' THEN ifnull(s.val,0) ELSE 0 END ) "高等数学(二)"
from student st
left join score s On st.sid = s.sid
left join course c On c.cid = s.cid
group by st.sid, st.sname;
动态行转列
列语句生成，版本>=8.1.2，使用GROUP_CONCAT
SELECT group_concat(
concat('sum(IF(c.cid = ''', c.cid, ''', s.val, 0)) AS "', c.cname, '"')
)
FROM course c;
列语句生成，版本<8.1.2不支持GROUP_CONCAT，暂且用LISTAGG替代
SELECT listagg(
concat('sum(IF(c.cid = ''', c.cid, ''', s.val, 0)) AS "', c.cname, '"'),
',') within GROUP(ORDER BY 1)
FROM course c;
新建/重建视图，可以用函数
CREATE OR REPLACE FUNCTION build_view()
RETURNS VOID
LANGUAGE plpgsql
AS $$ DECLARE
sql text;
rec record;
BEGIN
sql := 'select LISTAGG(
CONCAT( ''sum(IF(c.cid = '''''', c.cid, '''''', s.val, 0)) AS "'', c.cname, ''"'' )
,'','' ) within group(order by c.cid) from course c;';
EXECUTE sql INTO rec;
sql := 'drop view if exists get_sum';
EXECUTE sql;
sql := 'create view get_sum as select st.sid, st.sname, ' || rec.LISTAGG || 'from student st
left join score s On st.sid = s.sid
left join course c On c.cid = s.cid
group by st.sid, st.sname';
EXECUTE sql;
END$$;
call build_view();
新建/重建视图，也可以用匿名块
DECLARE
sql text;
rec record;
BEGIN
sql := 'select LISTAGG(
CONCAT( ''sum(IF(c.cid = '''''', c.cid, '''''', s.val, 0)) AS "'', c.cname, ''"'' )
,'','' ) within group(order by c.cid) from course c;';
EXECUTE sql INTO rec;
sql := 'drop view if exists get_sum';
EXECUTE sql;
sql := 'create view get_sum as select st.sid, st.sname, ' || rec.LISTAGG || 'from student st
left join score s On st.sid = s.sid
left join course c On c.cid = s.cid
group by st.sid, st.sname';
EXECUTE sql;
END;
/
执行视图查询
select * from get_sum;
想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）