select min(x), max(x)
from test
group by trunc(x,'mi') + case when to_number(to_char(x,'ss')) <= 30 then 30/60/60/24 else 60/60/60/24 end
order by 1;
select s.num, t.x, s.x from (
select row_number() over (order by x) num, x from (
select x from test
where not exists (
select 1 from (select x - 1/60/60/24 as y from test)
where x = y
)
order by x
)
) s,
(
select row_number() over (order by x) num, x from (
select x from test
where not exists (
select 1 from (select x + 1/60/60/24 as y from test)
where x = y
)
order by x
)
) t
where s.num = t.num;
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY minx) seq, minx, MAX(x) OVER (PARTITION BY minx) maxx
FROM (SELECT *
FROM (SELECT x, TO_DATE(NULL) minx, TO_DATE(NULL) maxx, ROW_NUMBER() OVER (ORDER BY x) rn
FROM test)
MODEL
DIMENSION BY(rn)
MEASURES(x, minx)
RULES UPDATE AUTOMATIC ORDER
(minx [ANY]
ORDER BY x =
CASE
WHEN x[CV(rn)] <= x[CV(rn) - 1] + 1 / 86400 THEN minx[CV(rn) - 1]
ELSE x[CV(rn)]
END))
.....
SELECT ROWNUM seq, minx, maxx
FROM ( SELECT minx, MAX(x) maxx
FROM (SELECT *
FROM (SELECT x,
TO_DATE(NULL) minx,
TO_DATE(NULL) maxx,
ROW_NUMBER() OVER (ORDER BY x) rn
FROM test)
MODEL
DIMENSION BY(rn)
MEASURES(x, minx)
RULES UPDATE AUTOMATIC ORDER
(minx [ANY]
ORDER BY x =
CASE
WHEN x[CV(rn)] <= x[CV(rn) - 1] + 1 / 86400
THEN
minx[CV(rn) - 1]
ELSE
x[CV(rn)]
END))
GROUP BY minx)
select ceil(num / 2), min(x), max(x)
from (
select row_number() over (order by x) num, t.x from (
select x, lead(x) over (order by x) next, lag(x) over (order by x) previous
from test
) t
where (next - x > 1/60/60/24 or next is null)
or (x - previous > 1/60/60/24 or previous is null)
) TEMP
group by ceil(num / 2)
;
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY minx) seq, minx, MAX(x) OVER (PARTITION BY minx) maxx
FROM (SELECT *
FROM (SELECT x, TO_DATE(NULL) minx, ROW_NUMBER() OVER (ORDER BY x) rn FROM test)
MODEL
DIMENSION BY(rn)
MEASURES(x, minx)
RULES UPDATE AUTOMATIC ORDER
(minx [ANY]
ORDER BY rn =
CASE
WHEN x[CV(rn)] <= x[CV(rn) - 1] + 1 / 86400 THEN minx[CV(rn) - 1]
ELSE x[CV(rn)]
END))
ORDER BY 1;
select row_number() over (order by x) num, t.x, case when (next - x > 1/60/60/24 and x - previous > 1/60/60/24)
or (next is null and x - previous > 1/60/60/24)
or (next - x > 1/60/60/24 and previous is null)
then 1
else 0 end isolated
from (
select x, lead(x) over (order by x) next, lag(x) over (order by x) previous
from test
group by x
) t
where (next - x > 1/60/60/24 or next is null)
or (x - previous > 1/60/60/24 or previous is null)
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
SQL: launch actions one before the other | 10 | 23 | |
Email Header Detail | 12 | 55 | |
Query Syntax | 17 | 36 | |
2 IIF's in Access query | 25 | 30 |
Join the community of 500,000 technology professionals and ask your questions.