在doris中实现行转列操作的三种方式及列转行的实现

本文演示使用sql进行列转行的操作和列转行

一、行转列

创建表

drop table score_1;
CREATE TABLE IF NOT EXISTS score_1
(
    `user_id` BIGINT NOT NULL COMMENT "user_id",
    `subject` VARCHAR(255) NOT NULL COMMENT "学科",
    `score` BIGINT  NOT NULL DEFAULT '0' COMMENT "分数"
)
DUPLICATE KEY(`user_id`,`subject`,`score`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
desc  score_1;

 插入数据

 insert  into `score_1`(`user_id`,`subject`,`score`) values (1,'MATH',90),(1,'ENGLISH',98),(1,'CHINESE',85),(2,'MATH',87),(2,'ENGLISH',78),(2,'CHINESE',89), (3,'MATH',90),(3,'ENGLISH',98),(3,'CHINESE',85),(4,'MATH',87),(4,'ENGLISH',78),(4,'CHINESE',89);

行转列的三种方式
1、union all

select user_id,SUM(MATH) AS 'MATH',SUM(ENGLISH) AS 'ENGLISH',SUM(CHINESE)AS CHINESE from (
(SELECT user_id,score AS 'MATH',0 AS 'ENGLISH',0 AS 'CHINESE' FROM score_1 WHERE subject = 'MATH')
UNION ALL
(SELECT user_id,0 AS 'MATH',score AS 'ENGLISH',0 AS 'CHINESE' FROM score_1 WHERE subject = 'ENGLISH')
UNION ALL
(SELECT user_id,0 AS 'MATH',0 AS 'ENGLISH',score AS 'CHINESE' FROM score_1 WHERE subject = 'CHINESE')) t
GROUP BY user_id

2、join

SELECT t1.user_id, t1.MATH, t2.score AS 'ENGLISH',t3.score AS 'CHINESE' FROM
    ( SELECT user_id, score AS 'MATH' FROM score_1 WHERE subject = 'MATH' ) AS t1
    JOIN ( SELECT user_id, score FROM score_1 WHERE subject = 'ENGLISH' ) AS t2 ON t1.user_id = t2.user_id
    JOIN ( SELECT user_id, score FROM score_1 WHERE subject = 'CHINESE' ) AS t3 ON t1.user_id = t3.user_id

3、sum group

    SELECT user_id,  
      sum(if(subject='MATH', score, NULL)) as `MATH`,  
      sum(if(subject='ENGLISH', score, NULL)) as `ENGLISH`, 
      sum(if(subject='CHINESE', score, NULL)) as `CHINESE`
FROM score_1 
GROUP BY user_id

二、列转行

drop table score_2;
CREATE TABLE IF NOT EXISTS score_2
(
    `user_id` BIGINT NOT NULL COMMENT "user_id",
    `math_score` BIGINT NOT NULL COMMENT "数学分数",
    `en_score` BIGINT NOT NULL COMMENT "英语分数",
    `cn_score` BIGINT NOT NULL COMMENT "语文分数"
)
UNIQUE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);


INSERT INTO score_2(user_id,cn_score,math_score,en_score) VALUES (1,90,92,80),(2,91,91,82),(3,97,81,86),(4,66,87,68),(5,86,89,78),(6,85,82,45);

select * from score_2;


select user_id,'语文' as subject,cn_score as score from score_2
union
select user_id,'英语' as subject,en_score as score from score_2
union 
select user_id,'数学' as subject,math_score as score from score_2;