SQL query initial and final balance as well sum of events

Question

I have a fairly big table (about 300gb) as:

event_day event_hour customer_id initial_bal final_bal topups debts
01/01     00         11111       0           50        60     10
01/01     01         11111       50          80        45     15
01/01     02         11111       80          30        0      50
...

I want to summarize it into whole days, e.g.:

event_day customer_id initial_bal final_bal topups debts
01/01     11111       0           30        105    75
...

But I´m having troubles with the analitical functions.. I'm working with something like:

select *
from
(
  select
    event_day, customer_id, initial_bal, final_bal,
    sum(topups) over (partition by event_day, customer_id by event_day, customer_id asc) topups,
    row_number() over (partition by event_day, customer_id order by event_day, customer_id asc) as initial_bal,
    row_number() over (partition by event_day, customer_id order by event_day, customer_id desc) as final_bal
  from MY_300GB_TABLE t
)
where initial_bal = 1 or final_bal = 1
order by customer_id, event_day

Which inst doing what I expected... a hand?

I´m tring to avoid joins, sub-queries and such.. I simplified here but there the actual project involves a few big tables and performance might an issue. I'm using Oracle 12C.

thanks!

Answer 1

Good occasion to aggregate with first (or last) option:

select event_day, customer_id,
       max(initial_bal) keep (dense_rank first order by event_hour) initial_bal,
       max(final_bal) keep (dense_rank last order by event_hour) final_bal,
       sum(topups) topups, sum(debts) debts
  from tla_t_balance_summary t
  group by event_day, customer_id;

^{dbfiddle demo}

Your query works too, but you made a mistake in order by. And it requires additional aggregation, because we have two rows for customers:

select event_day, customer_id, max(topups), max(debts),
       min(case rib when 1 then initial_bal end) ib, min(case rfb when 1 then final_bal end) fb 
  from (
    select event_day, customer_id, initial_bal, final_bal,
           sum(topups) over (partition by event_day, customer_id) topups,
           sum(debts) over (partition by event_day, customer_id) debts,
           row_number() over (partition by event_day, customer_id order by event_hour) as rib,
           row_number() over (partition by event_day, customer_id order by event_hour desc) as rfb
      from tla_t_balance_summary t)
  where rib = 1 or rfb = 1
  group by customer_id, event_day;