【Mysql】用套娃大法写个“合计”
年后公司做了新业务,小骨又开始重头写表。
这次依旧需要统计团队成员的工作业绩,不仅需要按人group by,最好还能按组小计,按全体合计(小计、合计需求是我自己加的)
之前在《》里写过用group by xxx with rollup来做,但是在数据不全(存在较多null)的情况下,with rollup似乎不太灵。
于是小骨又百度了一个更好的方法,不仅可以小计、合计,还可以把“小计”、“合计”字样给显示出来。
方法其实也很简单,就是活用Union自制小计和合计。
SELECT
u.u_group '组名',
u.u_name '组员',
sum(t.group_score)'小组积分',
sum(t.leader_score) '组长积分',
sum(t.happy_score) '喜悦积分'
FROM qyd_u u
LEFT JOIN qyd_t t on u.u_name = t.u_name
GROUP BY 1,2
这是我们按人聚合的每人积分总分。
现在我们要把按组聚合的积分表给算出来跟上表去Union。
所以要保证列数、列名都是相同的。
SELECT
q_t.`组名` '组名',
(CASE WHEN q_t.`组名` IS NOT NULL THEN '小计'
ELSE '合计' end) '组员',
sum(q_t.`小组积分`)'小组积分',
sum(q_t.`组长积分`)'组长积分',
sum(q_t.`喜悦积分`)'喜悦积分'
FROM
(SELECT
u.u_group '组名',
u.u_name '组员',
sum(t.group_score)'小组积分',
sum(t.leader_score) '组长积分',
sum(t.happy_score) '喜悦积分'
FROM qyd_u u
LEFT JOIN qyd_t t on u.u_name = t.u_name
GROUP BY 1,2) q_t
GROUP BY 1 with ROLLUP
用套娃大法把之前按人聚合的数据按组再聚合一次。
这里我们对“组员”字段做一个小处理,如果“组名”非空,就写“小计”,“组名”为空,就写“合计”。
最后,把两张表Union起来,再加一个order by,把“小计”放在每组最后,“合计”放在全表最后。
SELECT
u.u_group '组名',
u.u_name '组员',
sum(t.group_score)'小组积分',
sum(t.leader_score) '组长积分',
sum(t.happy_score) '喜悦积分'
FROM qyd_u u
LEFT JOIN qyd_t t on u.u_name = t.u_name
GROUP BY 1,2
UNION
SELECT
q_t.`组名` '组名',
(CASE WHEN q_t.`组名` IS NOT NULL THEN '小计'
ELSE '合计' end) '组员',
sum(q_t.`小组积分`)'小组积分',
sum(q_t.`组长积分`)'组长积分',
sum(q_t.`喜悦积分`)'喜悦积分'
FROM
(SELECT
u.u_group '组名',
u.u_name '组员',
sum(t.group_score)'小组积分',
sum(t.leader_score) '组长积分',
sum(t.happy_score) '喜悦积分'
FROM qyd_u u
LEFT JOIN qyd_t t on u.u_name = t.u_name
GROUP BY 1,2) q_t
GROUP BY 1 with ROLLUP
order by 1 desc,3
就成功啦~~~
拼了那么多表之后,小骨总结出来:
左右拼用left/right join,
上下拼用union
(对于很多数据大神来说,这种显而易见的规律都不用动脑子的吧)
不过,为了使数据适用metabase可视化图表,还真的得活用拼表!
例如用union拼出来的强调流失率的漏斗图。
Metabase做不了横向的漏斗图,只能做纵向的,有点儿像瀑布图了。
尽管这样,紫色的流失部分也可以很直观的表现出来,很赞了!
(只不过这个NB哄哄的代码不是我写的)