关于前端:SQL-grouping

SQL grouping 解决 OLAP 场景总计与小计问题，其语法分为几类，但要解决的是同一个问题：

ROLLUP 与 CUBE 是封装了规定的 GROUPING SETS，而 GROUPING SETS 则是最原始的规定。

为了不便了解，让咱们从一个问题动手，层层递进吧。

底表

以上是示例底表，共有 8 条数据，城市 1、城市 2 两个城市，上面各有地区 1～4，每条数据都有该数据的人口数。

当初想计算人口总计，以及各城市人口小计。在没有把握 grouping 语法前，咱们只能通过两个 select 语句 union 后失去：

SELECT city, sum(people) FROM test GROUP BY city
union
SELECT '共计' as city, sum(people) FROM test

但两条 select 语句聚合了两次，性能是一个不小的开销，因而 SQL 提供了 GROUPING SETS 语法解决这个问题。

GROUPING SETS

GROUP BY GROUPING SETS 能够指定任意聚合项，比方咱们要同时计算总计与分组共计，就要依照空内容进行 GROUP BY 进行一次 sum，再依照 city 进行 GROUP BY 再进行一次 sum，换成 GROUPING SETS 形容就是：

SELECT 
city, area,
sum(people)
FROM test
GROUP BY GROUPING SETS((), (city, area))

其中 GROUPING SETS((), (city, area)) 示意别离依照 ()、(city, area) 聚合计算总计。返回后果是：

能够看到，值为 NULL 的行就是咱们要的总计，其值是没有任何 GROUP BY 限度算进去的。

相似的，咱们还能够写 GROUPING SETS((), (city), (city, area), (area)) 等任意数量、任意组合的 GROUP BY 条件。

通过这种规定计算的数据咱们称为“超级分组记录”。咱们发现“超级分组记录”产生的 NULL 值很容易和真正的 NULL 值弄混，所以 SQL 提供了 GROUPING 函数解决这个问题。

函数 GROUPING

对于超级分组记录产生的 NULL，是能够被 GROUPING() 函数辨认为 1 的：

SELECT 
GROUPING(city),
GROUPING(area),
sum(people)
FROM test
GROUP BY GROUPING SETS((), (city, area))

具体成果见下图：

能够看到，但但凡超级分组计算出来的字段都会辨认为 1，咱们利用之前学习的 SQL CASE 表达式 将其转换为总计、小计字样，就能够得出一张数据分析表了：

SELECT 
CASE WHEN GROUPING(city) = 1 THEN '总计' ELSE city END,
CASE WHEN GROUPING(area) = 1 THEN '小计' ELSE area END,
sum(people)
FROM test
GROUP BY GROUPING SETS((), (city, area))

而后前端表格展现时，将第一行“总计”、“小计”单元格合并为“总计”，就实现了总计这个 BI 可视化剖析性能。

ROLLUP

ROLLUP 是卷起的意思，是一种特定规定的 GROUPING SETS，以下两种写法是等价的：

SELECT sum(people) FROM test
GROUP BY ROLLUP(city)

-- 等价于
SELECT sum(people) FROM test
GROUP BY GROUPING SETS((), (city))

再看一组等价形容：

SELECT sum(people) FROM test
GROUP BY ROLLUP(city, area)

-- 等价于
SELECT sum(people) FROM test
GROUP BY GROUPING SETS((), (city), (city, area))

发现法则了吗？ROLLUP 会按程序把 GROUP BY 内容“一个个卷起来”。用 GROUPING 函数判断超级分组记录对 ROLLUP 同样实用。

CUBE

CUBE 又有所不同，它对内容进行了所有可能性开展（所以叫 CUBE）。

类比下面的例子，咱们再写两组等价的开展：

SELECT sum(people) FROM test
GROUP BY CUBE(city)

-- 等价于
SELECT sum(people) FROM test
GROUP BY GROUPING SETS((), (city))

下面的例子因为只有一项还看不出来，上面两项分组就能看进去了：

SELECT sum(people) FROM test
GROUP BY CUBE(city, area)

-- 等价于
SELECT sum(people) FROM test
GROUP BY GROUPING SETS((), (city), (area), (city, area))

所谓 CUBE，是一种多维形态的形容，二维时有 2^1 种开展，三维时有 2^2 种开展，四维、五维依此类推。能够设想，如果用 CUBE 形容了很多组合，复杂度会爆炸。

总结

学习了 GROUPING 语法，当前前端同学的你不会再纠结这个问题了吧：

产品开启了总计、小计，咱们是额定取一次数还是放到一起获取啊？

这个问题的标准答案和原理都在这篇文章里了。PS：对于不反对 GROUPING 语法数据库，要想方法屏蔽，就像前端 polyfill 一样，是一种降级计划。至于如何屏蔽，参考文章结尾提到的两个 SELECT + UNION。

探讨地址是：精读《SQL grouping》· Issue #406 · ascoders/weekly

如果你想参加探讨，请 点击这里，每周都有新的主题，周末或周一公布。前端精读 – 帮你筛选靠谱的内容。

关注 前端精读微信公众号

<img width=200 src=”https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg”>

版权申明：自在转载 - 非商用 - 非衍生 - 放弃署名（创意共享 3.0 许可证）