问题描述
我有以下初始数据集
我想从SQL查询生成以下输出:
即，我想计算每个F_ID保留在特定L_CAT中的时间跨度
生成场景的代码
谢谢
F_ID L_CAT CHG_DT F1 VHL 01-FEB-2016 F1 VHL 10-FEB-2016 F1 VHL 15-FEB-2016 F1 MHL 20-FEB-2016 F1 VHL 25-FEB-2016 F1 VHL 28-FEB-2016 F1 MHL 05-MAR-2016 F1 MHL 10-MAR-2016 F2 VHL 01-FEB-2016 F2 VHL 10-FEB-2016 F2 MHL 18-FEB-2016 F2 MHL 21-FEB-2016 F2 VHL 25-FEB-2016
我想从SQL查询生成以下输出:
F_ID L_CAT FROM_DT TO_DT F1 VHL 01-FEB-2016 20-FEB-2016 F1 MHL 20-FEB-2016 25-FEB-2016 F1 VHL 25-FEB-2016 05-MAR-2016 F1 MHL 05-MAR-2016 10-MAR-2016 F2 VHL 01-FEB-2016 18-FEB-2016 F2 MHL 18-FEB-2016 25-FEB-2016 F2 VHL 25-FEB-2016 25-FEB-2016
即，我想计算每个F_ID保留在特定L_CAT中的时间跨度
生成场景的代码
create table my_test
(
f_id varchar2(30),
l_cat varchar2(30),
chg_dt date
);
insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','01-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','10-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','15-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','20-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','25-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','28-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','05-MAR-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','10-MAR-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','01-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','10-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F2','MHL','18-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F2','MHL','21-FEB-2016');
insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','25-FEB-2016');
COMMIT;
谢谢
专家解答
有更简洁的方法，但我会给你更长的版本，因为它会让你在这里建立概念
SQL> @drop my_Test
Y1 Y2
-------------------------------------------------------------------------------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL>
SQL> create table my_test
2 (
3 f_id varchar2(30),
4 l_cat varchar2(30),
5 chg_dt date
6 );
Table created.
SQL>
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','01-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','10-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','15-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','20-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','25-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','VHL','28-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','05-MAR-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F1','MHL','10-MAR-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','01-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','10-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F2','MHL','18-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F2','MHL','21-FEB-2016');
1 row created.
SQL> insert into my_test(f_id, l_cat, chg_dt) values ('F2','VHL','25-FEB-2016');
1 row created.
SQL> COMMIT;
Commit complete.
--
-- extract some information about previous and next row from the current row
--
SQL>
SQL>
SQL> select f_id,
2 l_cat,
3 chg_dt,
4 lead(chg_dt) over ( partition by f_id order by chg_dt ) nxt_dt,
5 lag(chg_dt) over ( partition by f_id order by chg_dt ) prev_dt,
6 lead(l_cat) over ( partition by f_id order by chg_dt ) nxt_cat,
7 lag(l_cat) over ( partition by f_id order by chg_dt ) prev_cat
8 from my_Test
9 order by 1,3;
F_ID L_CAT CHG_DT NXT_DT PREV_DT NXT_CAT PREV_CAT
------------------------------ ------------------------------ --------- --------- --------- ------------------------------ ------------------------------
F1 VHL 01-FEB-16 10-FEB-16 VHL
F1 VHL 10-FEB-16 15-FEB-16 01-FEB-16 VHL VHL
F1 VHL 15-FEB-16 20-FEB-16 10-FEB-16 MHL VHL
F1 MHL 20-FEB-16 25-FEB-16 15-FEB-16 VHL VHL
F1 VHL 25-FEB-16 28-FEB-16 20-FEB-16 VHL MHL
F1 VHL 28-FEB-16 05-MAR-16 25-FEB-16 MHL VHL
F1 MHL 05-MAR-16 10-MAR-16 28-FEB-16 MHL VHL
F1 MHL 10-MAR-16 05-MAR-16 MHL
F2 VHL 01-FEB-16 10-FEB-16 VHL
F2 VHL 10-FEB-16 18-FEB-16 01-FEB-16 MHL VHL
F2 MHL 18-FEB-16 21-FEB-16 10-FEB-16 MHL VHL
F2 MHL 21-FEB-16 25-FEB-16 18-FEB-16 VHL MHL
F2 VHL 25-FEB-16 21-FEB-16 MHL
13 rows selected.
-- DATE_FROM
-- when the prev is null (start of rows), we are at the start of a new range
-- or when this category is not the same as previous, we are at the start of a new range
--
-- DATE_TO
-- when the next is null (end of rows), we are at the end of current date range
-- or when next category is not same as this one, we are at the end of current date range
SQL> with t as
2 (
3 select f_id,
4 l_cat,
5 chg_dt,
6 lead(chg_dt) over ( partition by f_id order by chg_dt ) nxt_dt,
7 lag(chg_dt) over ( partition by f_id order by chg_dt ) prev_dt,
8 lead(l_cat) over ( partition by f_id order by chg_dt ) nxt_cat,
9 lag(l_cat) over ( partition by f_id order by chg_dt ) prev_cat
10 from my_Test
11 )
12 select
13 f_id,
14 l_cat,
15 case
16 when prev_dt is null then chg_dt
17 when l_cat != prev_cat then chg_dt
18 end date_from,
19 case
20 when nxt_dt is null then chg_dt
21 when l_cat != nxt_cat then nxt_dt
22 end date_to
23 from t;
F_ID L_CAT DATE_FROM DATE_TO
------------------------------ ------------------------------ --------- ---------
F1 VHL 01-FEB-16
F1 VHL
F1 VHL 20-FEB-16
F1 MHL 20-FEB-16 25-FEB-16
F1 VHL 25-FEB-16
F1 VHL 05-MAR-16
F1 MHL 05-MAR-16
F1 MHL 10-MAR-16
F2 VHL 01-FEB-16
F2 VHL 18-FEB-16
F2 MHL 18-FEB-16
F2 MHL 25-FEB-16
F2 VHL 25-FEB-16 25-FEB-16
13 rows selected.
--
-- now I want to carry the DATE_FROM down through every row
--
SQL> with t as
2 (
3 select f_id,
4 l_cat,
5 chg_dt,
6 lead(chg_dt) over ( partition by f_id order by chg_dt ) nxt_dt,
7 lag(chg_dt) over ( partition by f_id order by chg_dt ) prev_dt,
8 lead(l_cat) over ( partition by f_id order by chg_dt ) nxt_cat,
9 lag(l_cat) over ( partition by f_id order by chg_dt ) prev_cat
10 from my_Test
11 )
12 select
13 f_id,
14 l_cat,
15 chg_dt,
16 last_value(case
17 when prev_dt is null then chg_dt
18 when l_cat != prev_cat then chg_dt
19 end ignore nulls) over ( partition by f_id order by chg_dt) date_from,
20 case
21 when nxt_dt is null then chg_dt
22 when l_cat != nxt_cat then nxt_dt
23 end date_to
24 from t
25 /
F_ID L_CAT CHG_DT DATE_FROM DATE_TO
------------------------------ ------------------------------ --------- --------- ---------
F1 VHL 01-FEB-16 01-FEB-16
F1 VHL 10-FEB-16 01-FEB-16
F1 VHL 15-FEB-16 01-FEB-16 20-FEB-16
F1 MHL 20-FEB-16 20-FEB-16 25-FEB-16
F1 VHL 25-FEB-16 25-FEB-16
F1 VHL 28-FEB-16 25-FEB-16 05-MAR-16
F1 MHL 05-MAR-16 05-MAR-16
F1 MHL 10-MAR-16 05-MAR-16 10-MAR-16
F2 VHL 01-FEB-16 01-FEB-16
F2 VHL 10-FEB-16 01-FEB-16 18-FEB-16
F2 MHL 18-FEB-16 18-FEB-16
F2 MHL 21-FEB-16 18-FEB-16 25-FEB-16
F2 VHL 25-FEB-16 25-FEB-16 25-FEB-16
13 rows selected.
--
-- and with that, a simple GROUP BY / MAX will get the DATE_TO I need
--
SQL> with t as
2 (
3 select f_id,
4 l_cat,
5 chg_dt,
6 lead(chg_dt) over ( partition by f_id order by chg_dt ) nxt_dt,
7 lag(chg_dt) over ( partition by f_id order by chg_dt ) prev_dt,
8 lead(l_cat) over ( partition by f_id order by chg_dt ) nxt_cat,
9 lag(l_cat) over ( partition by f_id order by chg_dt ) prev_cat
10 from my_Test
11 ),
12 t2 as
13 (
14 select
15 f_id,
16 l_cat,
17 chg_dt,
18 last_value(case
19 when prev_dt is null then chg_dt
20 when l_cat != prev_cat then chg_dt
21 end ignore nulls) over ( partition by f_id order by chg_dt) date_from,
22 case
23 when nxt_dt is null then chg_dt
24 when l_cat != nxt_cat then nxt_dt
25 end date_to
26 from t
27 )
28 select
29 f_id,
30 l_cat,
31 date_from,
32 max(date_to)
33 from t2
34 group by f_id, l_cat, date_from
35 order by 1,3;
F_ID L_CAT DATE_FROM MAX(DATE_
------------------------------ ------------------------------ --------- ---------
F1 VHL 01-FEB-16 20-FEB-16
F1 MHL 20-FEB-16 25-FEB-16
F1 VHL 25-FEB-16 05-MAR-16
F1 MHL 05-MAR-16 10-MAR-16
F2 VHL 01-FEB-16 18-FEB-16
F2 MHL 18-FEB-16 25-FEB-16
F2 VHL 25-FEB-16 25-FEB-16
7 rows selected.
SQL>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
