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

<div class="moz-cite-prefix"><br /> On 03/19/2015 06:05 PM, David G. Johnston wrote:<br /></div><blockquote
cite="mid:CAKFQuwY27=A_Y3HjndKhAQzZtg4Y3wAHfDCXvEiHL6fMciq_ng@mail.gmail.com"type="cite"><div dir="ltr"><div
class="gmail_extra"><br/></div><div class="gmail_extra"><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">​Ilikely would not be alive if I tried executing it on any non-trivial
sizeddatabase though.</div></div></div></blockquote><br /> Me too :) !<br /><br /><blockquote
cite="mid:CAKFQuwY27=A_Y3HjndKhAQzZtg4Y3wAHfDCXvEiHL6fMciq_ng@mail.gmail.com"type="cite"><div dir="ltr"><div
class="gmail_extra"><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif">As an 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></blockquote><br /> You're right, this should
bethe fastest implementation possible, but cross/cartesian matching is very slow with a huge amount of data (it is
natural).<br /><br /> I think that a simple & dynamic (t4/t4/t4/t4... n times) solution is not possible, as 9.4 PG
version.Correct me if i am wrong.<br /><br /><div id="gt-src-tools"><div id="gt-src-tools-l"><div id="gt-input-tool"
style="display:inline-block;"><div id="itamenu"><span class="ita-kd-inputtools-div"></span></div></div></div></div><div
class="almost_half_cell"id="gt-res-content"><div dir="ltr" style="zoom:1"><span class="short_text" id="result_box"
lang="en"><spanclass="hps alt-edited">I hoped</span> <span class="hps">that there was</span> <span class="hps">a
magic-trick-functionthat would resolve the problem. Nope. :(<br /><br /> I need to review & rewrite my
db/applicationto solve the problem in another way. <br /><br /></span></span><br /><span class="short_text"
id="result_box"lang="en"><span class="hps"><span class="short_text" id="result_box" lang="en"><span class="hps">I owe
you</span><span class="hps">a beer</span></span>, thanks a lot for your suggestions.<br /><br /><br /> Cheers,<br /><br
/>Agharta<br /><br /><br /></span></span></div></div><br />