SQL 分组依据和滞后函数未给出正确的输出

Question

>我有以下输出：

last_login                 | id  | type   | w_id
11/9/2016  10:59:13 PM  | 123 | Thing1 | W1
11/9/2016  10:59:15 PM  | 123 | Thing1 | W1
11/9/2016  10:59:15 PM  | 123 | Thing1 | W3
11/10/2016  10:59:13 PM | 123 | Thing2 | W2
11/11/2016  10:59:13 PM | 123 | Thing1 | W1
11/12/2016  10:59:13 PM | 123 | Thing1 | W1
11/12/2016  10:59:13 PM | 345 | Thing1 | W4
11/13/2016  10:59:13 PM | 345 | Thing1 | W1
11/14/2016  10:59:13 PM | 345 | Thing2 | W2
11/15/2016  10:59:13 PM | 345 | Thing2 | W5
11/16/2016  10:59:13 PM | 345 | Thing1 | W1
11/16/2016  10:59:13 PM | 345 | Thing1 | W1
11/17/2016  10:59:13 PM | 345 | Thing1 | W4
11/17/2016  10:59:13 PM | 345 | Thing1 | W4

对于以下查询：

select sa.last_login, ad.ID, sa.type, w_id,
from table1 dcc
join table2 AD
on AD.ID=DCC.id
JOIN table3 sa
ON AD.ID=sa.id
join table4 sc
on dcc.id=sc.id
where sic3=‘Something’
order by dcc.id, sa.last_login

我想要这样的东西的输出：

last_login              | id | old_type | type | old_w_id | w_id
11/11/2016  10:59:13 PM | 123 | Thing2  | Thing1 | W2     | W1
11/17/2016  10:59:13 PM | 345 | Thing1  | Thing1 | W1     | W4

我正在尝试通过以下方式执行此操作：

select
t.last_login, t.id, t.old_type, t.type , t.old_w_id, t.w_id
from
(select sa.last_login, ad.id, sa.type, 
lag(sa.type, 1) over (partition by ad.id order by sa.last_login) as old_type, w_id,
lag(w_id, 1) over (partition by ad.ID order by sa.last_login) as old_w_id from table1 dcc
join table2 AD
on ad.id=DCC.id 
JOIN table3 sa
ON AD.ID=sa.id
join table4 sc
on dcc.id=sc.id
where sc.si=’Something’
order by dcc.id, sa.last_login) t
where t.old_type like ’THING1’ and t.type like ‘THING2’
group by t.id, t.id, t.old_type, t.type, t.w_id, t.old_w_id

但是我得到了这样的输出：

last_login              | id | old_type | type | old_w_id | w_id
11/11/2016  10:59:13 PM | 123 | Thing1  | Thing2 | W1     | W2

如何获得所需的输出，为什么我的查询(滞后函数(无法正常工作？

Answer 1

你不需要子查询。 只需使用lag()：

select sa.last_login, ad.ID, sa.type, w_id,
lag(sa.type) over (partition by ad.id order by sa.last_login) as prev_type,
lag(sa.w_id) over (partition by ad.id order by sa.last_login) as prev_w_id
from table1 dcc join
table2 AD
on AD.ID = DCC.id join
table3 sa
on AD.ID = sa.id join
table4 sc
on dcc.id = sc.id
where sic3 = 'Something'
order by dcc.id, sa.last_login

如果你只想要最后一行，你可以使用fetch first 1 row only或sometihng，如下所示：

select *
from (select sa.last_login, ad.ID, sa.type, w_id,
lag(sa.type) over (partition by ad.id order by sa.last_login) as prev_type,
lag(sa.w_id) over (partition by ad.id order by sa.last_login) as prev_w_id,
row_number() over (partition by ad.id order by sa.last_login desc) as seqnum
from table1 dcc join
table2 AD
on AD.ID = DCC.id join
table3 sa
on AD.ID = sa.id join
table4 sc
on dcc.id = sc.id
where sic3 = 'Something'
) t
where seqnum = 1
order by id, last_login;

Answer 2

在对要检查的字段(TYPE 和 W_ID(进行滞后处理后，您可以使用 where 子句将其包装在另一个 select 语句中，该语句删除了最后一个等于当前行的行。例如：

编辑我的第一个答案包括它刚刚更改的所有行，我所做的只是获取该代码并将其包装以获取按 ID 分区的最高更改。

create table Testing
(
last_login date,
id number,
type varchar2(50),
w_id varchar2(2)
);
insert into Testing values(to_date('11/09/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing1','W1');
insert into Testing values(to_date('11/09/2016 10:59:15 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing1','W1');
insert into Testing values(to_date('11/09/2016 10:59:15 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing1','W3');
insert into Testing values(to_date('11/10/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing2','W2');
insert into Testing values(to_date('11/11/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing1','W1');
insert into Testing values(to_date('11/12/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),123,'Thing1','W1');
insert into Testing values(to_date('11/12/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W4');
insert into Testing values(to_date('11/13/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W1');
insert into Testing values(to_date('11/14/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing2','W2');
insert into Testing values(to_date('11/15/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing2','W5');
insert into Testing values(to_date('11/16/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W1');
insert into Testing values(to_date('11/16/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W1');
insert into Testing values(to_date('11/17/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W4');
insert into Testing values(to_date('11/17/2016 10:59:13 PM','MM/DD/YYYY HH:MI:SS PM'),345,'Thing1','W4');
select LAST_LOGIN, ID, TYPE, W_ID, TypeLag, W_IDLag
from(
select LAST_LOGIN, ID, TYPE, W_ID, TypeLag, W_IDLag,
rank() over(partition by id order by last_login desc) RankOrder
from(
select Testing.*,
lag(TYPE,1) over(partition by ID order by last_login) TypeLag,
lag(W_ID,1) over(partition by ID order by last_login) W_IDLag
from Testing)
where (nvl(TypeLag,'X') <> TYPE
or nvl(W_IDLag,'X') <> W_ID)
)
where RankOrder = 1

输出为：

LAST_LOGIN  ID      TYPE    W_ID    TYPELAG     W_IDLAG
11-NOV-16   123     Thing1  W1      Thing2      W2
17-NOV-16   345     Thing1  W4      Thing1      W1