Angharad:

A cartesian-product like this may generate a huge (read that unreadable and unusable) report if you have many rows. If you don't have many rows, then it's not a tough problem anyway ;^).

Perhaps an alternative report structure might be nicer. Instead of the id-based cartesian product, you might do a cartesian product of the group codes and display them as a table. Then for each cell of the table, you can have a separate cartesian product as another page in your report. Then you could glue it together with some perl, something like (untested, incomplete):

#####
# Header page
#####
print "Group vs. Group summary\n"
.     "-----------------------\n\n"
.     "Grp  Grp  Stat1  Stat2  ...\n"
.     "---- ---- ------ ------ ------\n";
# Do SQL query here to generate cross-tab for Group pairs:
my $S=$DB->prepare(
    "SELECT a.groupcode, b.groupcode, /* compute stats */ "
   ."FROM my_table a, my_table b "
   ."WHERE a.entry_code != b.entry_code "
   ."GROUP BY a.groupcode, b.groupcode "
   ."ORDER BY a.groupcode, b.groupcode"
) or die;
$S->execute or die;
for (my $ar=$S->fetchrow_arrayref) {
    # print junk
    # Store the pair of group codes.  (I'm assuming that the
    # stats are symmetric, so only push group code pairs once
    # so we don't get two pages of details for each pair.)
    # (?Syntax OK? I've never tried pushing an arrayref onto
    # an array before...)
    push @grp_pairs, \($ar[0], $ar[1]) if $ar[0] < $ar[1];
}

#####
# Detail view pages
#####
$S = $DB->prepare(
     "SELECT a.entry_id, a.some_other_col, b.entry_id, "
    ."       b.some_other_col "
    ."FROM my_table a, my_table b "
    ."WHERE a.entry_code=? and b.entry_code=? "
    ."ORDER BY a.entry_id, b.entry_id"
) or die;
# (?Syntax again? probably wrong and needs fixing)
while (my ($grp1, $grp2) = pop @grp_pairs) {
   print $FormFeed
   .     "DETAILS for $grp1 vs. $grp2\n\n"
   .     "A.ID A.ColB B.ID B.ColB Stat1 Stat2 ...\n"
   .     "---- ------ ---- ------ ----- ----- ------\n";
   # Generate group vs. group details
   $S->execute($grp1, $grp2) or die;
   for (my $ar=$S->fetchrow_arrayref) {
      # print junk...
   }
}

[download]
--roboticus

In reply to Re^2: an efficient program? by roboticus
in thread an efficient program? by Angharad

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.