Analytic functions by Example

Shouvik Basu's picture
articles: 

Comments

Try this and you'll see why it is so, as I wrote the first time > > >

SELECT *
FROM
(SELECT
DENSE_RANK() over (order by forw_dens_per_5sec DESC,end_time_5sec-timepoint,timepoint) rank,
s.*,
end_time_5sec-timepoint dff
FROM
(SELECT timepoint,
COUNT(*) over (order by timepoint range BETWEEN CURRENT row AND interval '5' second following ) forw_dens_per_5sec,
MAX(timepoint) over (order by timepoint range BETWEEN CURRENT row AND interval '5' second following ) end_time_5sec
FROM trt
) s
)ss
--WHERE rank=1
;

The density is 5 records per interval for both (my and your solution) but mine has minor diff between first member and last one (order by forw_dens_per_5sec DESC,end_time_5sec-timepoint,timepoint).

shouvikb's picture

Pages