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

时间：2022-09-16 12:51:49

1.前言

昨天遇到一个统计的问题，数据大概14w条左右,在Excel文件中，原本打算用之前学的python练练手，后来时间紧，就直接导到Oracle数据库里统计了，解决的过程挺有意思，特此记录如下：

2.需求

现有3000多家公司2008年-2016年 员工的信息；约有14w条数据；存储在 Excel文件中；

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

要求统计：
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正常“两次分组”

比较直观的解决办法是：先按照公司分组，再按照年分组 同时添加约束条件即可统计

统计出来的数据结果是这样的：

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

4.2“曲线两次分组”

如果要求一个公司的信息统计为一条数据

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

此时只能首先按照公司分组，分组之后，组内再利用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数据导入

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

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

标签：

相关文章