oracle 3个实用统计sql场景

我们使用oracle做一些统计的时候，时常碰到如下场景：

1.竖列转横列

2.分组并合并某列作为结果集

3.分组排序取首条记录

 

我们使用一个简化的业务场景，来展示这三个场景如何使用sql来解决。

业务场景：一张表记录着员工的出勤记录

业务需求：（对应上面的三个场景）

1.统计员工某年的每月出勤记录数

2.查询每个人的出勤记录

3.获得每个员工第一天上班的出勤记录

 

首先我们先创建测试数据表和测试数据

 

--创建考勤记录表
CREATE TABLE T_ATTENDANCE_LOG
(  
    ID_ VARCHAR(36),  
    USERNAME_ VARCHAR(255),  
    LOGDATE_ VARCHAR(100)
)  


--初始化一些测试数据
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('1','张三','2014-02-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('2','张三','2014-02-02');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('3','张三','2014-02-03');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('4','张三','2014-02-04');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('5','张三','2014-02-05');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('6','张三','2014-02-06');

insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('11','李四','2014-03-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('12','李四','2014-04-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('13','李四','2014-05-01');

insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('21','王五','2014-02-15');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('22','王五','2014-03-15');

--查询
SELECT T.*,T.ROWID FROM  T_ATTENDANCE_LOG T;

 

结果：

 

 

1.统计员工2014年的每月出勤情况

with sql1 as
(
select USERNAME_,substr(LOGDATE_,0,7) as a,count(LOGDATE_) as b from T_ATTENDANCE_LOG
group by USERNAME_,substr(LOGDATE_,0,7)
)
select USERNAME_, 
sum(case A when '2014-01' then B end) 一月,
sum(case A when '2014-02' then B end) 二月,
sum(case A when '2014-03' then B end) 三月,
sum(case A when '2014-04' then B end) 四月,
sum(case A when '2014-05' then B end) 五月,  
sum(case A  when '2014-06' then B  end) 六月, 
sum(case A  when '2014-07' then B  end) 七月, 
sum(case A  when '2014-08' then B  end) 八月, 
sum(case A  when '2014-09' then B  end) 九月, 
sum(case A  when '2014-10' then B  end) 十月, 
sum(case A  when '2014-11' then B  end) 十一月, 
sum(case A  when '2014-12' then B  end) 十二月
from sql1  group by USERNAME_

 这里用到“sql统计利器”--with。

 

结果：

 

 

2.查询每个人的出勤记录

select USERNAME_ as 员工,wmsys.wm_concat(LOGDATE_) as 出勤记录 from T_ATTENDANCE_LOG t group by  USERNAME_

 

结果：

 

但是我们发现这个统计出来的结果是乱序，改造一下

select USERNAME_ as 员工, max(r) as 出勤记录 from (
select USERNAME_,wmsys.wm_concat(LOGDATE_) OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r
from T_ATTENDANCE_LOG t 
)group by USERNAME_

 

改造结果：

 

 

3.获得每个员工第一天上班的出勤记录

SELECT * FROM 
(
--分组排序加序号
select USERNAME_,LOGDATE_,ROW_NUMBER() OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r
from T_ATTENDANCE_LOG t 
group by USERNAME_,LOGDATE_
) where R=1

 

结果：

 

 

评论

9 楼 周茂恩 2014-05-19  

min()这个挺不符合分析函数的语法的[function() over (<partition by ...><order by ...><...>)],另外，ROW_NUMBER和RANK以及DENSE_RANK是同一组的，ROW_NUMBER就是普通意义上的ROWNUM(123),而RANK是113，DENSE_RANK则是112.
注: 112,113,123就是排名中两个并列第一名，接下来是第二名。两个并列第一名，接下来是第三名和第一，二，三名，没有并列的。

snkcxy 写道

周茂恩 写道

确实first_value() 那样写 没有问题，但是 我试last_value() 不加 那句话就有问题。
我用这两个函数确实比较少，欢迎讨论~
min()在这个业务逻辑下 确实是一个不错的选择，但是如果需要展示的字段比较多（比如：我需要展示id_），那min()这种方式,是不是还要再关联一次自己（因为不可能按照每个字段进行group by），是不是这种情况下ROW_NUMBER()写法 就有些优势了~

8 楼 snkcxy 2014-05-19  

周茂恩 写道

确实first_value() 那样写 没有问题，但是 我试last_value() 不加 那句话就有问题。
我用这两个函数确实比较少，欢迎讨论~
min()在这个业务逻辑下 确实是一个不错的选择，但是如果需要展示的字段比较多（比如：我需要展示id_），那min()这种方式,是不是还要再关联一次自己（因为不可能按照每个字段进行group by），是不是这种情况下ROW_NUMBER()写法 就有些优势了~

7 楼 hdwmp123 2014-05-19  

不错的分享

6 楼 周茂恩 2014-05-19  

哈哈
select USERNAME_, min(LOGDATE_) keep (dense_rank first order by LOGDATE_ asc) first
from T_ATTENDANCE_LOG
group by USERNAME_;


select USERNAME_, min(LOGDATE_)
from T_ATTENDANCE_LOG
group by USERNAME_;

5 楼 周茂恩 2014-05-19  

SORRY,还是错了
SELECT DISTINCT USERNAME_, FIRST_VALUE(LOGDATE_）OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_ ASC)
FROM T_ATTENDANCE_LOG
GROUP BY USERNAME_, LOGDATE_;

周茂恩 写道

1. 后面的Windowing Clause不是很常用，也不是必须的。
2. 我忘记加distinct了，正确的结果应该是
    SELECT DISTINCT USERNAME_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;
3. 另外一种
     SELECT DISTINCT USERNAME_, MIN(LOGDATE_）OVER(PARTITION BY  USERNAME_ ORDER BY TO_DATE(LOGDATE_, 'YYYY-MM-DD') ASC)
FROM T_ATTENDANCE_LOG;

snkcxy 写道

周茂恩 写道

SELECT USERNAME_, LOGDATE_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;

感谢讨论~
first_value()版本：

select distinct tt.username_,tt.f
from(
select t.*,
       first_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) F,
       last_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) L
  from T_ATTENDANCE_LOG T
 order by USERNAME_
 ) tt;

个人觉得oracle的first_value 使用还是有些麻烦的，需要加一句ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则出来的数据和预想的不太一样。

4 楼 周茂恩 2014-05-19  

1. 后面的Windowing Clause不是很常用，也不是必须的。
2. 我忘记加distinct了，正确的结果应该是
    SELECT DISTINCT USERNAME_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;
3. 另外一种
     SELECT DISTINCT USERNAME_, MIN(LOGDATE_）OVER(PARTITION BY  USERNAME_ ORDER BY TO_DATE(LOGDATE_, 'YYYY-MM-DD') ASC)
FROM T_ATTENDANCE_LOG;

snkcxy 写道

周茂恩 写道

SELECT USERNAME_, LOGDATE_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;

感谢讨论~
first_value()版本：

select distinct tt.username_,tt.f
from(
select t.*,
       first_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) F,
       last_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) L
  from T_ATTENDANCE_LOG T
 order by USERNAME_
 ) tt;

个人觉得oracle的first_value 使用还是有些麻烦的，需要加一句ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则出来的数据和预想的不太一样。

3 楼 snkcxy 2014-05-19  

周茂恩 写道

SELECT USERNAME_, LOGDATE_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;

感谢讨论~
first_value()版本：

select distinct tt.username_,tt.f
from(
select t.*,
       first_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) F,
       last_value(LOGDATE_) over(partition by USERNAME_ order by LOGDATE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) L
  from T_ATTENDANCE_LOG T
 order by USERNAME_
 ) tt;

个人觉得oracle的first_value 使用还是有些麻烦的，需要加一句ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则出来的数据和预想的不太一样。

2 楼 周茂恩 2014-05-19  

SELECT USERNAME_, LOGDATE_, FIRST_VALUE(LOGDATE_）OVER( ORDER BY USERNAME_)
FROM T_ATTENDANCE_LOG;

1 楼 snkcxy 2014-05-18  

如果大家有什么更简单或性能更好的方法 欢迎讨论~ 相互学习~