英文:
How to add a column that contains the next date column with SQL oracle developper
问题
我想要在我的表格中添加一列，该列包含了下一个出生日期（按出生日期排序）。
这是我的现有表格：
| ID | 出生日期 |
|---|---|
| Personne1 | 19/11/90 19:14:52,182000000 |
| Personne1 | 19/11/90 20:00:52,170000000 |
| Personne1 | 19/11/90 21:00:00,190000000 |
而我想要的表格如下：
| ID | 出生日期 | 下一个日期 |
|---|---|---|
| Personne1 | 19/11/94 90:14:52,182000000 | 90/11/94 20:00:52,170000000 |
| Personne1 | 19/11/94 90:00:52,170000000 | 90/11/94 21:00:00,190000000 |
| Personne1 | 19/11/94 90:00:00,190000000 | N/A |
是否有类似于'NEXT(出生日期)'的函数，以计算出生日期列与下一个生日列之间的分钟:秒差异？
英文:
I want to add a column to my table that contains the next Birthdate (ordered by Birthdate)
This is my actuale table:
| ID | Birthdate |
|---|---|
| Personne1 | 19/11/90 19:14:52,182000000 |
| Personne1 | 19/11/90 20:00:52,170000000 |
| Personne1 | 19/11/90 21:00:00,190000000 |
And I'm looking for this table
| ID | Birthdate | nextdate |
|---|---|---|
| Personne1 | 19/11/94 90:14:52,182000000 | 90/11/94 20:00:52,170000000 |
| Personne1 | 19/11/94 90:00:52,170000000 | 90/11/94 21:00:00,190000000 |
| Personne1 | 19/11/94 90:00:00,190000000 | N/A |
Is there any function like 'NEXT(Birthdate)' ? in order to calculate the difference in min:ss between the Birthdate column and the next_Birthday column
答案1
得分: 0
这是LEAD分析函数。
示例数据：
with test (id, birthdate) as
(select 'personne1', to_timestamp('19.11.1990 19:14:52,182000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
select 'personne1', to_timestamp('19.11.1990 20:00:52,170000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
select 'personne1', to_timestamp('19.11.1990 21:14:00,190000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual
),
如果您想基于这些值计算某些内容，可以使用extract函数，它在时间戳上运行良好（显然，您已经有时间戳数据）：
temp as
(select id,
birthdate,
lead(birthdate) over (partition by id order by birthdate) nextdate
from test
)
select id, birthdate, nextdate,
extract(minute from (nextdate - birthdate)) mins,
extract(second from (nextdate - birthdate)) secs
from temp;
结果：
ID BIRTHDATE NEXTDATE MINS SECS
--------- ------------------------------ ------------------------------ ---------- ----------
personne1 19.11.90 19:14:52,182000000 19.11.90 20:00:52,170000000 45 59,988
personne1 19.11.90 20:00:52,170000000 19.11.90 21:14:00,190000000 13 8,02
personne1 19.11.90 21:14:00,190000000
英文:
That would be LEAD analytic function.
Sample data:
SQL> with test (id, birthdate) as
2 (select 'personne1', to_timestamp('19.11.1990 19:14:52,182000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
3 select 'personne1', to_timestamp('19.11.1990 20:00:52,170000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual union all
4 select 'personne1', to_timestamp('19.11.1990 21:14:00,190000000', 'dd.mm.yyyy hh24:mi:ss,ff9') from dual
5 ),
As you want to calculate something based on those values, use extract function which works well on timestamps (which is what you, apparently, have):
6 temp as
7 (select id,
8 birthdate,
9 lead(birthdate) over (partition by id order by birthdate) nextdate
10 from test
11 )
12 select id, birthdate, nextdate,
13 extract(minute from (nextdate - birthdate)) mins,
14 extract(second from (nextdate - birthdate)) secs
15 from temp;
ID BIRTHDATE NEXTDATE MINS SECS
--------- ------------------------------ ------------------------------ ---------- ----------
personne1 19.11.90 19:14:52,182000000 19.11.90 20:00:52,170000000 45 59,988
personne1 19.11.90 20:00:52,170000000 19.11.90 21:14:00,190000000 13 8,02
personne1 19.11.90 21:14:00,190000000
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论