1

Pretty new to SQL and have hit a roadblock.

I have this query, which works fine:

SELECT 
(COUNT(*)::float / (current_date - '2017-05-17'::date)) AS "avg_per_day"
FROM "table" tb;

I now want it to include only data from the last month, not all time.

I've tried doing something along the lines of:

SELECT 
(COUNT(*)::float / (current_date - (current_date - '1 month' ::date)) AS "avg_per_day"
FROM "table" tb;

The syntax is clearly wrong, but I am not sure what the right answer is. Have googled around and tried various options to no avail.

I can't use a simple AVG because the number I require is an AVG per day for the last month of data. Thus I've done a count of rows divided by the number of days since the first occurrence to get my AVG per day.

I have a column which tells me the date of the occurrence, however there are multiple rows with the same date in the dataset. e.g. 

created_at 
----------------------------
Monday 27th June 2017 12:00 
Monday 27th June 2017 13:00 
Tuesday 28th June 2017 12:00

and so on.

I am counting the number of occurrences per day and then need to work out an average from that, for the last month of results only (they date back to May).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Define "last month" exactly. This can mean a number of things. And always disclose your version of Postgres as well as your table definition. There may be corner cases depending on constraints and exact data types. Can there be days without entries at all? Do these count as `0`, lowering the average, or not count at all? – Erwin Brandstetter Aug 26 '17 at 11:42

3 Answers3

0

I believe Postgresql and other RDBMS has AVG() to calculate average.

SELECT AVG(tb.columnName) AS avg_per_month
FROM someTable tb
WHERE  
 tb.createdDate >= [start date of month] AND 
 tb.createdDate <= [end date of month]

Edit: I subtract current date with INTERVAL. I am on mobile phone so I cannot test.

SELECT 
(COUNT(*)::float / (current_date -  ( current_date -  INTERVAL '1 month')) AS "avg_per_day"
FROM "table" tb;
Zamrony P. Juhara
  • 5,222
  • 2
  • 24
  • 40
  • Ah, should have explained myself better. I believe I can't use a simple AVG because the number I require is an AVG per day for the last month of data. I have a column which tells me the date of the occurrence, however there are multiple rows with the same date in the dataset. e.g. created_at Monday 27th June 2017 12:00 Monday 27th June 2017 13:00 Tuesday 28th June 2017 12:00 and so on and so forth I am counting the number of occurrences per day and then need to work out an average from that, for the last month. –  Aug 26 '17 at 10:38
0

I think you just need a where clause:

SELECT 
(COUNT(*)::float / (current_date - (current_date - '1 month' ::date)) AS "avg_per_day"
FROM "table" tb
WHERE created_at > (current_date - '1 month' ::date)
Jasen
  • 11,837
  • 2
  • 30
  • 48
0

The answer depends on the exact definition of "last month" and the exact definition of "average count".

Assuming:

  • Your column is defined created_at timestamptz NOT NULL
  • You want the average number of rows per day - days without any rows count as 0.
  • Cover 30 days exactly, excluding today.

SELECT round(count(*)::numeric / 30, 2)  -- simple now with a fixed number of days
FROM   tbl
WHERE  created_at >= (now()::date - 30)
AND    created_at <   now()::date        -- excl. today

Rounding is optional, but you need numeric instead of float to use round() this way.

Not including the current day ("today"), which is ongoing and may result in a lower, misleading average.

If "last month" is supposed to mean something else, you need to define it exactly. Months have between 28 and 31 days, this can mean various things. And since you obviously operate with timestamp or timestamptz, not date, you also need to be aware of possible implications of the time of day and the current time zone. The cast to date (or the definition of "day" in general) depends on your current timezone setting while operating with timestamptz.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks! This seems to have worked a treat & given me a reasonable answer. Just to double check - days where the count is 0 are still included in the average? I don't want my answer to be artificially high. –  Aug 26 '17 at 16:12
  • @EliseNunn: Yes. Also means if you start this with only a couple of days in the data yet, missing days will all be counted as 0 and you get a low avg - which may or may not be what you want. – Erwin Brandstetter Aug 26 '17 at 16:17
  • That's absolutely fine and works for the purpose - cheers for helping out! –  Aug 26 '17 at 16:18
  • @EliseNunn: I had an off-by-1 error. With `>=` it needs to be `- 30` instead of `- 31` to cover 30 days. – Erwin Brandstetter Aug 26 '17 at 16:26