Question 348125 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Aggregate events at different interval in postgres

I have this table that tracks budget usage of an advertising campaign. It has those fields:

  • AdvertiserID
  • Budget Scope ID: It’s the campaign ID
  • Budget: Budget of the campaign (es. 100$/day) and it can change during the day
  • Percentage: Percentage of the budget spent today (from 00:00 to 23:59)
  • Usage_updated_timestamp: Timestamp event

Now I want to plot a sawtooth chart of the total cost (budget*percentage/100) aggregating by AdvertiserId and summing the cost for all campaigns.
I expect the cost to go up during the day and fall to 0 at midnight because percentage is 0.

The problem is that I do not have records all at the same timestamp but they are randomly updated during the day. So I tried with this query:

SELECT
 advertiser_id,
 usage_updated_timestamp,
 SUM(budget*budget_usage_percentage/100) AS cost
FROM budget_usages
GROUP BY 1,2

But as you can see the cost decrease during the day because for the same timestamp I have a record for one campaign and not for the other. I cannot use date_trunc(‘hour’, timestamp) because they are random. The only thing I know is that the percentage resets to 0 at midnight.
My idea is that I need to consider, for each minute, the last updated value for each existing campaign (budget_scope_id) and sum it.

FIDDLE: https://dbfiddle.uk/sTxfucZ1

My desidered results is:

Timestamp Cost Notes
2024-01-01 7:00 250 1000 * 0.25
2024-01-01 8:00 1600 3000 * 0,45 + 1000 * 0,25
2024-01-01 10:00 1650 3000 * 0.45 + 1000 * 0.30
Tags:

2

Answers


  1. 0

    My idea is that I need to consider, for each minute, the last updated value for each existing campaign (budget_scope_id) and sum it.

    Yes, except that you cannot just sum the values since they are already cumulated (per day). So you would first need to get the delta of each update, per campaign (and considering the day). This is possible using the LAG window function. Then afterwards you can sum these deltas, across campaigns, with a window function that aggregates per day. Throw in some zero values for restarting at each midnight and you get

    WITH expenses as (
  SELECT
    advertiser_id,
    marketplace_id,
    usage_updated_timestamp,
    budget * budget_usage_percentage / 100. 
    - LAG(budget * budget_usage_percentage / 100, 1, 0)OVER w1 AS delta
  FROM budget_usages
  WINDOW w1 AS (PARTITION BY advertiser_id, 
                             marketplace_id, 
                             budget_scope_id, 
                             date_trunc( 'day'
                                        ,usage_updated_timestamp) 
                ORDER BY usage_updated_timestamp)
UNION ALL
  SELECT DISTINCT
    advertiser_id,
    marketplace_id,
    date_trunc('day', usage_updated_timestamp),
    0 AS delta
  FROM budget_usages
)
SELECT
  advertiser_id,
  marketplace_id,
  usage_updated_timestamp AS timestamp,
  SUM(delta)OVER w2 AS cost
FROM expenses
WINDOW w2 AS (PARTITION BY advertiser_id, 
                           marketplace_id, 
                           date_trunc('day', usage_updated_timestamp) 
                 ORDER BY usage_updated_timestamp)
ORDER BY 1, 2, 3

    (updated fiddle)

    Login or Signup to reply.
  2. 0

    LATERAL subqueries and window functions let you define how far back you want to reach, then look back from a row: demo

    select *, current_total_spending / current_total_budget * 100 as "% spent"
from budget_usages as "then",
lateral
( select sum(budget)::float   as current_total_budget,
         sum(spending)::float as current_total_spending
  from( select distinct on(advertiser_id,budget_scope_id)
               budget,
               budget_usage_percentage*budget/100. spending
        from budget_usages as "by_then"
        where "by_then".usage_updated_timestamp
               <="then".usage_updated_timestamp
          and "by_then".usage_updated_timestamp::date
               = "then".usage_updated_timestamp::date
        order by advertiser_id,
                 budget_scope_id, 
                 usage_updated_timestamp desc)_)_
order by usage_updated_timestamp;
    usage_updated_timestamp budget_scope_id budget budget_usage_percentage % spent
    2024-01-01 07:00 1 10000.00 25.00 25.00
    2024-01-01 08:00 2 3000.00 45.00 29.62
    2024-01-01 10:00 1 10000.00 30.00 33.46
    2024-01-01 12:00 2 3000.00 60.00 36.92
    2024-01-01 15:00 2 3000.00 100.00 46.15
    2024-01-01 16:00 2 6000.00 50.00 37.50
    2024-01-01 23:00 2 6000.00 100.00 56.25
    2024-01-02 07:00 1 10000.00 10.00 10.00
    2024-01-02 08:00 2 6000.00 10.00 10.00

    You don’t need to round() to get rid of trailing zeroes if you instead cast to ::float – their default output format rounds those off. I’m showing rounded to 2 decimal places to simplify the demo.

    The distinct on picks the latest budgets and spendings for each advertiser_id and budget_scope_id for each row you’re looking back from.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search