| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | "Campbell, Lance" <lance(at)illinois(dot)edu> |
| Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: how many times |
| Date: | 2017-08-23 19:12:21 |
| Message-ID: | CAKFQuwayoTvjZLsNCAenHBgHp1172HUC5CHzrAfzhRFckY8TFg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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:13:53 | Re: how many times |
| Previous Message | Campbell, Lance | 2017-08-23 18:35:31 | Re: how many times |