This table I want to find out a list of car time vary within 10 s of license plates, and display only meet this condition number greater than 2 data,

I write this not rownum & lt; 200 will be particularly slow, outreach without limitation is the product of two table content, number so definitely can't write,
EMM,
Total way of thinking is found to day period appear less than set intervals circumstance is greater than the set threshold value calculate counterparts, eventually to check the number of data is an object counterparts, began to peer time finally peers counterparts captured the number of times (i.e., that the situation is less than the interval number),

CodePudding user response:

Is calculated for the column, it will be slow, table structure and index all the way, the DLL,

CodePudding user response:

Paging and no paging, of course, there will be a performance difference, stick SQL also come out, and best execution plan

CodePudding user response:

Good way to ask questions should be:
1. Stick the above-mentioned structure: if HK_CLKK table structure is the screenshot above you, that you don't need to,
2. The attached table statements and test statements, i.e., the Create statement + insert statement, after all is convenient, with people is to give the convenience,
3. Under the instructions to achieve the effect of, it is best to have the image display effect,

CodePudding user response:

Hope the building description problems and rearrangement,

CodePudding user response:

The select CPHM, count (*) from (
Select
CPHM,
Lag (to_date (GCSJ)) over (partition by CPHM order by to_date (GCSJ) desc) - to_date (GCSJ) d_minus
The from hk_clkk
) t1
Where d_minus * 24 * 60 * 60 & lt; 10
Group by CPHM
Having the count (*) & gt; 2;

CodePudding user response:

The select CPHM, count (*) from (
Select
CPHM,
Lag (to_date (GCSJ, 'yyyy - mm - dd hh24: mi: ss')) over (partition by CPHM order by to_date (GCSJ, 'yyyy - mm - dd hh24: mi: ss') desc) - to_date (GCSJ, 'yyyy - mm - dd hh24: mi: ss') d_minus
The from hk_clkk
) t1
Where d_minus * 24 * 60 * 60 & lt; 10
Group by CPHM
Having the count (*) & gt; 2;

Just missed to_date format, sweep through this list of the written word, performance measurement, can try