CODEWITHSUNDEEP
sqloracle-database
Christian
Christian

Reputation: 65

Add offset to rows to get monotonically increasing values

based on this thread (Check rows for monotonically increasing values), I have an additional requirement: The value-column represents a counter. In my application, due to some annoying reason, the counter value gets reset from time to time, i.e. starts from zero. For data evaluation, I need the accumulated value of all counts. My idea was to create an additional column that contains the accumulated value. As long as no reset occurs, the value of the new column is the same as of the original value column. After a reset, the value of the new column is the latest accumulated value + the current counter value. Multiple resets may occur in the data. Once again, rows with the same "name" belong to the same measurement and have to be handled sorted by meas_date.

This is the original data:

id   name   meas_date   value
1    name1  2018/01/01  1
2    name1  2018/01/02  2
3    name2  2018/01/04  2
4    name1  2018/01/03  1
5    name1  2018/01/04  5
6    name2  2018/01/05  4
7    name2  2018/01/06  2
8    name1  2018/01/05  2

Desired result would be

id   name   meas_date   value  accumulated_value
1    name1  2018/01/01  1      1
2    name1  2018/01/02  2      2
3    name2  2018/01/04  2      2
4    name1  2018/01/03  1      3 
5    name1  2018/01/04  5      7
6    name2  2018/01/05  4      4
7    name2  2018/01/06  2      6
8    name1  2018/01/05  2      9

The LAG function from the thread mentioned above is really helpful to find the rows where the counter value was reset. But now, I am struggling to combine this with the accumulation of the values to get the overall counter values.

Thank you very much,

Christian

Upvotes: 0

Views: 517

Answers (2)

Christian
Christian

Reputation: 65

I guess I found a solution which takes two steps:

-- 1. set flag column = 2 for all rows with values right before an reset
update TEST dst set dst.flag = (
  with src as (
    SELECT id, name, value,
    CASE WHEN value < value_next THEN 0 ELSE 2 END AS flag
    FROM (
      SELECT id, name, value,
      LEAD(value, 1, 0) OVER (PARTITION BY name order by meas_date) AS value_next
      FROM TEST
    )      
  )
  select src.flag from src where dst.id = src.id
) 

-- 2. Use SQL for Modeling to calculate the accumulated values
SELECT  name, meas_date, value, offset, value+offset as accumulated_value
FROM TEST 
MODEL RETURN UPDATED ROWS
 PARTITION BY (name) 
 DIMENSION BY (meas_date, flag)
 MEASURES (value, 0 as offset)
 RULES (
  offset[meas_date, ANY] ORDER BY meas_date = NVL(sum(NVL(value,0))[meas_date < CV(meas_date), flag=2],0) 
 );

After step 1:

id  name    meas_date   value flag
1   name1   01.01.18    1     0
2   name1   02.01.18    2     2
3   name2   04.01.18    2     0
4   name1   03.01.18    1     0
5   name1   04.01.18    5     2
6   name2   05.01.18    4     2
7   name2   06.01.18    2     2
8   name1   05.01.18    2     2

Output of step 2

name    meas_date   value  offset  accumulated_value
name1   01.01.18    1      0       1
name1   02.01.18    2      0       2
name1   03.01.18    1      2       3
name1   04.01.18    5      2       7
name1   05.01.18    2      7       9
name2   04.01.18    2      0       2
name2   05.01.18    4      0       4
name2   06.01.18    2      4       6

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

Is this helpful?

select id, name, meas_date, value,  sum(value) over(partition by meas_date order by meas_date, value )  from #temp 
group by id, name, meas_date, value
order by meas_date,   value

Upvotes: 0

Related Questions