[抛砖引玉]换个思路解决SQL经典问题(二):时间区间-按年分月统计

声明:本篇文章的SQL语句为了体现作者的思路,并非最优,请根据实际需要进行优化。

曾经帮别人解决一个这样的问题:

e_class_table

要求按年和月分别统计全部课程的统计每个月的人天次,即:

class_total

计算方法为 (每门课在当月的有效天数*人数)的总和,如2014年01月的应该为 课程06的 2014-01-01~2014-01-09共计9天*44人=396人天 再加上 课程07的2014-01-01~2014-01-31共计31天*36人=1116人天,那么2014年01月的人天为 396+1116=1512人天次。

这个问题看上去挺麻烦的,但是我们可以换个思路去考虑,先建一张序号表:

那。。。这张序号表是干什么用的呢?看看下面就知道了:

通过这个语句,我们可以生成一个从2011年01月01日开始的4000天,我们将用到这个查询来将课程表的时间段,拆分到每一天去:

看看效果:

class_split

OK,这样,是不是就很明了了呢?“年”字段有了,“月”字段有了,“学生数”是不是就可以简单地加了?然后再一个行列转换就可以实现了(SQL2000请自行转为Case When End):

看到了吧?有时,引入一张外表,可以让我们的思路更清楚。

如果将

这句生成的查询,直接做成一张日期的序列表再加上索引的话,那在性能上会快更多,具体优化就不多讲了。

继续留个尾巴:

请只统计2012年07月16日到2013年06月15日之间,排除双休日的每个月的人天次。

One thought on “[抛砖引玉]换个思路解决SQL经典问题(二):时间区间-按年分月统计

发表评论

电子邮件地址不会被公开。 必填项已用*标注