Oracle统计每个公司每年的员工信息——另类“两次分组”

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.每个公司每年员工增加的个数（第一年不统计）