[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] pgsql-function
From: "David G. Johnston" <david.g.johnston () gmail ! com>
Date: 2015-04-14 17:45:07
Message-ID: CAKFQuwbeBWNOdAwFsRW1WKh1+OnhTy076+A7qugGvGa9Nct2dA () mail ! gmail ! com
[Download RAW message or body]
On Tue, Apr 14, 2015 at 10:39 AM, hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> On Tue, Apr 14, 2015 at 03:39:38AM -0700, Rishi Ranjan wrote:
> > i have data like below and where ap_key is same for two different
> > first_occurence column . here i need to write a function which can
> > calculate the difference of two timestamp values for a Ap_Key and then
> with
> > difference it should multiply the severity
> >
> > AAA_key AP_KEY FIRSTOCCURRENCE SEVERITY
> > 111 418 3/4/2014 0:00 5
> > 111 418 3/4/2014 0:05 0
> > 112 12 3/4/2014 0:40 4
> > 112 12 3/4/2014 0:45 0
> > 113 13 3/4/2014 1:05 3
> > 113 13 3/4/2014 1:10 0
> > 114 114 3/4/2014 1:30 2
> > 114 114 3/4/2014 1:35 0
> > 115 35 3/4/2014 2:10 1
> > 115 35 3/4/2014 2:15 0
> > 116 116 3/4/2014 10:14 4
> > 116 116 3/4/2014 10:19 0
> > 117 127 3/4/2014 11:45 3
> > 117 127 3/4/2014 11:49 0
> > 118 118 3/4/2014 12:10 2
> > 118 118 3/4/2014 12:14 0
> > 119 19 3/4/2014 12:35 1
> > 119 19 3/4/2014 12:39 0
> > 119 120 3/4/2014 0:00 4
> > 119 120 3/4/2014 1:40 0
>
> Given this data, why don't you simply:
>
> select
> AAA_key,
> AP_KEY,
> max(SEVERITY),
> max(FIRSTOCCURRENCE) - min(FIRSTOCCURRENCE)
> from
> table
> group by
> AAA_key,
> AP_KEY;
>
> and then do whatever math you need on severity or FIRSTOCCURRENCE
> differences.
>
Much nicer :) I was over-thinking things a bit with suggesting window
functions.
This also handles any potential changes to the data which cause more than
2 records per ID to be present.
David J.
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif">On Tue, Apr 14, 2015 at 10:39 AM, hubert depesz \
lubaczewski </span><span dir="ltr" style="font-family:arial,sans-serif"><<a \
href="mailto:depesz@depesz.com" target="_blank">depesz@depesz.com</a>></span><span \
style="font-family:arial,sans-serif"> wrote:</span><br></div><div \
class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span \
class="">On Tue, Apr 14, 2015 at 03:39:38AM -0700, Rishi Ranjan wrote:<br> \
</span><span class="">> i have data like below and where ap_key is same for two \
different<br> > first_occurence column . here i need to write a function which \
can<br> > calculate the difference of two timestamp values for a Ap_Key and then \
with<br> > difference it should multiply the severity<br>
><br>
</span><span class="">> AAA_key AP_KEY FIRSTOCCURRENCE SEVERITY<br>
> 111 418 3/4/2014 0:00 5<br>
> 111 418 3/4/2014 0:05 0<br>
> 112 12 3/4/2014 0:40 4<br>
> 112 12 3/4/2014 0:45 0<br>
> 113 13 3/4/2014 1:05 3<br>
> 113 13 3/4/2014 1:10 0<br>
> 114 114 3/4/2014 1:30 2<br>
> 114 114 3/4/2014 1:35 0<br>
> 115 35 3/4/2014 2:10 1<br>
> 115 35 3/4/2014 2:15 0<br>
> 116 116 3/4/2014 10:14 4<br>
> 116 116 3/4/2014 10:19 0<br>
> 117 127 3/4/2014 11:45 3<br>
> 117 127 3/4/2014 11:49 0<br>
> 118 118 3/4/2014 12:10 2<br>
> 118 118 3/4/2014 12:14 0<br>
> 119 19 3/4/2014 12:35 1<br>
> 119 19 3/4/2014 12:39 0<br>
> 119 120 3/4/2014 0:00 4<br>
> 119 120 3/4/2014 1:40 0<br>
<br>
</span>Given this data, why don't you simply:<br>
<br>
select<br>
AAA_key,<br>
AP_KEY,<br>
max(SEVERITY),<br>
max(FIRSTOCCURRENCE) - min(FIRSTOCCURRENCE)<br>
from<br>
table<br>
group by<br>
AAA_key,<br>
AP_KEY;<br>
<br>
and then do whatever math you need on severity or FIRSTOCCURRENCE<br>
differences.<br></blockquote><div><br></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">Much nicer :) I \
was over-thinking things a bit with suggesting window functions.</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">This also \
handles any potential changes to the data which cause more than 2 records per ID to \
be present.</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">David J.</div> \
</div></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic