我现在有个需求就是，分组的同时，在每一列的后面，加一列该分组规则的总数量

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

方式一：

下面的sql, 根据
f.ROAD_CODE,
s.ROAD_NAME,
f.STATION_CODE,
s.STATION_NAME,
f.LANE_CODE
这几个字段进行分组

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 
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' 
GROUP BY
    f.ROAD_CODE,
    s.ROAD_NAME,
    f.STATION_CODE,
    s.STATION_NAME,
    f.LANE_CODE

查询结果

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

那么，总数的sql是：

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'

查询结果

所以，组合起来最终的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 
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' 
GROUP BY
    f.ROAD_CODE,
    s.ROAD_NAME,
    f.STATION_CODE,
    s.STATION_NAME,
    f.LANE_CODE

所以最终的查询结果是：

方式2：使用专用的比例函数

SELECT
    t.*,
     sum(t."counts") over() as 总合计,
    round( ratio_to_report ( t."counts" ) over ( ) * 100, 2 ) || '%' AS 占比 
FROM
    (
    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" 
    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' 
    GROUP BY
        f.ROAD_CODE,
        s.ROAD_NAME,
        f.STATION_CODE,
        s.STATION_NAME,
    f.LANE_CODE 
    ) t

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" 
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' 
GROUP BY
    f.ROAD_CODE,
    s.ROAD_NAME,
    f.STATION_CODE,
    s.STATION_NAME,
    f.LANE_CODE