From: | Michael Moore <michaeljmoore(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "Campbell, Lance" <lance(at)illinois(dot)edu>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: how many times |
Date: | 2017-08-23 21:13:53 |
Message-ID: | CACpWLjN7Srz=cz4-piVXYLTzQOa0ortoY=6medVPHnNOwLtPVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Wed, Aug 23, 2017 at 11:03 AM, Campbell, Lance <lance(at)illinois(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Moore | 2017-08-23 21:19:03 | Re: how many times |
Previous Message | David G. Johnston | 2017-08-23 19:12:21 | Re: how many times |