Postgres Pro
Downloads
Home   >   mailing lists

Re: [SQL] how many times - Mailing list pgsql-sql

From Michael Moore
Subject Re: [SQL] how many times
Date
Msg-id CACpWLjN7Srz=cz4-piVXYLTzQOa0ortoY=6medVPHnNOwLtPVw@mail.gmail.com
Whole thread Raw
In response to Re: [SQL] how many times  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
Sounds like a perfect use case for buckets. https://www.postgresql.org/docs/9.1/static/functions-math.html  You might have to use a function to convert the timestamps to decimal.

On Wed, Aug 23, 2017 at 12:12 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Aug 23, 2017 at 11:03 AM, Campbell, Lance <lance@illinois.edu> wrote:

On average in any consecutive five minute time frame how many times is the web page viewed?


​Do you want:

(00,05], (05,10], etc
or
(00,05], (01,06]​, (02, 07], etc

In either case you would want to build out a table containing that start and end points (two columns or an actual range-typed column), then join T1 to that table with an ON clause (t1.timestamp BETWEEN r1.rangestart AND r1.rangeend)

Then:
SELECT r1.rangein, count(t1.id)
FROM t1 RIGHT JOIN r1 ON (...)
GROUP BY r1.rangeid

You have to build the range table if you want to be able to show zero counts.

David J.


pgsql-sql by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [SQL] how many times
Next
From: Michael Moore
Date:
Subject: Re: [SQL] how many times