ORACLE SQL 使用窗口函数运行 TOTAL 和 daytotal
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13702693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE SQL Running TOTAL and daytotal using window function
提问by user1875323
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:
从 EMPLOYEE 表中，我想对记录数量（雇用的员工）进行分组，并且每天还要运行 TOTAL。输入的格式是这样的：
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
The desired output:
所需的输出：
Hired_date.......Hired_per_day.........TOTAL_number_of_employees 1/10/2012 ...................2 ........2 20/11/2012 ..................3 ........5 30/12/2012 ..................1 ....... 6
No problem for the GROUPING PER DAY:
GROUPING PER DAY 没问题：
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
问题：如何使用窗口函数获得运行总数（在最后一列中）
回答by Kirill Leontev
select trunc(hired),
count(*) hired_today,
sum(count(*)) over (order by trunc(hired)) as running_total
from emp
group by trunc(hired)
回答by a_horse_with_no_name
select trunc(hire_date),
count(*) over (partition by trunc(hire_date)) as hired_per_day,
count(*) over (order by hire_date) as total_number_of_employees
from employee
order by trunc(hire_date)
