DM技术交流QQ群：940124259

1. 场景描述

大伙儿可能经常遇到从MySQL数据库迁移到DM数据库，基本上有人认为数据全部都被搬过来，迁移过程中未出现任何错误，甚至严重性错误也未产生。偏偏程序运行起来，执行某项功能失败，检查程序后台日志发现是有关数据库SQL执行的语法报错“不是GROUP BY表达式”类似的字眼，然而这种错误在从MySQL迁到DM数据库很常见。
究其缘由，MySQL的SQL语法书写太随意、太灵活、太奇葩，迁到其他规范化的数据库中直接报错。本章主题围绕分组查询改写进行谈讨，如若想直接简单地解决问题，可参考文章《 魔障篇—兼容MySQL语法支持非分组项作为查询列.》，可跳过后文话题的深入讲解。

---

2. 改写目的

解决非分组项作为查询列的语法问题，对非分组项作查询列改写并纠正语法错误，同时兼顾提供多种手段实现同样的功能需求，即可选改写方案丰富。

---

3. 环境准备

数据准备：

create table tt3 (c1 int, c2 varchar(10),c3 varchar(5), c4 varchar(20));
insert into tt3 
  values
   (1, '张三1', '男', '总经理'),
   (2, '张三2', '男', '副经理'),
   (3, '李四1', '女', '销售'),
   (4, '李四2', '女', '销售'),
   (5, '王五1', '男', '行政'),
   (6, '王五1', '女', '秘书');   
   
commit;   

select * from tt3;
/*
C1	C2	C3	C4
1	张三1	男	总经理
2	张三2	男	副经理
3	李四1	女	销售
4	李四2	女	销售
5	王五1	男	行政
6	王五1	女	秘书
*/

功能需求：

查询以性别分组统计男与女各总人数，并从表中取出具有男女两组代表性的第一行记录。

select /*+GROUP_OPT_FLAG(1)*/ *, count(c3) from tt3 group by c3;

-- 结果集
/*
C1	C2	C3	C4	COUNT(C3)
1	张三1	男	总经理	3
3	李四1	女	销售	3
*/

4. 改写方法

4.1 改写1：first_value集函数（又名分组函数）

/*
思路：
运用first_value分组函数实现分组查询中查询列中非分组列的存在，间接将普通列转为合法的分组查询，即分组并取第一行记录
*/

SELECT FIRST_VALUE(C1),FIRST_VALUE(C2),FIRST_VALUE(C3),FIRST_VALUE(C4), COUNT(C3) FROM TT3 GROUP BY C3;

-- 结果集
/*
FIRST_VALUE(C1)	FIRST_VALUE(C2)	FIRST_VALUE(C3)	FIRST_VALUE(C4)	COUNT(C3)
1	张三1	男	总经理	3
3	李四1	女	销售	3
*/

4.1 改写2：有关rowid特性之拆分连接

/*
思路：
单独拎出分组查询运算形成一个内嵌派生子表（表子查询），再利用最小rowid（即同类分组中第一项）与原表连接连接查询，即可以获取非分组项的目标查询列。
*/

SELECT A.*, B.GENDER_CNT
FROM TT3 A,
(SELECT C3, MIN(ROWID) ROW_ID, COUNT(C3) GENDER_CNT FROM TT3 GROUP BY C3) B   
WHERE A.ROWID = B.ROW_ID AND A.C3 = B.C3;

-- 结果集
/*
C1	C2	C3	C4	GENDER_CNT
1	张三1	男	总经理	3
3	李四1	女	销售	3
*/

4.3 改写3：分析函数之分组排名

/*
思路：
利用分析函数分组查询统计，并结合同组排名，以rowid排序保持结果集排序输出，最后只取同组排名第一的数据（同组首行记录）
*/

SELECT B.C1, B.C2, B.C3, B.C4, B.GENDER_CNT 
FROM 
(
   SELECT 
         *,
         COUNT(C3) OVER(PARTITION BY C3) GENDER_CNT,
         ROW_NUMBER() OVER(PARTITION BY C3 ORDER BY ROWID) RN  
   FROM TT3
   ORDER BY ROWID
) B
WHERE B.RN = 1;

-- 结果集
/*
C1	C2	C3	C4	GENDER_CNT
1	张三1	男	总经理	3
3	李四1	女	销售	3
*/