ORACLE SQL使用窗口函数运行TOTAL和Daytotal [英] ORACLE SQL Running TOTAL and daytotal using window function
本文介绍了ORACLE SQL使用窗口函数运行TOTAL和Daytotal的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
从EMPLOYEE表中,我想对记录的数量(雇用的雇员)进行分组,并且还需要每天运行的TOTAL. 输入的格式如下:
From the EMPLOYEE table, I want to group the amount of records(employees hired) AND also have the running TOTAL per day. The format of the input is like this:
rownum Hired_date_time
1 1/10/2012 11:00
2 1/10/2012 13:00
3 20/11/2012 10:00
4 20/11/2012 15:00
5 20/11/2012 16:00
6 30/12/2012 1:00
所需的输出:
Hired_date.......Hired_per_day.........TOTAL_number_of_employees
1/10/2012 ...................2 ........2
20/11/2012 ..................3 ........5
30/12/2012 ..................1 ....... 6
每天分组"没问题:
select trunc(Hired_date_time) as "Hired_date" ,
count(*) as "Hired_per_day"
from employee
group by trunc(Hired_date_time)
order by trunc(Hired_date_time);
问题 :如何使用窗口函数
Question: how can I have a running total (in last column) using the window function
推荐答案
select trunc(hired),
count(*) hired_today,
sum(count(*)) over (order by trunc(hired)) as running_total
from emp
group by trunc(hired)
http://sqlfiddle.com/#!4/4bd36/9
这篇关于ORACLE SQL使用窗口函数运行TOTAL和Daytotal的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文