Oracle之分组合计、百分比详解ITWUYI的博客-

    日常工作中，我们常常需要开发报表或者统计一些数据的百分比、合计，这时候，下面的几个函数可以很快地解决问题 。它们不仅sql语句少，而且性能更好。下图即为我们这次要统计的结果展示。

    常用的函数如下：case when…then…else…end,  regexp_like,  ratio_to_report(score) OVER(),  rollup,  grouping,本文只是展示在sql中如何用这些函数巧妙地统计出这种效果，具体用法可自查阅资料了解更多细节。

    首先，需要你统计的东西，类似这样子：

    本人的表由于数据需要转换，所以需要使用下面的sql语句进行处理。如有不同，请跳过这一段。

 SELECT region, SUM(CASE region   WHEN 'Africa' THEN 1   WHEN 'Asia' THEN 1   WHEN 'Europe' THEN 1   WHEN 'NorthAmerica' THEN 1   WHEN 'Oceania' THEN 1   WHEN 'SouthAmerica' THEN 1   WHEN 'unkonwn' THEN 1   ELSE 0  END) AS score FROM (  SELECT CASE     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[A-C]') THEN 'Africa'    WHEN regexp_like(substr(TRANSDAY, 0, 1), '[J-R]') THEN 'Asia'    WHEN regexp_like(substr(TRANSDAY, 0, 1), '[S-Z]') THEN 'Europe'    WHEN regexp_like(substr(TRANSDAY, 0, 1), '[1-5]') THEN 'NorthAmerica'    WHEN regexp_like(substr(TRANSDAY, 0, 1), '[6-7]') THEN 'Oceania'    WHEN regexp_like(substr(TRANSDAY, 0, 1), '[8-9]') THEN 'SouthAmerica'    ELSE 'unkonwn'   END AS region  FROM test ) GROUP BY region

     接下来，你统计的数据展示可以再次转换为这样，即这一步可以求百分比：

    本人的数据需要转换，所以需要使用下面的sql语句进行处理。其实就是在这一步用ratio_to_report(aa.score) OVER()处理。如有不同，请跳过这一段。 

 SELECT aa.region, aa.score  , round(ratio_to_report(aa.score) OVER (), 4) AS percents FROM (  SELECT region, SUM(CASE region    WHEN 'Africa' THEN 1    WHEN 'Asia' THEN 1    WHEN 'Europe' THEN 1    WHEN 'NorthAmerica' THEN 1    WHEN 'Oceania' THEN 1    WHEN 'SouthAmerica' THEN 1    WHEN 'unkonwn' THEN 1    ELSE 0   END) AS score  FROM (   SELECT CASE      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[A-C]') THEN 'Africa'     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[J-R]') THEN 'Asia'     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[S-Z]') THEN 'Europe'     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[1-5]') THEN 'NorthAmerica'     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[6-7]') THEN 'Oceania'     WHEN regexp_like(substr(TRANSDAY, 0, 1), '[8-9]') THEN 'SouthAmerica'     ELSE 'unkonwn'    END AS region   FROM test  )  GROUP BY region ) aa ORDER BY aa.region

    接下来，就需要我们分组合计了，这个时候，rollup派上用场了。

    使用了rollup后，我们的数据展示变成了：

             下面的sql语句就是为了展示rollup使用后的效果，并非最后的效果，可以跳过这一段：           

 SELECT region, SUM(score) AS score, SUM(percents) AS percents FROM (  SELECT aa.region, aa.score   , round(ratio_to_report(aa.score) OVER (), 4) AS percents  FROM (   SELECT region, SUM(CASE region     WHEN 'Africa' THEN 1     WHEN 'Asia' THEN 1     WHEN 'Europe' THEN 1     WHEN 'NorthAmerica' THEN 1     WHEN 'Oceania' THEN 1     WHEN 'SouthAmerica' THEN 1     WHEN 'unkonwn' THEN 1     ELSE 0    END) AS score   FROM (    SELECT CASE       WHEN regexp_like(substr(TRANSDAY, 0, 1), '[A-C]') THEN 'Africa'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[J-R]') THEN 'Asia'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[S-Z]') THEN 'Europe'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[1-5]') THEN 'NorthAmerica'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[6-7]') THEN 'Oceania'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[8-9]') THEN 'SouthAmerica'      ELSE 'unkonwn'     END AS region    FROM test   )   GROUP BY region  ) aa  ORDER BY aa.region ) t_test GROUP BY region WITH ROLLUP;

    当我们使用了rollup后，我们发现，最下面的合计这一行，要进行合计的分类字段region最下面的值是为空的，所以，配合grouping这个函数使用，即将可以达到我们最后想要的效果。

    grouping函数可以接受一列，返回0或者1。如果列值为空，那么grouping()返回1；如果列值非空，那么返回0。grouping只能在使用rollup或cube的查询中使用。当需要在返回空值的地方显示某个值时，grouping()就非常有用。

   下图是最终的sql语句：

 SELECT CASE    WHEN grouping(region) = 1 THEN '合计'   ELSE region  END AS region, SUM(score) AS score, SUM(percents) AS percents FROM (  SELECT aa.region, aa.score   , round(ratio_to_report(aa.score) OVER (), 4) AS percents  FROM (   SELECT region, SUM(CASE region     WHEN 'Africa' THEN 1     WHEN 'Asia' THEN 1     WHEN 'Europe' THEN 1     WHEN 'NorthAmerica' THEN 1     WHEN 'Oceania' THEN 1     WHEN 'SouthAmerica' THEN 1     WHEN 'unkonwn' THEN 1     ELSE 0    END) AS score   FROM (    SELECT CASE       WHEN regexp_like(substr(TRANSDAY, 0, 1), '[A-C]') THEN 'Africa'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[J-R]') THEN 'Asia'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[S-Z]') THEN 'Europe'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[1-5]') THEN 'NorthAmerica'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[6-7]') THEN 'Oceania'      WHEN regexp_like(substr(TRANSDAY, 0, 1), '[8-9]') THEN 'SouthAmerica'      ELSE 'unkonwn'     END AS region    FROM test   )   GROUP BY region  ) aa  ORDER BY aa.region ) t_test GROUP BY region WITH ROLLUP;

    以上就是我们数据最后的展示效果。sql语句可以进一步优化，由于时间问题，后续有时间进一步优化。