By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
ORA-00923: FROM keyword not found where expected
create table test
(
c varchar2(10),
i INTEGER
);
insert into test
select 'A', level from dual connect by level <= 25
union all
select 'B', level from dual connect by level <= 18
UNION ALL
SELECT 'C', LEVEL FROM DUAL CONNECT BY LEVEL <= 25;
68 rows affected
SELECT
c, fval1, lval2
FROM
(
SELECT
c, i,
FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10)
ORDER BY c, i ASC) AS fval1,
-- the default frame clause for window functions
FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10) ORDER BY c, i ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS fval2,
-- the default doesn't work for the LAST_VALUE() fuction - you have to do it
-- this way.
LAST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 10) ORDER BY c, i ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lval2
FROM
test
) TAB
GROUP BY c, fval1, lval2
ORDER BY c, fval1;
| C | FVAL1 | LVAL2 |
|---|---|---|
| A | 1 | 10 |
| A | 11 | 20 |
| A | 21 | 25 |
| B | 1 | 10 |
| B | 11 | 18 |
| C | 1 | 10 |
| C | 11 | 20 |
| C | 21 | 25 |
SELECT
c, fval1, lval2
FROM
(
SELECT
c, i,
FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 7)
ORDER BY c, i ASC) AS fval1,
-- the default frame clause for window functions
FIRST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 7) ORDER BY c, i ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS fval2,
-- the default doesn't work for the LAST_VALUE() fuction - you have to do it
-- this way.
LAST_VALUE(i) OVER (PARTITION BY c, TRUNC((i - 1) / 7) ORDER BY c, i ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lval2
FROM
test
) TAB
GROUP BY c, fval1, lval2
ORDER BY c, fval1;
| C | FVAL1 | LVAL2 |
|---|---|---|
| A | 1 | 7 |
| A | 8 | 14 |
| A | 15 | 21 |
| A | 22 | 25 |
| B | 1 | 7 |
| B | 8 | 14 |
| B | 15 | 18 |
| C | 1 | 7 |
| C | 8 | 14 |
| C | 15 | 21 |
| C | 22 | 25 |