Table:
User_id channel dt
1 A 2020/6/28
1 A 2020/6/29
2 A 2020/6/30
2 V 2020/7/1
3 A 2020/7/2
3 S 2020/7/3
3 V 2020/7/4
The final effect
User_id channel dt
1 A 2020/6/28
2 V 2020/7/1
3 S 2020/7/3
3 V 2020/7/4
CodePudding user response:
- create a test data
The create table nota (user_id int, channel varchar (10), dt date)
Insert into nota
Values (1, 'A', '2020/6/28),
(1, 'A', '2020/6/29),
(2, 'A', '2020/6/30),
(2, 'V', '2020/7/1),
(3, 'A', '2020/7/2),
(3, 'S', '2020/7/3),
(3, 'V', '2020/7/4')
- query eventually data
With nota_a as - the only through A channel into the retained the user_id through A record for the first time when
(
Select * from top 1 nota where user_id in (
The select user_id from nota group by user_id having count (distinct channel)=1) and channel='A' order by dt
)
Nota_na as -- get rid of A situation, to delete the channel A
(
Select * from nota where user_id not in (select user_id from nota_a) and channel<> 'A'
)
, last_info as - the above two cases merger
(select * from nota_a union all select * from nota_na)
- according to query the table updates to the original table
The merge into nota as t
Using last_info as s
On s.u ser_id=t.u ser_id and s.c hannel=tc hannel and spyware doctor t=t.d t
When is not matched by the source
Then delete;
CodePudding user response:
Create table test (user_id int, channel varchar (100), dt datetime)
Insert into test (user_id, channel, dt)
Select 1, 'A', '2020/6/28'
Union select 1, 'A', '2020/6/29'
Union select 2, 'A', '2020/6/30'
Union select 2, I 'V' and '2020/7/1'
Union select 3, 'A', '2020/7/2'
Union select 3, 'S', '2020/7/3'
Union select 3, 'V', '2020/7/4'
The select a.u ser_id, a.c hannel, a. d. t the from (
The select ROW_NUMBER () over (partition by user_id order by user_id, channel, dt) rownum, * from the test a
) a
Where rownum=1 and user_id not in (select user_id from test where a channel<> 'A' group by user_id)
The union
Select * from the test a
Where user_id in (select user_id from test where a channel<> 'A' group by user_id)
And a.c hannel<> 'A'
CodePudding user response:
SELECT *
FROM the TABLE A
WHERE NOT EXISTS
(SELECT one FROM the TABLE WHERE USER_ID=A.U SER_ID AND CHANEL<> 'A')
AND NOT the EXISTS (SELECT 1 FROM the TABLE WHERE USER_ID=A.U SER_ID AND DTUNION ALL
SELECT *
FROM the TABLE A
WHERE the EXISTS
(SELECT one FROM the TABLE WHERE USER_ID=A.U SER_ID AND CHANEL<> 'A')
AND CHANEL<> 'A'