SQL笛卡尔积查询的典型应用

SQL笛卡尔积查询的典型应用

作者：AntoniotheFuture

关键词：SQL,笛卡尔积

开发平台：不限

开发语言：SQL

简介： 介绍一个利用SQL笛卡尔积查询来简化代码的典型应用

 

我的日常工作中经常用到SQL，但没用过笛卡尔积查询，以前一直以为他只会出现在逻辑出错的时候，而且通常意味着大量的资源开销，不过今天有幸能够使用一次，而且发现他能简化代码，分享给大家：

现在有一张员工信息表，记录着从创办公司到现在所有员工的信息：

 

人员代码	入职日期	离职日期
000001	2000-01-01
000002	2002-05-15	2008-04-09
000003	2005-09-10
000004	2005-10-01	2014-03-15
....	....	...

可能这时候就有DBA吐槽了，这个表不能这样设计，而是应该设计成任职记录表，每个员工多条记录，是我我也做成任职记录表，但是我们公司就是这样的

有一天老板心血来潮，他想看看我们公司的人力发展历史，就是统计公司自创办以来，每个月的在职人数，而且有个算法：当月入职或离职的算0.5个人，同月入职离职的不算在职。

以往我的思路是，将每个月的字段列出，然后分别判断该人员在当月是否在职，如果在职就为1，其他情况是0.5或者0，然后所有字段分别求和：

(case when to_date(入职日期,'yyyymm') < '200001' and to_date(离职日期,'yyyymm') >'200001' then
    1
when to_date(入职日期,'yyyymm') < '200001' and to_date(离职日期,'yyyymm') ='200001' then 
0.5
when to_date(入职日期,'yyyymm') < '200001' and to_date(离职日期,'yyyymm') <'200001' then 
0
when to_date(入职日期,'yyyymm') < '200001' and 离职日期 is null then 
1
when to_date(入职日期,'yyyymm') = '200001' and to_date(离职日期,'yyyymm') >'200001' then
0.5
when to_date(入职日期,'yyyymm') = '200001' and to_date(离职日期,'yyyymm') ='200001' then
0
when to_date(入职日期,'yyyymm') = '200001' and 离职日期 is null then
0.5
when to_date(入职日期,'yyyymm') > '200001' then
0
else
0
end) as 200001在职人力
(case when to_date(入职日期,'yyyymm') < '200002' and to_date(离职日期,'yyyymm') >'200002' then
    1
when to_date(入职日期,'yyyymm') < '200002' and to_date(离职日期,'yyyymm') ='200002' then 
0.5
when to_date(入职日期,'yyyymm') < '200002' and to_date(离职日期,'yyyymm') <'200002' then 
0
when to_date(入职日期,'yyyymm') < '200002' and 离职日期 is null then 
1
when to_date(入职日期,'yyyymm') = '200002' and to_date(离职日期,'yyyymm') >'200002' then
0.5
when to_date(入职日期,'yyyymm') = '200002' and to_date(离职日期,'yyyymm') ='200002' then
0
when to_date(入职日期,'yyyymm') = '200002' and 离职日期 is null then
0.5
when to_date(入职日期,'yyyymm') > '200002' then
0
else
0
end) as 200002在职人力
。。。。。

如果只求一两个月月到还好，但是现在要求所有月份，每年12个月12个字段，10年120个，代码虽然是规则的，但是也太长了，这么多字段，结果能不能输出还不一定呢。

于是我就想有没有优化的方案，灵光一闪，决定用笛卡尔积试试。

 

我们首先准备这样一张把所有月份列出来的表（可以从别的表group by而来）：

月份

200001

200002

200003

.....

然后直接和员工信息表做笛卡尔积，结果集是这样的：

select * from 员工信息,月份表

人员代码	入职日期	离职日期	月份
000001	2000-01-01		200001
000001	2000-01-01		200002
000001	2000-01-01		200003
000001	2000-01-01		200004
000001	2000-01-01		200005
000001	2000-01-01		200006
....	...	...	...

就相当于列出了每个人员在每个月的情况，这个时候我们只需要做一个字段，根据入职日期，离职日期和月份计算该人员在该月份是否在职即可：

 

(case when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') >月份 then
    1
when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') =月份 then 
0.5
when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') <月份 then 
0
when to_date(入职日期,'yyyymm') < 月份 and 离职日期 is null then 
1
when to_date(入职日期,'yyyymm') = 月份 and to_date(离职日期,'yyyymm') >月份 then
0.5
when to_date(入职日期,'yyyymm') = 月份 and to_date(离职日期,'yyyymm') =月份 then
0
when to_date(入职日期,'yyyymm') = 月份 and 离职日期 is null then
0.5
when to_date(入职日期,'yyyymm') > 月份 then
0
else
0
end) as 当月在职人力

得到的结果集是这样的：

 

人员代码	入职日期	离职日期	月份	当月在职人力
000001	2000-01-01		200001	0.5
000001	2000-01-01		200002	1
000001	2000-01-01		200003	1
000001	2000-01-01		200004	1
000001	2000-01-01		200005	1
000001	2000-01-01		200006	1
...	..	...	...	..

 

然后就可以用月份group by 起来啦：

完整的伪代码如下，根据环境和不同的需求自行修改~

Select 
    月份,
    sum(case when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') >月份 then
        1
        when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') =月份 then 
        0.5
        when to_date(入职日期,'yyyymm') < 月份 and to_date(离职日期,'yyyymm') <月份 then 
        0
        when to_date(入职日期,'yyyymm') < 月份 and 离职日期 is null then 
        1
        when to_date(入职日期,'yyyymm') = 月份 and to_date(离职日期,'yyyymm') >月份 then
        0.5
        when to_date(入职日期,'yyyymm') = 月份 and to_date(离职日期,'yyyymm') =月份 then
        0
        when to_date(入职日期,'yyyymm') = 月份 and 离职日期 is null then
        0.5
        when to_date(入职日期,'yyyymm') > 月份 then
        0
        else
        0
        end
    ) as 当月在职人力
from 员工信息表,月份表
group by 月份

结果示例：

月份	当月在职人力
201001	350
201002	364
201003	360
201004	372
.....	....