agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedpgsql-function
8+ messages / 3 participants
[nested] [flat]
* pgsql-function
@ 2015-04-14 10:39 Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:39 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 8+ messages in thread
From: Rishi Ranjan @ 2015-04-14 10:39 UTC (permalink / raw)
To: pgsql-sql
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
like for 418 time difference is (3/4/2014 0:00 - 3/4/2014/0.05) and it
should get multiplied with severity 5 . in this way we need to calculate
for every ap_key and store the result in separate table.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
@ 2015-04-14 11:27 ` hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:20 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2015-04-14 11:27 UTC (permalink / raw)
To: Rishi Ranjan <rishiranjan706@gmail.com>; +Cc: pgsql-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
1. Why does it have to be a function? Can't it simply be a query?
2. Which pg version do you use?
3. Which severity to use? from the earlier FIRSTOCCURRENCE, or later, or
simply higher/lower SEVERITY?
Basically what you're looking for is most likely some group by with
min/max.
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
@ 2015-04-14 17:20 ` Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:28 ` Re: pgsql-function David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rishi Ranjan @ 2015-04-14 17:20 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-sql
we are using version 9.4.
2) The result of above calculation i need to use in different calculation .
means output of this calculation will let us to determine the value of
some parameters of other equation like below
y= ((y2-y1)/(x2-x1 ))x+ (y1- (y2-y1)/(x2-x1 ) x1 )
where x1,y1,x2,y2 parameters are defined and whose value need to be picked
based on value of x (being calculated from above scenario provided with
sample data)
so help me to decide in what way we can proceed
3) from above data in firstoccurence column there is up time and down time
of some device and we need to choose higher severity value .
On 14 April 2015 at 04:27, 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
>
> 1. Why does it have to be a function? Can't it simply be a query?
> 2. Which pg version do you use?
> 3. Which severity to use? from the earlier FIRSTOCCURRENCE, or later, or
> simply higher/lower SEVERITY?
>
> Basically what you're looking for is most likely some group by with
> min/max.
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact
> with it.
>
> http://depesz.com/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:20 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
@ 2015-04-14 17:28 ` David G. Johnston <david.g.johnston@gmail.com>
2015-04-14 17:31 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2015-04-14 17:28 UTC (permalink / raw)
To: Rishi Ranjan <rishiranjan706@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 14, 2015 at 10:20 AM, Rishi Ranjan <rishiranjan706@gmail.com>
wrote:
>
>
> we are using version 9.4.
> 2) The result of above calculation i need to use in different calculation
> . means output of this calculation will let us to determine the value of
> some parameters of other equation like below
>
> y= ((y2-y1)/(x2-x1 ))x+ (y1- (y2-y1)/(x2-x1 ) x1 )
>
> where x1,y1,x2,y2 parameters are defined and whose value need to be picked
> based on value of x (being calculated from above scenario provided with
> sample data)
>
So, is the real data likely to be as perfect as your sample data or do you
need something more robust? With the clean data using the lag window
function will readily solve your problem. But if you could guarantee this
clean of data I would probably have simply created two columns (and yes, I
do know what normalization is) for timestamps - one of which can be null:
the "second" one) and perform the query directly.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:20 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:28 ` Re: pgsql-function David G. Johnston <david.g.johnston@gmail.com>
@ 2015-04-14 17:31 ` Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:37 ` Re: pgsql-function David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rishi Ranjan @ 2015-04-14 17:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: depesz@depesz.com; pgsql-sql
That will be in this format only. We thinked for normalization part but we
got clearance that data will be in this format and have to do processing
On Apr 14, 2015 10:58 PM, "David G. Johnston" <david.g.johnston@gmail.com>
wrote:
> On Tue, Apr 14, 2015 at 10:20 AM, Rishi Ranjan <rishiranjan706@gmail.com>
> wrote:
>
>>
>>
>> we are using version 9.4.
>> 2) The result of above calculation i need to use in different calculation
>> . means output of this calculation will let us to determine the value of
>> some parameters of other equation like below
>>
>> y= ((y2-y1)/(x2-x1 ))x+ (y1- (y2-y1)/(x2-x1 ) x1 )
>>
>> where x1,y1,x2,y2 parameters are defined and whose value need to be
>> picked based on value of x (being calculated from above scenario provided
>> with sample data)
>>
>
> So, is the real data likely to be as perfect as your sample data or do
> you need something more robust? With the clean data using the lag window
> function will readily solve your problem. But if you could guarantee this
> clean of data I would probably have simply created two columns (and yes, I
> do know what normalization is) for timestamps - one of which can be null:
> the "second" one) and perform the query directly.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:20 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:28 ` Re: pgsql-function David G. Johnston <david.g.johnston@gmail.com>
2015-04-14 17:31 ` Re: pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
@ 2015-04-14 17:37 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2015-04-14 17:37 UTC (permalink / raw)
To: Rishi Ranjan <rishiranjan706@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 14, 2015 at 10:31 AM, Rishi Ranjan <rishiranjan706@gmail.com>
wrote:
> That will be in this format only. We thinked for normalization part but we
> got clearance that data will be in this format and have to do processing
>
So write a query that uses the lag() window function to create an inline
view having one record per entry with two timestamp fields, both of which
are non-null. Then calculate using that inline view. I suggest you use a
CTE (WITH clause) for the inline view.
The documentation is quite good for window function, CTE (and the related
select statements) and "inserting to another table". If you try and get
stumped I'll be happy to post specific suggestions but if you want an
answer handed to you you will need to wait for someone else to reply.
David J.
> On Apr 14, 2015 10:58 PM, "David G. Johnston" <david.g.johnston@gmail.com>
> wrote:
>
>> On Tue, Apr 14, 2015 at 10:20 AM, Rishi Ranjan <rishiranjan706@gmail.com>
>> wrote:
>>
>>>
>>>
>>> we are using version 9.4.
>>> 2) The result of above calculation i need to use in different
>>> calculation . means output of this calculation will let us to determine the
>>> value of some parameters of other equation like below
>>>
>>> y= ((y2-y1)/(x2-x1 ))x+ (y1- (y2-y1)/(x2-x1 ) x1 )
>>>
>>> where x1,y1,x2,y2 parameters are defined and whose value need to be
>>> picked based on value of x (being calculated from above scenario provided
>>> with sample data)
>>>
>>
>> So, is the real data likely to be as perfect as your sample data or do
>> you need something more robust? With the clean data using the lag window
>> function will readily solve your problem. But if you could guarantee this
>> clean of data I would probably have simply created two columns (and yes, I
>> do know what normalization is) for timestamps - one of which can be null:
>> the "second" one) and perform the query directly.
>>
>> David J.
>>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
@ 2015-04-14 17:39 ` hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:45 ` Re: pgsql-function David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2015-04-14 17:39 UTC (permalink / raw)
To: Rishi Ranjan <rishiranjan706@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgsql-function
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:39 ` Re: pgsql-function hubert depesz lubaczewski <depesz@depesz.com>
@ 2015-04-14 17:45 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2015-04-14 17:45 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Rishi Ranjan <rishiranjan706@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-04-14 17:45 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-04-14 10:39 pgsql-function Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 11:27 ` hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:20 ` Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:28 ` David G. Johnston <david.g.johnston@gmail.com>
2015-04-14 17:31 ` Rishi Ranjan <rishiranjan706@gmail.com>
2015-04-14 17:37 ` David G. Johnston <david.g.johnston@gmail.com>
2015-04-14 17:39 ` hubert depesz lubaczewski <depesz@depesz.com>
2015-04-14 17:45 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox