Or you can use this one (previous one mabye to fixed). It allows for a
threshold that you can play with
//------------ SP start -------------------------------------------
CREATE OR ALTER PROCEDURE CALC_VALS_ADV (
threshold float)
returns (
param varchar(50),
id integer,
val float)
as
declare variable v_param varchar(50);
declare variable v_id integer;
declare variable v_val float;
declare variable v_hoev integer;
declare variable v_last_id integer;
declare variable thres_above float;
declare variable thres_below float;
begin
v_last_id = -1;
for select cpv.id ,cpv.val ,count(cpv.id)
from criticalparamvals cpv
group by id,val
order by 1,3 desc
into :v_id,:v_val,:V_HOEV do begin
if (v_last_id <> v_id) then begin
/*Biggest group to work from */
v_last_id = v_id;
thres_above = v_val + threshold;
thres_below = v_val - threshold;
end
if ((v_val <= thres_above) and (v_val >= thres_below)) then begin
for select cp.param,cpv.id ,cpv.val
from criticalparams cp , criticalparamvals cpv
where cp.param = cpv.param
and cpv.id = :V_ID
and cpv.val = :v_val
group by param, id,val into :param,:id,:val do begin
suspend;
end
end
end
end
//------------------- sp end ---------------------------
Ran with threshold of 0.5
PARAM ID VAL
a 1 0
b 1 0
a 2 0
b 2 0
a 3 0
b 3 0
a 4 0
b 4 0
a 5 0
b 5 0
then ran with threshold of 1.5 (see how C is taken into account also in certain
levels)
PARAM ID VAL
a 1 0
b 1 0
a 2 0
b 2 0
a 3 0
b 3 0
c 3 1
a 4 0
b 4 0
c 4 1
a 5 0
b 5 0
Hope this is what you where looking for
Johannes Pretorius
South Africa
________________________________________
From: [email protected] [[email protected]]
Sent: 07 April 2015 08:03 AM
To: [email protected]
Subject: [firebird-support] Recursive CTE question
Hi everyone!
This might not be a straightforward Firebird question, but I'm hoping there's a
feature I'm unaware of that can help me beyond plain-vanilla SQL.
I have two tables. The first is a list of names of "critical parameters," and
the second relates certain object IDs, critical parameter names, and critical
parameter values:
CREATE TABLE CRITICALPARAMS
(
PARAM Varchar(32) NOT NULL,
INDX INTEGER NOT NULL,
CONSTRAINT PK_CRITICALPARAMS_1 PRIMARY KEY (PARAM),
CONSTRAINT UNQ_CRITICALPARAMS_1 UNIQUE (INDX)
);
CREATE TABLE CRITICALPARAMVALS
(
ID INTEGER NOT NULL,
PARAM Varchar(32) NOT NULL,
VAL Float NOT NULL,
CONSTRAINT PK_CRITICALPARAMVALS_1 PRIMARY KEY (DATAPOINTHASH,PARAM)
);
Let's suppose we have a four-dimensional space:
insert into CRITICALPARAMS values ('a', 1);
insert into CRITICALPARAMS values ('b', 2);
insert into CRITICALPARAMS values ('c', 3);
insert into CRITICALPARAMS values ('foo', 4);
...and a handful of objects in that space:
insert into CRITICALPARAMVALS values (1, 'a', 0.0);
insert into CRITICALPARAMVALS values (1, 'b', 0.0);
insert into CRITICALPARAMVALS values (1, 'c', 2.0);
insert into CRITICALPARAMVALS values (1, 'foo', 99.0);
insert into CRITICALPARAMVALS values (2, 'a', 0.0);
insert into CRITICALPARAMVALS values (2, 'b', 0.0);
insert into CRITICALPARAMVALS values (2, 'c', 2.0);
insert into CRITICALPARAMVALS values (2, 'foo', 99.0);
insert into CRITICALPARAMVALS values (3, 'a', 0.0);
insert into CRITICALPARAMVALS values (3, 'b', 0.0);
insert into CRITICALPARAMVALS values (3, 'c', 1.0);
insert into CRITICALPARAMVALS values (3, 'foo', 98.0);
insert into CRITICALPARAMVALS values (4, 'a', 0.0);
insert into CRITICALPARAMVALS values (4, 'b', 0.0);
insert into CRITICALPARAMVALS values (4, 'c', 1.0);
insert into CRITICALPARAMVALS values (4, 'foo', 98.0);
insert into CRITICALPARAMVALS values (5, 'a', 0.0);
insert into CRITICALPARAMVALS values (5, 'b', 0.0);
insert into CRITICALPARAMVALS values (5, 'c', 2.0);
insert into CRITICALPARAMVALS values (5, 'foo', 98.0);
The problem is to partition the critical parameter space, grouping together all
object IDs that have the same parameter values. We can think of using a "seed"
object ID, and asking what other IDs belong to the same partition as the seed
object.
In our example, objects 1 and 2 form a partition, 3 and 4 form another, and 5
forms a third. All five objects are equal in the critical parameters a and b,
but differ in parameters c and foo.
Is there any way to solve this using plain-vanilla SQL? How about a recursive
CTE?
I've solved the problem crudely, using EXECUTE STATEMENT in a stored procedure,
looping through the seed's critical parameter values and manually constructing
a big SQL statement with as many WHERE clauses as critical parameters, but that
solution doesn't scale as I go up to around 500-1000 critical parameters (or
more!).
My current attempt has petered out at the following point -- I first define a
view that can give me a partition along a single critical parameter
(TEST_FLOAT_EQ is a selectable stored proc that compares two floats for 'good
enough!' equality):
CREATE VIEW VGROUPIDBYPARAM (SEEDID, GROUPMEMBERID, CRITPARAMINDX)
AS
select a.id as seedid, b.id as groupmemberid, c.INDX as critparamindx
from CRITICALPARAMVALS a join CRITICALPARAMVALS b
on a.PARAM=b.param and (exists (select isequal from TEST_FLOAT_EQ(a.val, b.val,
1e-5) where ISEQUAL=1))
join CRITICALPARAMS c on b.param=c.PARAM;
...and then I want to use the VGROUPIDBYPARAM view inductively, in something
like the following partially-complete select:
SELECT a1.SEEDID, a6.GROUPMEMBERID
FROM VGROUPIDBYPARAM a1 join VGROUPIDBYPARAM a2 on a1.SEEDID=a2.SEEDID and
a1.GROUPMEMBERID=a2.GROUPMEMBERID
join VGROUPIDBYPARAM a3 on a1.SEEDID=a3.SEEDID and
a2.GROUPMEMBERID=a3.GROUPMEMBERID
join VGROUPIDBYPARAM a4 on a1.SEEDID=a4.SEEDID and
a3.GROUPMEMBERID=a4.GROUPMEMBERID
join VGROUPIDBYPARAM a5 on a1.SEEDID=a5.SEEDID and
a4.GROUPMEMBERID=a5.GROUPMEMBERID
join VGROUPIDBYPARAM a6 on a1.SEEDID=a6.SEEDID and
a5.GROUPMEMBERID=a6.GROUPMEMBERID
...
where a1.CRITPARAMINDX=1
and a2.CRITPARAMINDX=2
and a3.CRITPARAMINDX=3
and a4.CRITPARAMINDX=4
and a5.CRITPARAMINDX=5
and a6.CRITPARAMINDX=6
...
At the end of this inductive process (which I'm hoping a recursive CTE can
imitate), the only surviving records that made it through the pile of JOINS
have group member ID's belong to the same partition as the seed ID.
Many thanks to anyone that can help me solve this efficiently!
Elias Sabbagh
Victor Technologies, LLC
ehs AT sabbagh.com
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
