PostgreSQL Daily Sum – Getting Sum of Each Day for Last 30 Days

performancepostgresqlquery-performance

I can get a list of each day of last 30 days row by row with this query and it works..
But I don't think that it is the best one since the "WHERE" part is messed up. I draw a graph and I just want to get daily_total_invoice_amount for sum of each day of last 30 days.

SELECT
  sum(b.invoice_amount) daily_total_invoice_amount,
  extract(day from a.order_creation_date)   as day,
  extract(month from a.order_creation_date) as month
FROM orders a
  INNER JOIN order_items b
    ON a.order_id = b.order_id
WHERE
  order_creation_date BETWEEN (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '2 DAY') 
AND CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY month, day
    order by month DESC, day DESC

Best Answer

Why include tomorrow (CURRENT_DATE + INTERVAL '1 DAY')? A future date in order_creation_date would normally indicate a data error. I would not even include today in the statistic, since that is typically incomplete and misleading until the day is over. Consequently I use the last 30 days up until yesterday in my query.

Assuming order_creation_date is an actual date, just GROUP BY and ORDER BY that date, that's simpler an cheaper than using extracted month and day numbers for the same. If it's not a date, but a timestamp or timestamptz, just cast to date to group by the day: o.order_creation_date::date. Be aware that the result depends on the timezone setting of the session when dealing with timestamptz (Day boundaries are defined by the time zone.)

Also assuming you want a result row for every single one of the 30 days. To make sure, days without orders don't go missing, generate the series of days with generate_series() and LEFT JOIN to that. You can generate timestamps, but I use the simpler integer variant while we are operating with dates. We can just subtract an integer from a date to subtract days.

SELECT CURRENT_DATE - i AS date
     , sum(oi.invoice_amount) AS daily_total_invoice_amount
FROM   generate_series (1, 30) i -- to return exactly 30 rows
LEFT   JOIN orders      o ON o.order_creation_date = CURRENT_DATE - i
LEFT   JOIN order_items oi USING (order_id)
GROUP  BY i    -- effectively group by days
ORDER  BY i;   -- effectively descending dates
Related Question