Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value

<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span
style="font-family:arial,sans-serif">OnThu, Mar 19, 2015 at 9:30 AM, agharta </span><span dir="ltr"
style="font-family:arial,sans-serif"><<ahref="mailto:agharta82@gmail.com"
target="_blank">agharta82@gmail.com</a>></span><spanstyle="font-family:arial,sans-serif"> wrote:</span><br
/></div><divclass="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><br /> It should be something like  t1.field_1 + t2.field_1 +
t3.field_1+ ( any combination of  2 records of t4.field_1) > 35<br /><span class="HOEnZb"><font color="#888888"><br
/></font></span></blockquote></div><br/></div><div class="gmail_extra"><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">​Icould probably brute-force write such a query in maybe a half-hour.  I
likelywould not be alive if I tried executing it on any non-trivial sized database though.</div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">Asan algorithm:</div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">Createtwo relations (temp tables/views/materialized views), one for
t1/t2/t3and one for t4/t4 each having a single row for every potential combination of rows.  Each table would
contributetwo values, the content of "field_1" and the primary key of the corresponding table.  The new PK would be a
compositeof all the contributing PKs</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br
/></div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif">For each relation, if the sum of the
valuecolumns is > 35 then every single row from the other table will provide a match.  This is your first
output.</div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif">Cross Join the two relations, after removing those
ineach that were matched above, and sum together all 5 fields.  This is your second output.</div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">UnionAll the two outputs together and you have your result.</div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">Itcan be done in one step but this at least gives you a prayer of
executingin reasonable time for meaningfully sized datasets.  You can just write the second part and avoid the union
untilyour data warrants the more complex, but likely faster, setup.</div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">DavidJ.</div></div></div>