Doris实战系列（三）Doris查询之行转列实战+列转行实战

在日常的工作中，我们在设计表的时候都是把表作为一个实体，然后向这个实体里面添加一行行的数据。但是有时候我们会涉及到使用数据来进行聚合计算，也就是把行数据指标进行聚合，转换成列来展示效果。举个例子：

现在有一个学生成绩的表，字段分别是id,学生id，subjectname,subjectscore。这样子只要学生进行考试，我们可以从这张表里面查询到每个学生各科的成绩。
但是现在有这样一个需求是要求是从这张表统计每个学生这次所有考试科目的成绩怎么办呢？这就是一个行转列的案例。

下面我们来演示一下：

一、创建表

这里根据上面的字段创建一张考试表：

-- 创建表
CREATE TABLE student_exam_score(
    id INT(11) NOT NULL,
    student_id VARCHAR(20) NOT NULL COMMENT '学生id',
    subject_name VARCHAR(20) COMMENT '考试科目',
    subject_score DOUBLE COMMENT '对应科目的考试成绩'
)
unique KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

二、插入测试数据

接下来我们插入对应的测试数据：

INSERT INTO student_exam_score  VALUES (1,'001','语文',90);
INSERT INTO student_exam_score  VALUES (2,'001','数学',91);
INSERT INTO student_exam_score  VALUES (3,'001','英语',92);
INSERT INTO student_exam_score  VALUES (4,'002','语文',93);
INSERT INTO student_exam_score  VALUES (5,'002','数学',94);
INSERT INTO student_exam_score  VALUES (6,'002','英语',95);
INSERT INTO student_exam_score  VALUES (7,'003','语文',96);
INSERT INTO student_exam_score  VALUES (8,'003','数学',97);
INSERT INTO student_exam_score  VALUES (9,'003','英语',98);
INSERT INTO student_exam_score  VALUES (10,'003','政治',99);

数据插入进去之后，我们查询下所有人的考试成绩

三、查询每个学生所有考试的成绩

接着我们按照上面的要求查询所有学生的考试成绩，传统的做法是：

SELECT student_id,
SUM(IF(`subject_name`='语文',subject_score,0)) as '语文',
SUM(IF(`subject_name`='数学',subject_score,0)) as '数学',
SUM(IF(`subject_name`='英语',subject_score,0)) as '英语',
SUM(IF(`subject_name`='政治',subject_score,0)) as '政治' 
FROM student_exam_score 
GROUP BY student_id;

在doris中使用起来就比较简单，他提供了一个map_agg的函数，可以把某两列组装成map进行聚合，最后得出相应的结果，示例如下：

select 
    student_id,
    IFNULL(map['语文'],0) as '语文',
    IFNULL(map['英语'],0) as '英语',
    IFNULL(map['数学'],0) as '数学',
    IFNULL(map['政治'],0) as '政治'
from  (
    select student_id ,map_agg(subject_name,subject_score) as map from student_exam_score group by student_id
) t ;

可以明显的看到doris这里使用map_agg函数能直接查询出来结果。他的特点是：

1、使用doris的行转列函数进行查询，看sql很容易理解。
2、使用doris的行转列函数进行查询，效率比传统做法快。

接下来我们再介绍下列转行的实操。在原始表里面我们可以看到id为001和002的学生是没有政治成绩的，

那么我们希望查询的时候给他吧没有的学科展示出来，那怎么办呢？在doris中主要会通过lateral view来实现的，关于Lateral View的说明可见官网：

https://doris.apache.org/zh-CN/docs/3.0/query-data/lateral-view

下面我们用lateral view来掩饰下，他的核心其实也是组装使用逗号分隔的数组，然后挨个取对应位置的值，sql语句如下：

select student_id,
      element_at(sub_score, 1) as subject,
      element_at(sub_score, 2) as score 
from (
  select student_id,
         SPLIT_BY_STRING(sub ,',') sub_score 
  from  (   
      select student_id, 
             array(
                concat('语文',',',SUM(IF(`subject_name`='语文',subject_score,0))),
                concat('数学',',',SUM(IF(`subject_name`='数学',subject_score,0))),
                concat('英语',',',SUM(IF(`subject_name`='英语',subject_score,0))),
                concat('政治',',',SUM(IF(`subject_name`='政治',subject_score,0)))
             ) as scores 
      from student_exam_score  GROUP BY student_id
      ) tmp LATERAL VIEW explode(scores) tbl1 AS sub 
) rs;

最后查询的结果如下：