Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
От | agharta |
---|---|
Тема | Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value |
Дата | |
Msg-id | 550AF9A7.2090404@gmail.com обсуждение исходный текст |
Ответы |
Re: Detect which sum of cartesian product (+ any combination of
n records in tables) exceeds a value
|
Список | pgsql-sql |
Hi all, I hope someone can helps me.... I have a problem detecting a sum of cartesian product of tables. ---------------------------- Test case: //CREATE TABLES create table t1 ( id serial, field_1 integer); create table t2 ( id serial, field_1 integer); create table t3 ( id serial, field_1 integer); create table t4 ( id serial, field_1 integer); //FILL TABLES insert into t1 (field_1) select cast(random()*10 as integer) from generate_series(1,10); insert into t2 (field_1) select cast(random()*10 as integer) from generate_series(1,10); insert into t3 (field_1) select cast(random()*10 as integer) from generate_series(1,10); insert into t4 (field_1) select cast(random()*10 as integer) from generate_series(1,10); -------------------- Example: i have 4 tables with fields, i would detect which combination of field_1 in any table exceed a value (eg. 35). Simple, ugly & slow but simple: select * from t1, t2,t3,t4 where t1.field_1 + t2.field_1 + t3.field_1 + t4.field_1 >35 It works. Now my question: i would determine which combination on field_1 of t1,t2,t3 plus a combination(any) of 2 records on field_1 of t4, exceeds a value (eg. 35) It should be something like t1.field_1 + t2.field_1 + t3.field_1 + ( any combination of 2 records of t4.field_1) > 35 Suppose i have these records in tables (field_1), for simple explain of my problem: t1 = 1 t2 = 5 t3 = 4 t4 = 1,3,4 the combination of 2 record on t4.field_1 should be: 1+5+4 + ( 1+3) 1+5+4 + ( 1+4) 1+5+4 + ( 3+1) 1+5+4 + ( 3+4) 1+5+4 + ( 4+1) 1+5+4 + ( 4+3) How to do it??? This is a static test case with a static (2 records) problem, in my production db it could be any combination (2,3,4,5+ records ) of field_1 of any table. Hope I was clear, Best regards and thanks in advance, Agharta
В списке pgsql-sql по дате отправления: