仅用SQL得到的复杂统计数据的例子

正好前不仅和朋友交流数据统计技术，就想写一个关于我如何进行统计的文章。在我们的一般的信息化项目中有这么几大块技术点：

1.后台J2EE技术架构。实际变化不大，多数时候用一个架构多年可以不变，加上脚手架代码，所以通常没有难度，也无法进一步提高工作效率。
2.前台技术。用的比较杂，多是基于jquery的框架，现在尽量用Bootstrap吧，easyUi也用，普通的jsp也用，多数情况最好用一套技术就行，除了有时候比较陌生，要查资料外，也无法进一步提高效率。
3.业务开发与设计，这个方面由于每个项目都不尽相同，多数情况下靠个人能力，重点是能用好流程处理技术，分析好主对象表，从对表表，业务基础表，业务数据表这些关系。这两方面，之前有写过分析的文章了。
4.统计是客户通常用的重要功能，一般我不认为这个技术上很重要，所以没太关注。后来看到很多统计都弄的很复杂，都是简单的查询，再返回复杂的对象在jvm中处理，花了不少时间在上面，觉得有必要针对这方面写一下，帮助开发人员提高工作效率。

[color=red][size=x-large] [b]特点：几乎都用纯SQL实现复杂的统计功能。[/b][/size][/color]。[color=blue][b]常用的sql函数有：group by, case when, group_concat(mysql)/listagg(oralce),decode, distinct,having 等。[/b][/color]

[size=large][b] 一、以具体示例表介绍，表之间的关系如下：[/b][/size]

1.一个学生表
[img]http://dl2.iteye.com/upload/attachment/0126/6357/f508532d-14f8-343c-9f42-1c528e8785f2.jpg[/img]

2.一个学生的奖惩情况表
[img]http://dl2.iteye.com/upload/attachment/0126/6359/6bd97e52-040c-389f-bb48-733f13039656.jpg[/img]
3.关系说明 学生表是主表，奖惩情况是从表，有一个FK是主表的PK，就是STUDENT_ID。很简单。从表也可以是多张表，比如每个学生的考试通过情况，每个学生参与班级活动的情况等等。很多统计表是把这些混合在一起的。

[b][size=large]二、第一类统计，每个学生的各种情况分类统计汇总[/size][/b]
[b]特点是，表格的一行是主表的一个对象。[/b]
[b]1.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6361/3d325262-1c26-3953-bde9-2fb26cdb9087.jpg[/img]

[b]2.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6363/406a1cf2-844c-3593-b805-c118c5d81533.jpg[/img]

[b]3.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6365/e5e35910-0f39-3a1e-ad45-b2e506b095db.jpg[/img]

[b]4.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6367/dcea4877-e2ba-33bc-9428-9d24dc4040c0.jpg[/img]

[b]5.一从表的多列统计[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6369/8fbd8a64-c946-36b5-9af6-75d831965307.jpg[/img]

[b]6.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6371/3ca4eb25-c6ed-3fb7-8625-7c8308e00fed.jpg[/img]

[b]7.多从表的情况[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6373/c650c4b5-1728-3d9e-bd4e-3965eefddd19.jpg[/img]


[size=large][b]三、第二类统计，某个分类的各种分类统计汇总[/b][/size]
[color=blue][b]特点是，表格是一行是主表对象的其中一个分类的汇总。[/b][/color]

[b]1.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6375/a484d3ac-a094-3569-81ec-7645ad0129d4.jpg[/img]

[b]2.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6377/3ead8e98-07e0-302f-80fb-58bd20bb7b36.jpg[/img]

[b]3.用left join进行统计[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6379/d3781718-95d9-368b-9796-a7f450cc9aa4.jpg[/img]

[b]4.[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6381/65e5c16a-b060-3634-b810-aaf081d79b9a.jpg[/img]

[b]5.从多个left join处理多个从表的统计[/b]
[img]http://dl2.iteye.com/upload/attachment/0126/6383/f1185dce-d7a1-3837-a610-5cf77fd5fce9.jpg[/img]

[color=red][b]6.不用left join，我个人尝试出来的统计方式。主表的字段组合用INSTR函数后，用于从表统计。[/b][/color]
[img]http://dl2.iteye.com/upload/attachment/0126/6385/c8df32c5-b33b-3a73-98ac-ef0ba9f14ed5.jpg[/img]


最后提一下最近碰到的需求，是要在一组里面随机抽取，按说group后里如果有一个random的函数就好了。但是没有，我的做法还是group_concat或者LISTAGG()取出这个字段后，外部程序用‘'’分割成数组，再从里面随机取一个值。

[size=large][b]四、总结[/b][/size]
[color=black][b]有些统计的分类是从字典表中得，或者直接用字典表，通过在jvm中循环拼接出来超长的sql，曾经生成过的sql超过2000字符也有。尽量使用SQL，充分利用了数据库本身的能力，虽然多，但不乱，非常的简单。可以节省很多开发时间。[/b][/color]