一个基本SQL学习_parch posey数据库-优快云博客

本文通过一个具体的例子展示了如何使用复杂的SQL查询来筛选特定的数据记录。包括使用LEAD和LAG函数进行窗口操作，以及ROW_NUMBER等函数来进行行编号，从而实现对数据的高级筛选。

create table TEMP3
(
SNAME CHAR(1),
SDATE VARCHAR2(10),
VALUE NUMBER
);

insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-1', 100);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-1', 200);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-10', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-11', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-2', 101);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-3', 100);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-5', 200);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-6', 200);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-8', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('A', '2008-10-9', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('B', '2008-10-4', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('B', '2008-10-5', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('B', '2008-10-6', 900);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('C', '2008-11-1', 100);
insert into TEMP3 (SNAME, SDATE, VALUE)
values ('D', '2008-12-1', 300);
commit;

select * from temp3

SNAME        SDATE        VALUE
A        2008-10-1        100
A        2008-10-1        200
A        2008-10-2        101
A        2008-10-3        100
A        2008-10-5        200
A        2008-10-6        200
A        2008-10-8        900
A        2008-10-9        900
A        2008-10-10        900
A        2008-10-11        900
B        2008-10-4        900
B        2008-10-5        900
B        2008-10-6        900
C        2008-11-1        100
D        2008-12-1        300

运行下面语句
with temp as
(
select t.*,
         lead(timec,1,null) over(partition by sname order by sdate) nexttime1,
         lead(timec,2,null) over(partition by sname order by sdate) nexttime2,
         lead(rn1,1,null) over(partition by sname order by sdate) nextrn1,
         lead(rn1,2,null) over(partition by sname order by sdate) nextrn2
from
(
    select sname,sdate,value,prvdate,rn1,sdate - prvdate timec from
    (
      select sname,sdate,value,
             lag(sdate,1,null) over(partition by sname order by sdate) prvdate,
             row_number() over(order by rownum) rn1
      from (select sname,to_date(sdate,'YYYY-MM-DD') sdate,value from temp3 order by sname,sdate)
    )
) t
)
select * from
(
select t.*,row_number() over(order by rownum) rn
from (select sname,to_date(sdate,'YYYY-MM-DD') sdate,value from temp3 order by sname,sdate) t
) where rn in
(
select rn1 from temp where nexttime1 = 1 and nexttime2 = 1
union
select nextrn1 from temp where nexttime1 = 1 and nexttime2 = 1
union
select nextrn2 from temp where nexttime1 = 1 and nexttime2 = 1
)

SNAME        SDATE                VALUE        RN
A        2008-10-1        200        2
A        2008-10-2        101        3
A        2008-10-3        100        4
A        2008-10-8        900        7
A        2008-10-9        900        8
A        2008-10-10        900        9
A        2008-10-11        900        10
B        2008-10-4        900        11
B        2008-10-5        900        12
B        2008-10-6        900        13