Oracle - Group By with "steps"
I need to follow the following table:
|MY_VALUES|
|---------|
| 1 | 1' iteration
| 3 | 1' iteration | 2' iteration
| 2 | 1' iteration | 2' iteration | 3' iteration
| 5 | 1' iteration | 2' iteration | 3' iteration
| 3 | 1' iteration | 2' iteration | 3' iteration
| 2 | | 2' iteration | 3' iteration
| 4 | | 3' iteration
I need a query that outputs the following:
MY_RESULTS
----------
14
15
16
The query basically sums the first 5 rows for the first result, then it goes forward from one value and sums the other 5 and so on ...
Is there a way to do this with Oracle? I can't find anything in the documentation to do this kind of work.
source to share
SQL tables are unordered sets. There is no such thing as 5 adjacent rows unless you have a column indicating the order.
Then you can do it using window functions if you have a column like this:
select t.*, sum(my_values) over (order by id rows between 4 preceding and current row) as sum5
from t;
This is done for all lines. If you want to remove the first 4 (and "id" doesn't make it easy), there are various ways:
select t.*
from (select t.*,
sum(my_values) over (order by id rows between 4 preceding and current row) as sum5,
row_number() over (order by id) as seqnum
from t
) t
where seqnum >= 5;
source to share
Your table is a little thin with only one column, so you don't have anything to order, but something like this: -
WITH MYVALUES AS (
SELECT 1 MYVALUE FROM DUAL
UNION ALL SELECT 3 FROM DUAL
UNION ALL SELECT 2 FROM DUAL
UNION ALL SELECT 5 FROM DUAL
UNION ALL SELECT 3 FROM DUAL
UNION ALL SELECT 2 FROM DUAL
UNION ALL SELECT 4 FROM DUAL
)
SELECT
MYVALUE,
SUM (MYVALUE) OVER (PARTITION BY NULL ORDER BY NULL ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) S
FROM MYVALUES
source to share
Assuming your table already had a row number, you can get the desired result by iterating over the table and for each row using a correlated subquery that sums the values from the current row up to 4 rows before it.
Since you don't already have a line number, we need to include it. But you don't even have a column that can be used to reproduce the order of the data that you showed us. In my request below, I am assuming that your table has a column id
that can be used to create an order. If you don't have such a column, I would recommend that you add one.
WITH cte AS (
SELECT MY_VALUES,
ROW_NUMBER() OVER (ORDER BY id) rn
FROM yourTable
)
SELECT
(SELECT SUM(t2.MY_VALUES) FROM cte t2
WHERE t2.rn BETWEEN t1.rn AND t1.rn + 4) AS MY_RESULTS
FROM cte t1
WHERE t1.rn <= (SELECT COUNT(*) FROM yourTable) - 4
source to share