Group hourly stats into daily entries, with a diff for each day [SQL]

user_2000246 0
Viewed 36

I am trying to condense my hourly stats DB into a daily "diff" output.

I have a database as follows:

id timestamp points
1 2021-04-16 09:15:00 1855
2 2021-04-16 09:15:00 1261
1 2021-04-16 10:15:00 1855
2 2021-04-16 10:15:00 1264
1 2021-04-16 11:15:00 1855
2 2021-04-16 11:15:00 1264
. . .
1 2021-04-17 09:15:00 1857
2 2021-04-17 09:15:00 1282
1 2021-04-17 10:15:00 1857
2 2021-04-17 10:15:00 1283
1 2021-04-17 11:15:00 1857
2 2021-04-17 11:15:00 1284

I can condense this down into daily stats for each user (for a given month) with the query:

SELECT CAST(timestamp AS DATE) AS TIMESTAMP, MAX(points ) AS pts
FROM table_name
WHERE EXTRACT(MONTH FROM timestamp) = 4 AND EXTRACT(YEAR FROM timestamp) = 2021
AND id = 2
GROUP BY CAST(timestamp AS DATE)
ORDER BY CAST(timestamp AS DATE);

This gives me the output:

timestamp pts
2021-04-16 1274
2021-04-17 1284

But what I would like to see is the diff per day instead, like below. Ideally I'd even like to provide an initial value for the first entry to "diff" against, say 1200 for this example, so the first result would be 74. But I could live with a 0 for the first value.

timestamp pts
2021-04-16 0
2021-04-17 10

Not suite sure where to start, not very well versed on aggregates, which I'm guessing I might need? Using PostgreSQL 13.

sql
1 Answers

I think you just want LAG():

SELECT CAST(timestamp AS DATE) AS TIMESTAMP,
       MAX(points) AS pts,
       COALESCE(MAX(points) - LAG(MAX(points)) OVER (ORDER BY MIN(timestamp)), 0) as diff
FROM table_name
WHERE EXTRACT(MONTH FROM timestamp) = 4 AND
      EXTRACT(YEAR FROM timestamp) = 2021 AND
      id = 2
GROUP BY CAST(timestamp AS DATE)
ORDER BY CAST(timestamp AS DATE);

Note that I would be inclined to write the query using more Postgres'y syntax:

SELECT timestamp::DATE AS TIMESTAMP,
       MAX(points) AS pts,
       COALESCE(MAX(points) - LAG(MAX(points)) OVER (ORDER BY MIN(timestamp)), 0) as diff
FROM table_name
WHERE timestamp >= '2021-04-01' AND
      timestamp < '2021-05-01' AND
      id = 2
GROUP BY timestamp::DATE
ORDER BY timestamp::DATE;

The change to the WHERE clause is more optimizer friendly, allowing the use of an appropriate index, if available.

user_11440350
Related