Postgres Pro
Downloads
Home   >   mailing lists

Thread: [SQL] how many times

[SQL] how many times

From
"Campbell, Lance"
Date:

I am not for sure how to do the below.  I was hoping someone could share their thoughts.

 

Assume I have a table called T1.  It contains a single filed called “viewed” that is of type timestamp.

 

Every time someone views a particular web page we insert the current timestamp into T1.

 

Now I need to ask this business question:

 

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

 

Thanks,

 

Lance

Re: [SQL] how many times

From
Rob Sargent
Date:



On 08/23/2017 12:03 PM, Campbell, Lance wrote:

I am not for sure how to do the below.  I was hoping someone could share their thoughts.

 

Assume I have a table called T1.  It contains a single filed called “viewed” that is of type timestamp.

 

Every time someone views a particular web page we insert the current timestamp into T1.

 

Now I need to ask this business question:

 

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

 

Thanks,

 

Lance

window functions are your friends
https://www.postgresql.org/docs/current/static/functions-window.html

Re: [SQL] how many times

From
"Campbell, Lance"
Date:

I appreciate your response but I don’t see how to apply this to five minute time frames?

 

Thanks,

 

Lance

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Rob Sargent
Sent: Wednesday, August 23, 2017 1:16 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] how many times

 

 

 

On 08/23/2017 12:03 PM, Campbell, Lance wrote:

I am not for sure how to do the below.  I was hoping someone could share their thoughts.

 

Assume I have a table called T1.  It contains a single filed called “viewed” that is of type timestamp.

 

Every time someone views a particular web page we insert the current timestamp into T1.

 

Now I need to ask this business question:

 

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

 

Thanks,

 

Lance

window functions are your friends
https://www.postgresql.org/docs/current/static/functions-window.html

Re: [SQL] how many times

From
"David G. Johnston"
Date:
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.

Re: [SQL] how many times

From
Michael Moore
Date:
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.


Re: [SQL] how many times

From
Michael Moore
Date:
something like this.
select
sum (case when buc = 1 then 1 else 0 end) _1_8_sec,
sum (case when buc = 2 then 1 else 0 end) _2_8_sec,
sum (case when buc = 3 then 1 else 0 end) _3_8_sec,
sum (case when buc = 4 then 1 else 0 end) _4_8_sec,
sum (case when buc = 5 then 1 else 0 end) _5_8_sec,
sum (case when buc = 6 then 1 else 0 end) _6_8_sec,
sum (case when buc = 7 then 1 else 0 end) _7_8_sec,
sum (case when buc = 8 then 1 else 0 end) _1_sec,
sum (case when buc = 9 then 1 else 0 end) _8_8_sec,
sum (case when buc = 10 then 1 else 0 end) _10_8_sec,
sum (case when buc = 11 then 1 else 0 end) _11_8_sec,
sum (case when buc = 12 then 1 else 0 end) _12_8_sec,
count(*) cnt
from 
(select width_bucket( temp_time, 0, 1500 , 11) buc  from mikes_debug_log_vals2  where temp_time is not null) x

On Wed, Aug 23, 2017 at 11:03 AM, Campbell, Lance <lance@illinois.edu> wrote:

I am not for sure how to do the below.  I was hoping someone could share their thoughts.

 

Assume I have a table called T1.  It contains a single filed called “viewed” that is of type timestamp.

 

Every time someone views a particular web page we insert the current timestamp into T1.

 

Now I need to ask this business question:

 

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

 

Thanks,

 

Lance


Re: [SQL] how many times

From
"Campbell, Lance"
Date:

Thanks.

 

From: Michael Moore [mailto:michaeljmoore@gmail.com]
Sent: Wednesday, August 23, 2017 4:19 PM
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] how many times

 

something like this.

select

sum (case when buc = 1 then 1 else 0 end) _1_8_sec,

sum (case when buc = 2 then 1 else 0 end) _2_8_sec,

sum (case when buc = 3 then 1 else 0 end) _3_8_sec,

sum (case when buc = 4 then 1 else 0 end) _4_8_sec,

sum (case when buc = 5 then 1 else 0 end) _5_8_sec,

sum (case when buc = 6 then 1 else 0 end) _6_8_sec,

sum (case when buc = 7 then 1 else 0 end) _7_8_sec,

sum (case when buc = 8 then 1 else 0 end) _1_sec,

sum (case when buc = 9 then 1 else 0 end) _8_8_sec,

sum (case when buc = 10 then 1 else 0 end) _10_8_sec,

sum (case when buc = 11 then 1 else 0 end) _11_8_sec,

sum (case when buc = 12 then 1 else 0 end) _12_8_sec,

count(*) cnt

from 

(select width_bucket( temp_time, 0, 1500 , 11) buc  from mikes_debug_log_vals2  where temp_time is not null) x

 

On Wed, Aug 23, 2017 at 11:03 AM, Campbell, Lance <lance@illinois.edu> wrote:

I am not for sure how to do the below.  I was hoping someone could share their thoughts.

 

Assume I have a table called T1.  It contains a single filed called “viewed” that is of type timestamp.

 

Every time someone views a particular web page we insert the current timestamp into T1.

 

Now I need to ask this business question:

 

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

 

Thanks,

 

Lance