mysql 按两字段分组，取字段a所有子集的第一行

背景

假设我们有一张工单表，主要记录了每个工单所属的区划，以及办理的事项名称。
现在想要统计出每个区划内工单数最多的事项名称。

原始数据

目标结果

初步思路

首先根据区划、事项名称进行分组，并根据工单数量进行排序。

SELECT
	count( 1 ) cnt, item_name, region_name 
FROM
	work_order 
GROUP BY
	item_name, region_name 
ORDER BY
	region_name, count( 1 ) DESC;

这样分组之后其实只差一步，即过滤出每个区的第一条数据。
可以顺着看，或者跳到方法2(较优思路)。

再次按照区划分组获取每个区最大工单量

SELECT
	region_name,
	max( cnt ) mt 
FROM
	( SELECT count( 1 ) cnt, item_name, region_name FROM work_order GROUP BY item_name, region_name ) t
GROUP BY
	region_name;

这样就获取到了每个区不同事项的最大工单数，再与上一步的结果集进行关联即可得到最终结果。

SELECT
	t1.*
FROM
	( SELECT count( 1 ) cnt, item_name, region_name FROM work_order GROUP BY item_name, region_name ) t1,
	(
	SELECT
		region_name,
		max( cnt ) mt 
	FROM
		( SELECT count( 1 ) cnt, item_name, region_name FROM work_order GROUP BY item_name, region_name ) t
	GROUP BY
		region_name 
	) t2 
WHERE
	t1.cnt = t2.mt 
	AND t1.region_name = t2.region_name;

优化

最近有使用hive进行数据转换，发现使用with…as…的写法进行子查询会比较易读。(搜了下mysql8.0之后的版本也支持了这种写法)

with base as (
	SELECT count( 1 ) cnt, item_name, region_name 
	FROM work_order 
	GROUP BY item_name, region_name
)
SELECT
	t1.*
FROM
	base t1,
	( SELECT region_name, max( cnt ) mt FROM base t GROUP BY region_name ) t2 
WHERE
	t1.cnt = t2.mt 
	AND t1.region_name = t2.region_name;

方法2：row_number() over( PARTITION BY 字段a ORDER BY 数量 DESC )

还是在使用hive时发现了一个不错的语法，在初步思路的基础上只用加一层就能得到最终结果。

SELECT
	* 
FROM
	(
	SELECT
		row_number() over ( PARTITION BY region_name ORDER BY cnt DESC ) rn,* 
	FROM
		( SELECT count( 1 ) cnt, item_name, region_name FROM work_order GROUP BY item_name, region_name ) t1 
	) t2 
WHERE
	rn < 2;

用到了row_number这个函数获取行数，并根据区划分组，数量倒序排列，这样每个分组下第一行就是我们要的结果。
目前mysql不支持这个函数，但可以通过自定义变量的方式达到类似的效果，后面有时间再补充。

后记

其实上面的查询方法感觉不够优雅，等后面遇到更好的解决思路再来更新吧。
2020-06-04 补充方法2