多行数据合并成一行

太过爱你忘了你带给我的痛 2021-09-27 16:14 543阅读 0赞

需求：在大数据平台maxcompute上统计各个等级对应的人数，字段大致如下

CREATE TABLE `XXXX` (
      `requestid` char(32) DEFAULT NULL,
      `id` char(32) NOT NULL,
      `createtime` varchar(50) DEFAULT NULL COMMENT '创建时间',
      `createdate` varchar(50) DEFAULT NULL COMMENT '创建日期',
      `showdate` varchar(50) NOT NULL DEFAULT '' COMMENT '统计日期',
      `total` int(11) DEFAULT '0' COMMENT '总人数',
      `five_star_num` int(11) DEFAULT '0' COMMENT '五星级人数',
      `four_star_num` int(11) DEFAULT '0' COMMENT '四星级人数',
      `three_star_num` int(11) DEFAULT '0' COMMENT '三星级人数',
      `two_star_num` int(11) DEFAULT '0' COMMENT '二星级人数',
      `one_star_num` int(11) DEFAULT '0' COMMENT '一星级人数',
      `zero_star_num` int(11) DEFAULT '0' COMMENT '零星级人数',
      `new_member` int(11) DEFAULT '0' COMMENT '新增用户数',
      PRIMARY KEY (`showdate`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='日活-用户等级变化统计表';

目前面临的问题：各个等级都在一张表中，可以按等级分类查询人数，查询语句如下：

查询出各个等级对应的人数

select user_level,count(id) as count from XXX where register_date<=? group by user_level

查询结果如下

![20181030164221999.jpg][]

然而现在只能在大数据平台（maxcompute）编写sql，不能借助任何高级语句（如java），要实现将表内的数据以一行的形式存储到

with a1 as (
        select user_level,count(id) as count from XXXXXXX where register_date<=? group by user_level 
    ),a4 as(
        select  
            sum(CASE when a1.user_level="0" then a1.count else 0 end) as zero_star_num, 
            sum(CASE when a1.user_level="1" then a1.count else 0 end) as one_star_num, 
            sum(CASE when a1.user_level="2" then a1.count else 0 end) as two_star_num, 
            sum(CASE when a1.user_level="3" then a1.count else 0 end) as three_star_num, 
            sum(CASE when a1.user_level="4" then a1.count else 0 end) as four_star_num,
            sum(CASE when a1.user_level="5" then a1.count else 0 end) as five_star_num from a1 
    )
    select * from a4

结果如下

![20181030164515345.png][]

[20181030164221999.jpg]: /images/20210923/634784c849be4a0793257644b5ea8db7.png
[20181030164515345.png]: /images/20210923/82247e8a056d4ee18ee66b8361708c02.png