公司项目有一个需求，就是有一张故障表，要查询车道故障原因的的故障数，以及一个车道，在该车道的该故障类型的百分比，

说通俗点，假设 一个公司有10个部门，求每个部门每个月发工资的总和，同时计算每个部门每个月发工资的总和占全公司所发工资的百分比

因为涉及公司数据，这里就不把数据贴出来了，个人能力有限，写的不好，仅提供参考

如下图所示

想了很久，个人能力有限，也没有有写出来，最后想到了分而治之的思想，把复杂的功能拆分成简单的功能，然后在组合成想要的功能，虽然不一定是最优的方案，但总归是靠自己的能力写出来的

首先，先求出每个车道的故障数

SELECT
	'故障原因' AS "searchResult",
	f.ROAD_CODE AS "roadCode",
	s.ROAD_NAME AS "roadName",
	f.STATION_CODE AS "stationCode",
	s.STATION_NAME AS "stationName",
	f.LANE_CODE AS "laneCode",
	COUNT( f.FAULT_TYPE ) AS counts 
	TB_DS_FAULTINFO f
	LEFT JOIN TB_BASE_STATION s ON f.ROAD_CODE = s.ROAD_CODE 
	AND f.STATION_CODE = s.STATION_CODE 
WHERE
	f.FAULT_TYPE = '1' 
GROUP BY
	f.ROAD_CODE,
	s.ROAD_NAME,
	f.STATION_CODE,
	s.STATION_NAME,
	f.LANE_CODE

那还有一个分组后的总数

先别急，上面的sql,先通过 WHERE 后面的条件进行过滤，然后进行分组；所以，无论对过滤后的数据怎么分组，那么，过滤后的数据的总数是不变的

那么，分组后的总数sql是：

SELECT
    COUNT( f.ID ) 
    TB_DS_FAULTINFO f
    LEFT JOIN TB_BASE_STATION s ON f.ROAD_CODE = s.ROAD_CODE 
    AND f.STATION_CODE = s.STATION_CODE 
WHERE
    f.FAULT_TYPE = '1'

所以，组合起来最终的sql是

SELECT
	'故障原因' AS "searchResult",
	f.ROAD_CODE AS "roadCode",
	s.ROAD_NAME AS "roadName",
	f.STATION_CODE AS "stationCode",
	s.STATION_NAME AS "stationName",
	f.LANE_CODE AS "laneCode",
	COUNT( f.FAULT_TYPE ) AS counts,
	(SELECT	COUNT( f.ID ) 	FROM TB_DS_FAULTINFO f	LEFT JOIN TB_BASE_STATION s ON 
    f.ROAD_CODE = s.ROAD_CODE 	
	AND f.STATION_CODE = s.STATION_CODE 
	WHERE f.FAULT_TYPE = '1' ) AS total 
	TB_DS_FAULTINFO f
	LEFT JOIN TB_BASE_STATION s ON f.ROAD_CODE = s.ROAD_CODE 
	AND f.STATION_CODE = s.STATION_CODE 
WHERE
	f.FAULT_TYPE = '1' 
GROUP BY
	f.ROAD_CODE,
	s.ROAD_NAME,
	f.STATION_CODE,
	s.STATION_NAME,
	f.LANE_CODE

所以最终的查询结果是：

现在故障的原因总数是有了，每个车道的故障原因的数量有了，那计算百分比就简单多了，这里就不写了

更高效的处理方式

使用专用的比例函数

sum() over ( )

ratio_to_report () over ( )

SELECT
	'故障原因' AS "searchResult",
	f.ROAD_CODE AS "roadCode",
	s.ROAD_NAME AS "roadName",
	f.STATION_CODE AS "stationCode",
	s.STATION_NAME AS "stationName",
	f.LANE_CODE AS "laneCode",
	COUNT( * ) AS "counts",
	sum( COUNT( * ) ) over ( ) AS "total",
	round( ratio_to_report ( COUNT( * ) ) over ( ) * 100, 2 ) || '%' AS "proportion" 
	TB_DS_FAULTINFO f
	LEFT JOIN TB_BASE_STATION s ON f.ROAD_CODE = s.ROAD_CODE 
	AND f.STATION_CODE = s.STATION_CODE 
WHERE
	f.FAULT_TYPE = '1' 
GROUP BY
	f.ROAD_CODE,
	s.ROAD_NAME,
	f.STATION_CODE,
	s.STATION_NAME,
	f.LANE_CODE