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: firebird-support@yahoogroups.com [firebird-support@yahoogroups.com]
Sent: 07 April 2015 08:03 AM
To: firebird-support@yahoogroups.com
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