1.前言
昨天遇到一个统计的问题，数据大概14w条左右,在Excel文件中，原本打算用之前学的python练练手，后来时间紧，就直接导到Oracle数据库里统计了，解决的过程挺有意思，特此记录如下：
2.需求
现有3000多家公司2008年-2016年 员工的信息；约有
14w条数据；存储在 Excel文件中；
为保护隐私此处数据为模拟数据
要求统计：
1.每个公司每年的员工数
2.每个公司每年的男、女员工数
3.每个公司每年的员工的平均年龄
sql语句在最后，如急需可跳至末尾
sql语句在最后，如急需可跳至末尾
sql语句在最后，如急需可跳至末尾
3.前导知识
3.1 case 一般语句
- case 语句带有选择效果知返回第一个条件满足要求的语句，即语句一语句二都的判断都为 true ，返回排在前面的。
- case 的语法根据放置的位置不同而不同。
CASE SELECTOR
WHEN EXPRESSION_1 THEN STATEMENT_1;
[WHEN EXPRESSION_2 THEN STATEMENT_2;]
[...]
[ELSE STATEMENT_N+1 ;]
END CASE;
- 注意 在then 后面需要 ； 分号，而且结束的时候 是 END CASE ;
CASE v_element
WHEN xx THEN yy;
WHEN xxx THEN yyy;
ELSE yyyy;
END CASE;
当v_element 等于 xx 时，执行 yy 语句，如果很长可以 前后加 begin 和 end，判断的条件是 v_element =xx ，xx是 具体值。
3.2搜索式 case 语句
CASE
WHEN SEARCH_CONDITION_1 THEN STATEMENT_1;
[WHEN SEARCH_CONDITION_1 THEN STATEMENT_2;]
[...]
[ELSE STATEMENT_N+1 ;]
END CASE;
CASE
WHEN v_element=xx THEN yy;
WHEN v_element=xxx THEN yyy;
ELSE yyyy;
END CASE;
4.问题分析
问题解决的难点在于：
每个公司每年的
4.1正常“两次分组”
比较直观的解决办法是：先按照
公司分组，再按照年分组 同时添加约束条件即可统计
统计出来的数据结果是这样的：
4.2“曲线两次分组”
如果要求一个公司的信息统计为一条数据
此时只能首先按照公司分组，分组之后，组内再利用case 语句“曲线实现分组”的效果
5.问题解决
5.1环境介绍
1.Oracle 11g 64位
2.PLSQL 11.0.3.1700
3.Microsoft Office 2013
5.2建表
create table gsygxx
(
gsdm VARCHAR2(16),
tjsj VARCHAR2(64),
xm VARCHAR2(32),
xb VARCHAR2(8),
nl NUMBER
)
5.3Excel数据导入
5.4正常“两次分组”源代码
先按照
公司分组，再按照年分组 得到的数据是每个公司每年的员工信息
直接添加约束条件，即可统计出结果
select
gsdm 公司代码,
tjsj 统计时间,
count(*) 总人数,
avg (nl) 平均年龄,
count(CASE WHEN xb='女' THEN 1 ELSE NULL END) 女,
count(CASE WHEN xb='男' THEN 1 ELSE NULL END) 男
from gsygxx
group by gsdm,tjsj
order by gsdm,tjsj;
5.5“曲线两次分组”源代码
1.按照
公司分组 得到的是每个公司的全部年份的全部员工信息：在添加约束条件时，需要额外的添加上年份的限制
2.为了满足一个公司的信息统计为一条数据，需要把不同年份的数据分别在一条sql语句中进行统计
select
gsdm 公司代码,
count(*) 总人数,
avg (CASE WHEN tjsj = '2008-12-31' THEN nl ELSE NULL END) 平均年龄_2008,
count (CASE WHEN tjsj = '2008-12-31' THEN 1 ELSE NULL END) 总人数_2008,
count (CASE WHEN tjsj = '2008-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2008_男,
count (CASE WHEN tjsj = '2008-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2008_女,
avg (CASE WHEN tjsj = '2009-12-31' THEN nl ELSE NULL END) 平均年龄_2009,
count (CASE WHEN tjsj = '2009-12-31' THEN 1 ELSE NULL END) 总人数_2009,
count (CASE WHEN tjsj = '2009-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2009_男,
count (CASE WHEN tjsj = '2009-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2009_女,
avg (CASE WHEN tjsj = '2010-12-31' THEN nl ELSE NULL END) 平均年龄_2010,
count (CASE WHEN tjsj = '2010-12-31' THEN 1 ELSE NULL END) 总人数_2010,
count (CASE WHEN tjsj = '2010-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2010_男,
count (CASE WHEN tjsj = '2010-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2010_女,
avg (CASE WHEN tjsj = '2011-12-31' THEN nl ELSE NULL END) 平均年龄_2011,
count (CASE WHEN tjsj = '2011-12-31' THEN 1 ELSE NULL END) 总人数_2011,
count (CASE WHEN tjsj = '2011-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2011_男,
count (CASE WHEN tjsj = '2011-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2011_女,
avg (CASE WHEN tjsj = '2012-12-31' THEN nl ELSE NULL END) 平均年龄_2012,
count (CASE WHEN tjsj = '2012-12-31' THEN 1 ELSE NULL END) 总人数_2012,
count (CASE WHEN tjsj = '2012-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2012_男,
count (CASE WHEN tjsj = '2012-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2012_女,
avg (CASE WHEN tjsj = '2013-12-31' THEN nl ELSE NULL END) 平均年龄_2013,
count (CASE WHEN tjsj = '2013-12-31' THEN 1 ELSE NULL END) 总人数_2013,
count (CASE WHEN tjsj = '2013-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2013_男,
count (CASE WHEN tjsj = '2013-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2013_女,
avg (CASE WHEN tjsj = '2014-12-31' THEN nl ELSE NULL END) 平均年龄_2014,
count (CASE WHEN tjsj = '2014-12-31' THEN 1 ELSE NULL END) 总人数_2014,
count (CASE WHEN tjsj = '2014-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2014_男,
count (CASE WHEN tjsj = '2014-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2014_女,
avg (CASE WHEN tjsj = '2015-12-31' THEN nl ELSE NULL END) 平均年龄_2015,
count (CASE WHEN tjsj = '2015-12-31' THEN 1 ELSE NULL END) 总人数_2015,
count (CASE WHEN tjsj = '2015-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2015_男,
count (CASE WHEN tjsj = '2015-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2015_女,
avg (CASE WHEN tjsj = '2016-12-31' THEN nl ELSE NULL END) 平均年龄_2016,
count (CASE WHEN tjsj = '2016-12-31' THEN 1 ELSE NULL END) 总人数_2016,
count (CASE WHEN tjsj = '2016-12-31' and xb='男' THEN 1 ELSE NULL END) 总人数_2016_男,
count (CASE WHEN tjsj = '2016-12-31' and xb='女' THEN 1 ELSE NULL END) 总人数_2016_女
from gsygxx
group by gsdm
order by gsdm;
5.5拓展
根据上面的分析和源代码，我们不难对其进行稍稍拓展，可以统计
1.每个公司每年男员工在某个年龄段（如 30-40岁）的数量
2.每个公司每年的男、女员工的平均年龄
3.每个公司每年的男、女员工数的比例
4.每个公司每年员工增加的个数（第一年不统计）
网友评论