Re: pgsql-function

On Tue, Apr 14, 2015 at 10:39 AM, hubert depesz lubaczewski <
depesz(at)depesz(dot)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.​