Subject: Re: how many times
To: pgsql-sql@postgresql.org
References: 
 <B75CD08C73BD3543B97E4EF3964B7D7047D3E9E8@CITESMBX1.ad.uillinois.edu>
From: Rob Sargent <robjsargent@gmail.com>
Message-ID: <864a1386-2c98-5060-1a72-d663671af917@gmail.com>
Date: Wed, 23 Aug 2017 12:15:52 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D7047D3E9E8@CITESMBX1.ad.uillinois.edu>
Content-Type: multipart/alternative;
 boundary="------------F0A2FC490E4AC2746919943B"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------F0A2FC490E4AC2746919943B
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit


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

--------------F0A2FC490E4AC2746919943B
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <p><br>
    </p>
    <br>
    <div class="moz-cite-prefix">On 08/23/2017 12:03 PM, Campbell, Lance
      wrote:<br>
    </div>
    <blockquote
cite="mid:B75CD08C73BD3543B97E4EF3964B7D7047D3E9E8@CITESMBX1.ad.uillinois.edu"
      type="cite">
      <meta http-equiv="Content-Type" content="text/html;
        charset=windows-1252">
      <meta name="Generator" content="Microsoft Word 15 (filtered
        medium)">
      <style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
      <div class="WordSection1">
        <p class="MsoNormal">I am not for sure how to do the below.  I
          was hoping someone could share their thoughts.<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Assume I have a table called T1.  It
          contains a single filed called “viewed” that is of type
          timestamp.<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Every time someone views a particular web
          page we insert the current timestamp into T1.<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Now I need to ask this business question:<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">On average in any consecutive five minute
          time frame how many times is the web page viewed?<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Thanks,<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal">Lance<o:p></o:p></p>
      </div>
    </blockquote>
    window functions are your friends<br>
    <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/static/functions-window.html">https://www.postgresql.org/docs/current/static/functions-window.html</a><br>
  </body>
</html>

--------------F0A2FC490E4AC2746919943B--