sql处理重复的列，更好理清分组和分区

淡淡的烟草味﹌ 2024-03-25 09:48 34阅读 0赞

### 一、分组统计、分区排名 ###

#### 1、语法和含义： ####

> 如果查询结果看得有疑惑，看第二部分-sql处理重复的列，更好理清分组和分区，有建表插入数据的sql语句

##### 分组统计：GROUP BY 结合 统计/聚合函数一起使用 #####

-- 举例子: 按照性别统计男生、女生的人数
    select sex,count(distinct id) sex_num from student_score group by sex;

![ed27650978b3247a1115c6966e344ae1.png][]

##### 分区排名：ROW\_NUMBER() OVER(PARTITION BY 分区的字段 ORDER BY 升序/降序字段 \[DESC\]) #####

-- 举例子: 按照性别-男生、女生进行分区，按照成绩进行降序
    select id,name,sex,score,
    ROW_NUMBER() OVER(PARTITION BY sex ORDER BY score DESC) rn
    from student_score;

![e0a06df0a2b0137d9bb35ebfa380f288.png][]

#### 2、使用注意事项： ####

##### ▷ 排名函数row\_number() 需要的mysql 版本需要8及以上！ #####

##### ▷ 对于分组统计 group by 容易出现的报错问题： #####

###### `因为规定要求 select 列表的字段非聚合字段，必须出现在group by后面进行分组`。 ######

**报错**：Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘数据库.表.字段’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

> SELECT列表的表达式-不在GROUP BY子句中，并且包含非聚合列’数据库.表.字段’。

##### ▷ 对于排名函数ROW\_NUMBER，容易出现的报错问题： #####

*  一般是你的分区字段写得有问题，可以坚持一下分区字段！比如在hive中，分区字段为 get\_json\_object(map\_col,‘$.title’)，但是漏掉了一个’

**报错**：Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.

> 未能将窗口调用分解为组。至少 1 个组必须仅依赖于输入列。还要检查循环依赖。

### 二、sql处理重复的列，更好理清分组和分区 ###

#### 1、sql语句-建表、插入数据的语句 ####

DROP TABLE IF EXISTS `student_score`;
    CREATE TABLE `student_score` (
      `id`  int(6),
      `name` varchar(255),
      `sex` varchar(255),
      `subject` varchar(30),
      `score` float
    ) ENGINE = InnoDB;
    
    
    INSERT INTO `student_score` VALUES (1, '小明', '男','语文', 80);
    INSERT INTO `student_score` VALUES (2, '小红', '女','语文', 70);
    INSERT INTO `student_score` VALUES (3, '小哈', '女','语文', 88);
    INSERT INTO `student_score` VALUES (1, '小明', '男','数学', 66);
    INSERT INTO `student_score` VALUES (2, '小红', '女','数学', 70);
    INSERT INTO `student_score` VALUES (3, '小哈', '女','数学', 89);
    INSERT INTO `student_score` VALUES (1, '小明', '男','英语', 80);
    INSERT INTO `student_score` VALUES (2, '小红', '女','英语', 70);
    INSERT INTO `student_score` VALUES (3, '小哈', '女','英语', 68);

#### 2、查询所有学生的成绩： ####

*  select \* from student\_score;

![84985b8089cc833d8fa8335d302924ce.png][]

#### 3、结果，有重复的列值 ####

相应的成绩对应的学科名称是以列的形式展示的，造成了语文、语文、语文的重复

![322286d63cb30c6f3d41f00c7ec6154e.png][]

#### 4-1、处理重复的列-方式1-`合并去除重复`的列值\[列转行\] ####

> 对应到常见的sql应用场景，**统计各个学生的各科成绩**，实现方式有两种，一种是分组统计的方式，一种是分区排名的方式

##### 分组统计: #####

select id,name,sex,
    	max(case when subject='语文' then score else 0 end) as chinese,
    	max(case when subject='英语' then score else 0 end) as english,
    	max(case when subject='数学' then score else 0 end) as math
    from student_score 
    group by id
    order by score desc

*  结果：

![32cf6d1e35d9c35669b0f7c9223028df.png][]

> 按成绩降序排序，可以看到默认选择第一门学科-语文的成绩进行降序排序。

#### 4-2、处理重复的列-方式2-对重复的列值进行`排名` ####

##### 分区排名 #####

select id,name,subject,score,
           row_number() over(partition by subject order by score desc) rn
    from student_score;

![ab872ee457bd9eac39dac8afc1c9efe3.png][]

### 三、总结分组、分区的区别 ###

> 例如按学科分组或按学科分区，那么，分组得到的是一个列值(一条记录数据)的结果，分区是多个列值(多条记录数据)的结果。

#### 分组-一条记录 ####

![3c6b6f565d4684f2ebca25b4761e6518.png][]

#### 分区-多条记录 ####

![f2645ddff9fa9326f43b1d73aeafaac5.png][]

**如果本文对你有帮助的话记得给一乐点个赞哦，感谢！**

[ed27650978b3247a1115c6966e344ae1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/bc1f8b85fa7844e1aafb098847bc1f27.png
[e0a06df0a2b0137d9bb35ebfa380f288.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/275761211ea943f2b05eeffbd4852add.png
[84985b8089cc833d8fa8335d302924ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5a8df6c9616d4b0c9807895523359055.png
[322286d63cb30c6f3d41f00c7ec6154e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/eb30ac753eb442c5a91178980c46e2d1.png
[32cf6d1e35d9c35669b0f7c9223028df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8c9703fabcf648d2b6c00102ffe5a37b.png
[ab872ee457bd9eac39dac8afc1c9efe3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8df2311fa5b44dcabe2a83e12cd2c825.png
[3c6b6f565d4684f2ebca25b4761e6518.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1e5ae99828a14ace974c17df8a902317.png
[f2645ddff9fa9326f43b1d73aeafaac5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/af79c653363244d7a3fbe836833b77e0.png