sql - Oracle SQL 创建汇总总计
问题描述
我有以下测试数据,我可以使用一些帮助来生成以下输出
YEAR 2021
Week 30
1 Smith,Jane HRS 11 MIN 45 SEC 08
2 Doe,John HRS 07 MIN 01 SEC 11
Week Total HRS 18 MIN 46 SEC 19
Week 31
1 Smith,Jane HRS 13 MIN 36 SEC 01
2 Doe,John HRS 03 MIN 27 SEC 00
Week Total HRS 17 MIN 03 SEC 01
Year Total HRS 35 MIN 49 SEC 20
我想从 emp_attendance 表中提取每个唯一的 YYYY,然后为 YYYY(年份总计)创建总计。
在每个 YYYY 中(样本只有 2 个周数 30,31,但每年最多可以有 52 个)我想创建周数总计。
在每周数内,我想要每个employee_id 的总数。
我使用以下查询来获取小时、分钟、秒,但我不确定如何汇总数据以在上面的示例输出中生成我想要的几个总数。我更喜欢 ALL sql 解决方案。
select
e.employee_id
e.first_name,
e.last_name,
trunc(sum(a.end_date - a.start_date) * 24) hours,
trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,
round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds
from employees e,
emp_attendance a
where a.employee_id = e.employee_id
group by e.employee_id, e.first_name, e.last_name
order by e.employee_id, e.first_name,
e.last_name;
Emp Fname Lname hrs min sec
1 Jane Smith 25 21 9
2 John Doe 10 28 11
我的测试用例。
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
Create table employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20)
);
Insert into employees (employee_id,
first_name,
last_name) values
(1, 'Jane', 'Smith');
Insert into employees (employee_id,
first_name,last_name)
values(2, 'John', 'Doe');
CREATE TABLE emp_attendance(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
start_date DATE,
end_date DATE,
week_number NUMBER(2),
create_date DATE DEFAULT SYSDATE
);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (1, to_date('20210714 18:35:40','YYYYMMDD HH24:MI:SS'), to_date('20210715 02:34:56','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number) VALUES (1, to_date('20210715 19:37:10','YYYYMMDD HH24:MI:SS'), to_date('20210715 23:23:02','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, c start_date, end_date, week_number)VALUES (1, to_date('20210722 09:17:11','YYYYMMDD HH24:MI:SS'), to_date('20210722 22:53:12','YYYYMMDD HH24:MI:SS'),31);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)
VALUES (2, to_date('20210716 18:24:11','YYYYMMDD HH24:MI:SS'), to_date('20210717 01:25:22','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)
VALUES (2, to_date('20210722 09:17:11','YYYYMMDD HH24:MI:SS'), to_date('20210722 12:44:11','YYYYMMDD HH24:MI:SS'),31);
解决方案
该GROUPING SETS
子句可用于创建多个聚合,该GROUPING_ID
函数可用于识别每行属于哪个聚合。
例如:
select
to_char(a.start_date, 'YYYY') the_year,
week_number,
e.employee_id,
e.first_name,
e.last_name,
trunc(sum(a.end_date - a.start_date) * 24) hours,
trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,
round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds,
grouping_id(to_char(a.start_date, 'YYYY'), week_number, e.employee_id, e.first_name, e.last_name) the_grouping_id
from employees e
join emp_attendance a
on a.employee_id = e.employee_id
group by grouping sets
(
(to_char(a.start_date, 'YYYY'), week_number, e.employee_id, e.first_name, e.last_name),
(to_char(a.start_date, 'YYYY'), week_number),
(to_char(a.start_date, 'YYYY')),
()
)
order by week_number, employee_id;
THE_YEAR WEEK_NUMBER EMPLOYEE_ID FIRST_NAME LAST_NAME HOURS MINUTES SECONDS THE_GROUPING_ID
-------- ----------- ----------- ---------- --------- ----- ------- ------- ---------------
2021 30 1 Jane Smith 11 45 8 0
2021 30 2 John Doe 7 1 11 0
2021 30 18 46 19 7
2021 31 1 Jane Smith 13 36 1 0
2021 31 2 John Doe 3 27 0 0
2021 31 17 3 1 7
2021 35 49 20 15
35 49 20 31
这些结果包含您想要的数字,但不是您想要的确切格式。我假设您将使用报告工具进行格式化,但如果您想纯粹在 SQL 中执行此操作,您可以使用复杂CASE(WHEN THE_GROUPING_ID = 'X' THEN ...
的表达式来构建您想要的确切字符串。
推荐阅读
- javascript - 选项卡粘滞时转到另一个选项卡后不需要隐藏内容
- xml - CRM模块中的Many2one res.partner过滤器
- dictionary - 在 Flutter 中显示由 JSON 请求创建的标记的问题
- dart - 单击图标时,凸起的按钮大小发生了变化。如何解决这个问题?
- android - Xamarin HttpClient.PostAsync 死锁
- android - 在android中将联系人号码与firebase数据库电话号码进行比较时出错
- powershell - 在 PowerShell 中重命名文件名
- javascript - 通过预定义的属性名称数组获取对象属性
- javascript - 将前几天的大日历样式反应到当前日期
- windows - 通过 TERMINAL 关闭 Kafka