Data in the table below, table, lane, pos, three columns represent the position, remove all locations (table, lane, the same pos) two recent data according to time,
Table data about hundreds of thousands of lines


Id table lane pos wu nan create_time
1 2 1 1 aaa BBBBB 01:03:01 2020-6-25. 456
2 1 2 1 CCCC DDDD 02:00:01 2020-6-25. 456
1 2 3 aaa BBB 03:00:01 2020-6-25. 456
4 2 1 1 333 2020-6-25 FFFF 04:06:01. 456
5 2 1 1 aaa BBB 05:00:01 2020-6-25. 456
6 1 2 1 333 2020-6-25 FFFF 06:00:01. 456
7 1 2 1 aaa BBB 07:00:01 2020-6-25. 456
8 1 1 FFFF 333 2 020-6-25 08:08:01. 456
9 1 1 3 333 2020-6-25 FFFF 09:00:01. 456
10 1 1 3 333 2020-6-25 FFFF 10:00:01. 456
11 1 1 3 aaa BBB 11:09:01 2020-6-25. 456
12 3 2 1 333 2020-6-25 FFFF 12:00:01. 456
13 3 2 1 aaa BBB 13:00:01 2020-6-25. 456
14 3 2 1 333 2020-6-25 FFFF 14:09:01. 456
15 3 2 1 333 2020-6-25 FFFF 15:00:01. 456

CodePudding user response:

Using the grouping function can be achieved directly;
According to the three columns grouping within the group, in accordance with the reverse chronological order

 select table, lane, pos from (
Select table, lane, pos, 
ROW_NUMBER () over (partition by table, lane, pos order by create_time desc) from test) as a 
Where n<3 

CodePudding user response:

Using the grouping function, according to the table, lane, pos group, in accordance with the time sorting within the group, Lag function can be used to calculate the time difference, adjacent

CodePudding user response:

sunny flowers quote 2 floor response:

using the grouping function can be achieved directly;
According to the three columns grouping within the group, in accordance with the reverse chronological order

 select table, lane, pos from (
Select table, lane, pos, 
ROW_NUMBER () over (partition by table, lane, pos order by create_time desc) from test) as a 
Where n<3 

Don't understand ah, to give the correct SQL execution, what is n?

CodePudding user response:

Well, forgot to aliasing;
You can add the knowledge of Row_number function

 select table, lane, pos from (
Select table, lane, pos, 
ROW_NUMBER () over (partition by table, lane, pos order by create_time desc) as n the from test) as a 
Where n<3