I have a table, movements, that shows the user's use of their card. It's something like this:

It shows their deposits and expenses, and also shows their final balance. I have this query that gets the total of deposits and expenses per day;

select EXTRACT(day FROM m.movementdate) as d, EXTRACT(month FROM m.movementdate) as m, 
       EXTRACT(year FROM m.movementdate) as y, sum(m.expense) as TotalExpense, 
       sum (m.deposit) as TotalDeposit
from movements m 
group by 3,2,1

But I want to get also the last record of their balance per day.

something like this:

How could I get this last part? I have found some ways to get the last balance of the day, but I can't figure out how to include it in the same query.

CodePudding user response：

another method to desired achieve the result is using window function row_number()

Here is the db<>fiddle

with cte as
(
  select 
    movementdate::date as dt,
    EXTRACT(day FROM movementdate) as d,
    EXTRACT(month FROM movementdate) as m, 
    EXTRACT(year FROM movementdate) as y,
    sum(expense) over (partition by movementdate::date) as expense,
    sum(deposit) over (partition by movementdate::date) as deposit,
    balance,
    row_number() over (partition by movementdate::date order by movementdate desc) as rn
  from movements
)

select 
  dt,
  d, 
  m, 
  y, 
  expense, 
  deposit, 
  balance
from cte
where rn = 1
order by
  1, 2, 3

output:

*-------------------------------------------------------*
|  dt          d    m   y     expense   deposit  balance|
*-------------------------------------------------------*
| 2021-07-09    9   7   2021    0         10       8    |
| 2021-07-10    10  7   2021    15         5       0    |
*-------------------------------------------------------*

CodePudding user response：

One method uses arrays:

select EXTRACT(day FROM m.movementdate) as d,
       EXTRACT(month FROM m.movementdate) as m, 
       EXTRACT(year FROM m.movementdate) as y,
       sum(m.expense) as TotalExpense, 
       sum (m.deposit) as TotalDeposit,
       (array_agg(m.balance order by m.movementdate desc))[1] as balance
from movements m 
group by 3,2,1;

I would also suggest that you use m.movementdate::date instead of defining the first three columns.

CodePudding user response：

Another option is to use PostgreSQL LATERAL Subquery

Something like:

WITH movements_per_day AS (
  select m.movementdate::date AS dt
     , EXTRACT(day FROM m.movementdate) as d
     , EXTRACT(month FROM m.movementdate) as m
     , EXTRACT(year FROM m.movementdate) as y
     , sum(m.expense) as TotalExpense
     , sum (m.deposit) as TotalDeposit
  from movements m
  group by 1,2,3,4
)
SELECT movements_per_day.*, last_movement.balance
  FROM movements_per_day
  CROSS JOIN LATERAL (
    SELECT movements.balance
    FROM movements
    WHERE movements.movement_date::date = movements_per_day.dt
    ORDER BY movements.movementdate DESC 
    LIMIT 1
  ) AS last_movement

As the other answers suggest, it's a bit simpler to use the m.movementdate::date instead of the first three columns.