agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: hubert depesz lubaczewski <depesz@depesz.com>
To: Rishi Ranjan <rishiranjan706@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pgsql-function
Date: Tue, 14 Apr 2015 19:39:46 +0200
Message-ID: <20150414173946.GA20194@depesz.com> (raw)
In-Reply-To: <CALiz8WShL07OjnZySwvmdpYcwDi57JX_WxGSKt69k9kPxLDb8g@mail.gmail.com>
References: <CALiz8WShL07OjnZySwvmdpYcwDi57JX_WxGSKt69k9kPxLDb8g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
depesz
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (8+ messages) latest in thread
Message-ID: <20150414173946.GA20194@depesz.com>
Permalink: ../20150414173946.GA20194@depesz.com/
Also on: postgresql.org/message-id/20150414173946.GA20194@depesz.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: depesz@depesz.com, rishiranjan706@gmail.com
Subject: Re: pgsql-function
In-Reply-To: <20150414173946.GA20194@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox