Postgresql View aggregating data

Question

I've got a problem to make this "nice" and efficient, as well as easy to read. Unfortunately it's lacking both properties.

Given a table with date,transaction_type,username and credits, I want to generate a view which summarizes into these fields: date_from,date_next,username,credits_total,credits_total

Explanation:

date_from and date_to are this first of a month and the next first of a month. (e.g. 2022-06-01 and 2022-07-01)
username is grouped and so only one same username per date_from/date_next pair
credits_total is the sum of credit_change where transaction = 'charge'
credits_left is credits_total - sum(credits_change where transaction_type = 'usage')

I've identified multiple problems and were partially able to fix them:

date_from/_next is easy with date_trunc('month', date) and `date_trunc('month', date) + interval '1 month''
group username/dates is possible with group by
making credits_total without duplication is hard. Or is subquery the only solution?
credits left is almost the same, but with other transaction_type and subtracting from credits_total. How Can I reuse the credits_total?

What I came up with (and am very unhappy with)

The source table:

create table usage  -- simplified
(
    datetime timestamp default now() not null,
    transaction_type varchar(16) not null,
    user varchar(128) not null,
    credits_change int not null,
);

My code for the view:

CREATE MATERIALIZED VIEW token_usage
AS
SELECT 
       -- trivial:
       user,
       date_trunc('month', datetime) as date_from,
       date_trunc('month', datetime) + interval '1 month' as date_next,

       -- sum of credits_change with requirement + duplication
       (    -- see here. first time
            SELECT sum(credits_change)
            FROM usage
            WHERE transaction_type = 'charge'
            AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) as credits_total,

       -- sum of credits change minus other sum and more duplication
       (   -- see here. using the same again
           SELECT sum(credits_change)
           FROM usage
           WHERE transaction_type = 'charge'
           AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) - (  -- see here. using the same again, but with different transaction_type
           SELECT sum(credits_change)
           FROM usage
           WHERE transaction_type = 'usage'
           AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) as credits_left
    FROM usage
    GROUP BY user_name, datetime, datetime_next_start
WITH DATA;

Tbh it seems I'm just missing some postgresql tool, to make this better.

Thanks for the help :)

"am very unhappy with" - the unhappy part is mostly described in the code comments? Would it help to expand on those points directly in the SO Question text? — Stefan Wuebbe
– Stefan Wuebbe, Commented Jun 28, 2022 at 11:20

S-Man · Accepted Answer · 2022-06-28 13:30:57Z

3

Without knowing some sample data and expected output to try the query, the following can surely taken as a sketch for your complete solution. I guess, the main point here is knowing about the FILTER clause for aggregation functions(*):

CREATE MATERIALIZED VIEW token_usage AS

SELECT
    user,
    date_trunc('month', datetime) as date_from,
    date_trunc('month', datetime) + interval '1 month' as date_next,
    
    SUM(credits_change) FILTER (WHERE transaction_type = 'charge') as credits_total,
    SUM(credits_change) FILTER (WHERE transaction_type = 'charge')
        - SUM(credits_change) FILTER (WHERE transaction_type = 'usage') as credits_left
FROM usage
GROUP BY 1, 2, 3

Alternative with less duplication but maybe less readable due to subquery:

CREATE MATERIALIZED VIEW token_usage AS

SELECT 
    user,
    date_from,
    date_from + interval '1 month' as date_next,
    credits_total,
    credits_total - credits_usage as credits_left
FROM (
    SELECT
        user,
        date_trunc('month', datetime) as date_from,
    
        SUM(credits_change) FILTER (WHERE transaction_type = 'charge') as credits_total,
        SUM(credits_change) FILTER (WHERE transaction_type = 'usage') as credits_usage
    FROM usage
    GROUP BY 1, 2 
) s

*) You can use a CASE clause instead of the FILTER as well:

SUM(abc) FILTER (WHERE condition)

-- generally the same as

SUM(
    CASE WHEN condition THEN 
        abc 
    END
)

edited Jun 28, 2022 at 13:30

answered Jun 28, 2022 at 11:03

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

loki.dev Over a year ago

Awesome, thanks! FILTER was exactly what I needed and this even helps with the parts, which I left out to make my example simpler. <3 !

Thorsten Kettner Over a year ago

The last part is wrong. SUM(abc) FILTER (WHERE condition) can be converted to SUM(CASE WHEN condition THEN abc END), not to CASE WHEN condition THEN SUM(abc) END.

S-Man Over a year ago

@ThorstenKettner of course, you're right!

Collectives™ on Stack Overflow

Postgresql View aggregating data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related