Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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 |
---|---|---|---|
Migration from SQL server to oracle (XML input) | 4 | 55 | |
why truncate is faster than delete in oracle ? | 4 | 51 | |
How to add a suffix to a value in a column based on the value in another column | 4 | 32 | |
UPDATE JOIN multiple tables | 5 | 23 |
Join the community of 500,000 technology professionals and ask your questions.