英文:
How to calculate duration of timestamp when only given start_time and the end_time is the next row in start_time using PL SQL?
问题
我需要计算 end_time - start_time 的持续时间，但我只有 start_time，end_time 会在下一行的 start_time 中给出。我该如何使用 PL SQL 编写选择语句来获取 end_time？
当前表示例：
期望的表：
英文:
I have this data where I need to calculate the duration of end_time - start_time, but I am only given the start_time and the end_time would be the next row in start_time. How can I do the select statement to get the end_time using PL SQL?
Current Table Example:
Expected Table:
答案1
得分: 1
使用lead分析函数是一种选项，它返回下一个日期值（按start_time排序）；然后减去end - start以获得以天数表示的持续时间（因为当你减去两个date数据类型的值时，会得到这个）。如果你愿意，你可以格式化它，使它看起来更漂亮。
设置日期格式（这样我们就知道什么是什么）：
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
示例数据：
SQL> with test (product, start_time) as
2 (select 'A', to_date('01.07.2023 12:25:30', 'dd.mm.yyyy hh24:mi:ss') from dual union all
3 select 'B', to_date('01.07.2023 12:29:35', 'dd.mm.yyyy hh24:mi:ss') from dual union all
4 select 'C', to_date('01.07.2023 12:32:02', 'dd.mm.yyyy hh24:mi:ss') from dual
5 )
查询开始如下：
6 select
7 product,
8 start_time,
9 nvl(lead(start_time) over (order by start_time), start_time) as end_time,
10 --
11 nvl(lead(start_time) over (order by start_time), start_time) - start_time as duration_in_days
12 from test
13 order by start_time;
PRODUCT START_TIME END_TIME DURATION_IN_DAYS
-------- ------------------- ------------------- ----------------
A 01.07.2023 12:25:30 01.07.2023 12:29:35 ,002835648
B 01.07.2023 12:29:35 01.07.2023 12:32:02 ,001701389
C 01.07.2023 12:32:02 01.07.2023 12:32:02 0
英文:
One option is to use lead analytic function which returns the next date value (sorted by start_time); then subtract end - start to get duration which is measured in number of days (as that's what you get when you subtract two date datatype values). If you want, you can format it so that it looks prettier.
Setting date format (so that we'd know what is what):
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
Sample data:
SQL> with test (product, start_time) as
2 (select 'A', to_date('01.07.2023 12:25:30', 'dd.mm.yyyy hh24:mi:ss') from dual union all
3 select 'B', to_date('01.07.2023 12:29:35', 'dd.mm.yyyy hh24:mi:ss') from dual union all
4 select 'C', to_date('01.07.2023 12:32:02', 'dd.mm.yyyy hh24:mi:ss') from dual
5 )
Query begins here:
6 select
7 product,
8 start_time,
9 nvl(lead(start_time) over (order by start_time), start_time) as end_time,
10 --
11 nvl(lead(start_time) over (order by start_time), start_time) - start_time as duration_in_days
12 from test
13 order by start_Time;
PRODUCT START_TIME END_TIME DURATION_IN_DAYS
-------- ------------------- ------------------- ----------------
A 01.07.2023 12:25:30 01.07.2023 12:29:35 ,002835648
B 01.07.2023 12:29:35 01.07.2023 12:32:02 ,001701389
C 01.07.2023 12:32:02 01.07.2023 12:32:02 0
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论