agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedhow many times
7+ messages / 4 participants
[nested] [flat]
* how many times
@ 2017-08-23 18:03 Campbell, Lance <lance@illinois.edu>
2017-08-23 18:15 ` Re: how many times Rob Sargent <robjsargent@gmail.com>
2017-08-23 19:12 ` Re: how many times David G. Johnston <david.g.johnston@gmail.com>
2017-08-23 21:19 ` Re: how many times Michael Moore <michaeljmoore@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Campbell, Lance @ 2017-08-23 18:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
@ 2017-08-23 18:15 ` Rob Sargent <robjsargent@gmail.com>
2017-08-23 18:35 ` Re: how many times Campbell, Lance <lance@illinois.edu>
2 siblings, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2017-08-23 18:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
2017-08-23 18:15 ` Re: how many times Rob Sargent <robjsargent@gmail.com>
@ 2017-08-23 18:35 ` Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Campbell, Lance @ 2017-08-23 18:35 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
@ 2017-08-23 19:12 ` David G. Johnston <david.g.johnston@gmail.com>
2017-08-23 21:13 ` Re: how many times Michael Moore <michaeljmoore@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2017-08-23 19:12 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
2017-08-23 19:12 ` Re: how many times David G. Johnston <david.g.johnston@gmail.com>
@ 2017-08-23 21:13 ` Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Moore @ 2017-08-23 21:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Campbell, Lance <lance@illinois.edu>; 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.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.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
@ 2017-08-23 21:19 ` Michael Moore <michaeljmoore@gmail.com>
2017-08-24 14:46 ` Re: how many times Campbell, Lance <lance@illinois.edu>
2 siblings, 1 reply; 7+ messages in thread
From: Michael Moore @ 2017-08-23 21:19 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: how many times
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
2017-08-23 21:19 ` Re: how many times Michael Moore <michaeljmoore@gmail.com>
@ 2017-08-24 14:46 ` Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Campbell, Lance @ 2017-08-24 14:46 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
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<mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-08-24 14:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-23 18:03 how many times Campbell, Lance <lance@illinois.edu>
2017-08-23 18:15 ` Rob Sargent <robjsargent@gmail.com>
2017-08-23 18:35 ` Campbell, Lance <lance@illinois.edu>
2017-08-23 19:12 ` David G. Johnston <david.g.johnston@gmail.com>
2017-08-23 21:13 ` Michael Moore <michaeljmoore@gmail.com>
2017-08-23 21:19 ` Michael Moore <michaeljmoore@gmail.com>
2017-08-24 14:46 ` Campbell, Lance <lance@illinois.edu>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox