SQL高级分组＆amp;案例语句，这可能吗？

发布于 2025-02-09 08:50:59 字数 2461 浏览 2 评论 0原文

我有以下方案

系统	子系统＆amp;文件名	文件加载启动时间	文件加载终止时间
alpha	a1 trassactionTXT	2022-06-19 08:00:00	2022-06-19 08:00:02
Alpha	A2 Alpha A2 usercsv	2022-06-19	08:00:00: 02 2022-2022-06-19 08:00:05
Alpha	A2员工	CSV 2022-06-19 08:00:05	2022-06-19 08:00:08
Alpha	A1 ManagersCSV	2022-06-19 08:00:08	20222-06-06-06-19 08:00： 16
Alpha	A3客户	CSV 2022-06-19 08:00:01	2022-06-19 08:00:04
GAMMA	A1 TRASSACTIONTXT	2022-06-19 10:00:48	2022-06-06-19
00:00:00: 003	10 :	2022-06-19 10:00:53	2022-06-19 10:00:54
GAMMA	A2 Emplayscsv	2022-06-19 10:00:27	2022-06-19 10:00:00:30
Gamma	A1 ManagersCSV	2022-06--06--06--06-- 19 10:00:11	2022-06-19 10:00:17
Gamma	A3 Customerscsv	2022-06-19 10:00:00	: 13 2022-06-19 10:00:14

我想能够通过系统。所需的信息是何时开始（最早的时间），何时结束（最新时间）以及每个子系统在几秒钟内发生的时间。从上面的示例中，结果应如下：

系统	整体系统负载启动时间	总体系统负载终止时间	a1花费	A2时间	A3时间为
Alpha	2022-06-19 08:00：00：00	2022-06-19 08： 00:16	00:00:10	00:00:06	00:00:03
伽马	2022-06-19	:11 2022-06-19	10:00 :	10:00:54	00 ：00：01

我找不到在查询中做到这一点的方法，我试图在每列的选择子句中选择选项，而仅按系统进行末端组。但这是不可能的，因为我必须使用一个汇总函数，而在“选择”条款中，

我的方法

SELECT System, 
min(StartTime) as 'File Load Start Time',
max(EndTime) as 'File Load End Time', 
CASE WHEN SubSystem LIKE 'A1%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A1 Time Taken',
CASE WHEN SubSystem LIKE 'A2%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A2 Time Taken',
CASE WHEN SubSystem LIKE 'A3%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A3 Time Taken'
FROM TABLE GROUP BY SYSTEM

在我的方法中不支持案例语句，但这是不起作用的条款也是我无法汇总的

原文

I have the following scenario

System	Subsystem & Filename	File Load Start Time	File Load End Time
Alpha	A1 transactiontxt	2022-06-19 08:00:00	2022-06-19 08:00:02
Alpha	A2 userscsv	2022-06-19 08:00:02	2022-06-19 08:00:05
Alpha	A2 employeescsv	2022-06-19 08:00:05	2022-06-19 08:00:08
Alpha	A1 managerscsv	2022-06-19 08:00:08	2022-06-19 08:00:16
Alpha	A3 customerscsv	2022-06-19 08:00:01	2022-06-19 08:00:04
Gamma	A1 transactiontxt	2022-06-19 10:00:48	2022-06-19 10:00:53
Gamma	A2 userscsv	2022-06-19 10:00:53	2022-06-19 10:00:54
Gamma	A2 employeescsv	2022-06-19 10:00:27	2022-06-19 10:00:30
Gamma	A1 managerscsv	2022-06-19 10:00:11	2022-06-19 10:00:17
Gamma	A3 customerscsv	2022-06-19 10:00:13	2022-06-19 10:00:14

I want to be able to group the summary statistics by System. The info needed is when the overall started (earliest time), when it ended (latest time), and the time it took for each subsystem to occur, in seconds. From the example above, the result should look as below:

System	Overall System Load Start Time	Overall System Load End Time	A1 Time Taken	A2 Time Taken	A3 Time Taken
Alpha	2022-06-19 08:00:00	2022-06-19 08:00:16	00:00:10	00:00:06	00:00:03
Gamma	2022-06-19 10:00:11	2022-06-19 10:00:54	00:00:11	00:00:04	00:00:01

I cannot find a way to do this in a query, I'm trying to do select subqueries in the select clause for each column, and at the end group by only System. But this is not possible because I'd have to use an aggregate function which is not being supported with case statements in subqueries in the select clause

My approach was something like

SELECT System, 
min(StartTime) as 'File Load Start Time',
max(EndTime) as 'File Load End Time', 
CASE WHEN SubSystem LIKE 'A1%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A1 Time Taken',
CASE WHEN SubSystem LIKE 'A2%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A2 Time Taken',
CASE WHEN SubSystem LIKE 'A3%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A3 Time Taken'
FROM TABLE GROUP BY SYSTEM

But this does not work because the case statements need to be in a group by clause as well, and I cannot aggregate them

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

罗罗贝儿 2025-02-16 08:50:59

您的原始查询只需要进行一些调整即可正常工作。我在工作台上对其进行了测试。现在起作用。

SELECT System, 
    min(StartTime) as 'File Load Start Time',
    max(EndTime) as 'File Load End Time', 
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a1' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A1 Time Taken',
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a2' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A2 Time Taken',
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a3' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A3 Time Taken'
FROM test  GROUP BY SYSTEM
;

Your original query just needs a little bit of tweaking in order to work properly. I tested it in workbench. It works now.

SELECT System, 
    min(StartTime) as 'File Load Start Time',
    max(EndTime) as 'File Load End Time', 
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a1' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A1 Time Taken',
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a2' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A2 Time Taken',
    sec_to_time(sum( case when substring_index(subsystem,' ',1)='a3' then to_seconds(endtime)-to_seconds(starttime) else 0 end 
    ) )as 'A3 Time Taken'
FROM test  GROUP BY SYSTEM
;

回复收藏 0 原文

治碍 2025-02-16 08:50:59

我假设您的子系统是有限的，不需要动态列（枢轴/crosstab）。

Folloing查询应为您提供所需的输出。

SELECT
    system_name AS "System", 
    min(file_load_start_time) AS "Overall System Load Start Time", 
    max(file_load_end_time) AS "Overall System Load End Time",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a1.file_load_start_time, tsl_a1.file_load_end_time)))
    FROM
        t_system_log tsl_a1
    WHERE
        tsl_a1.system_name = tsl.system_name
        AND LEFT(tsl_a1.subsystem_filename,
        2) = 'A1') AS "A1 Time Taken",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a2.file_load_start_time, tsl_a2.file_load_end_time)))
    FROM
        t_system_log tsl_a2
    WHERE
        tsl_a2.system_name = tsl.system_name
        AND LEFT(tsl_a2.subsystem_filename,
        2) = 'A2') AS "A2 Time Taken",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a3.file_load_start_time, tsl_a3.file_load_end_time)))
    FROM
        t_system_log tsl_a3
    WHERE
        tsl_a3.system_name = tsl.system_name
        AND LEFT(tsl_a3.subsystem_filename,
        2) = 'A3') AS "A3 Time Taken"
FROM
    t_system_log tsl
GROUP BY
    system_name;

如果您的子系统是动态的，则应使用枢轴查询而不是子查询。子查询可能会影响性能。

”
将所有子征服转换为内联，以提高性能。
还更新了我的小提琴。

SELECT
    system_name AS "System", 
    min(file_load_start_time) AS "Overall System Load Start Time", 
    max(file_load_end_time) AS "Overall System Load End Time",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A1' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A1 Time Taken",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A2' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A2 Time Taken",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A3' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A3 Time Taken"
FROM
    t_system_log tsl
GROUP BY
    system_name;

I am assuming your subsystems are finite and doesn't require dynamic columns (pivot/crosstab).

Folloing query should give you the desired output.

SELECT
    system_name AS "System", 
    min(file_load_start_time) AS "Overall System Load Start Time", 
    max(file_load_end_time) AS "Overall System Load End Time",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a1.file_load_start_time, tsl_a1.file_load_end_time)))
    FROM
        t_system_log tsl_a1
    WHERE
        tsl_a1.system_name = tsl.system_name
        AND LEFT(tsl_a1.subsystem_filename,
        2) = 'A1') AS "A1 Time Taken",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a2.file_load_start_time, tsl_a2.file_load_end_time)))
    FROM
        t_system_log tsl_a2
    WHERE
        tsl_a2.system_name = tsl.system_name
        AND LEFT(tsl_a2.subsystem_filename,
        2) = 'A2') AS "A2 Time Taken",
    (
    SELECT
        sec_to_time(sum(timestampdiff(SECOND, tsl_a3.file_load_start_time, tsl_a3.file_load_end_time)))
    FROM
        t_system_log tsl_a3
    WHERE
        tsl_a3.system_name = tsl.system_name
        AND LEFT(tsl_a3.subsystem_filename,
        2) = 'A3') AS "A3 Time Taken"
FROM
    t_system_log tsl
GROUP BY
    system_name;

In case, your subsystems are dynamic, you should use pivot query instead of subquery. Also subquery might impact on the performance.

My Fiddle

EDIT:
Converting all the sub-queries to inline to improve performance.
Updated my Fiddle also.

Updated Fiddle

SELECT
    system_name AS "System", 
    min(file_load_start_time) AS "Overall System Load Start Time", 
    max(file_load_end_time) AS "Overall System Load End Time",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A1' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A1 Time Taken",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A2' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A2 Time Taken",
    sec_to_time(sum(case when  LEFT(tsl.subsystem_filename, 2) = 'A3' then timestampdiff(SECOND, tsl.file_load_start_time, tsl.file_load_end_time) else 0 end)) as "A3 Time Taken"
FROM
    t_system_log tsl
GROUP BY
    system_name;

回复收藏 0 原文

~没有更多了~