agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: agharta <agharta82@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
Date: Thu, 19 Mar 2015 17:30:31 +0100
Message-ID: <550AF9A7.2090404@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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









-- 
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 (4+ messages)  latest in thread

Message-ID: <550AF9A7.2090404@gmail.com>
Permalink:  ../550AF9A7.2090404@gmail.com/
Also on:    postgresql.org/message-id/550AF9A7.2090404@gmail.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: agharta82@gmail.com
  Subject: Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
  In-Reply-To: <550AF9A7.2090404@gmail.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