Apologies for quite possibly a highly unsuitable post Title. Please feel free to propose a better Title.

A poster on the SQLite list asked the following question: given the following data extracted from the db table 

Field1,Field2,Field3
1.00,Blue,12:00    <-----
40.20,White,12:00
80.30,White,12:00
120.00,White,12:00
126.00,White,12:00 <-----
162.43,White,12:00 <-----
198.86,White,12:00 <-----
235.29,White,12:00 <-----
271.72,Red,03:45   <=====
308.15,White,12:00 <-----
344.58,White,12:00 <-----
381.01,White,12:00 <-----
417.44,White,12:00 <-----
453.87,White,12:00
490.30,White,12:00
526.73,White,12:00
563.16,Red,07:45
599.59,White,12:00
636.02,White,12:00
672.45,White,12:00
708.88,White,12:00
745.31,White,12:00
781.74,White,12:00
818.17,White,12:00
854.60,Blue,12:00  <-----
891.03,White,12:00
963.89,White,12:00
1000.32,White,12:00
1036.75,Red,08:30
1073.18,White,12:00
1109.61,Red,06:00
1146.04,White,12:00
1182.47,White,12:00
1218.90,White,12:00
18516.40,Blue,12:00
1255.33,White,12:00
927.46,White,12:00

[download]

The poster wanted to find a first occurrence of "Red," then searching from this record find the previous 4 records that contain "White," then find the Previous 1 that contains "Blue." Now do the same thing but searching "next". In other words, the following result was desired: 

1.00,Blue,12:00
126.00,White,12:00
162.43,White,12:00
198.86,White,12:00
235.29,White,12:00
271.72,Red,03:45
308.15,White,12:00
344.58,White,12:00
381.01,White,12:00
417.44,White,12:00
854.60,Blue,12:00

[download]

I have marked the desired rows with  <----- and  <=====

A very complicated SQL was proposed, but this is the kind of thing best accomplished by in the application. I have been recuperating from a post-Thanksgiving flu/fever, so to pass away boredom, I decided to write the following solution in my favorite programming language. 

my @res = analyze();
for (@res) {
    print $_->{f1} . ',' . $_->{f2} . ',' . $_->{f3} . "\n";
}

sub analyze {
    
    # get the data as an array of hashes just like DBI would return
    my @data = map { 
        chomp; 
        split /,/; 
        { f1 => $_[0], f2 => $_[1], f3 => $_[2] } 
    } <DATA>;
    
    # arrays for holding bits of results
    my (@res_red, @res_blu, @res_wht, 
        @nxt_res_red, @nxt_res_blu, @nxt_res_wht
     );
    
    # counters for tracking whether we are before or 
    # after the 'Red' divide 
    my $i = -1; my $j;
    
    # array to hold rows *before* the 'Red' divide
    my @seen;
    
    LOOP: for (@data) {
        if ($i >= 0) {

            if ($_->{f2} eq 'White') {
                next LOOP if $j > 3;
                push @nxt_res_wht, $_;
                $j++;
            }
            
            if ($_->{f2} eq 'Blue') {
                push @nxt_res_wht, $_;
                last LOOP;
            }
        }
        else {
            if ($_->{f2} eq 'Red') {
                push @res_red, $_;
                $i++;
                my @rev_seen = reverse @seen;
                my $k;
                REV_WHITE: for (@rev_seen) {
                    if ($_->{f2} eq 'White') {
                        push @res_wht, $_;
                        $k++
                    }
                    last REV_WHITE if $k > 3;
                }
                REV_BLUE: for (@rev_seen) {
                    if ($_->{f2} eq 'Blue') {
                        push @res_blu, $_;
                        last REV_BLUE;
                    }
                }
                push @res_blu, reverse(@res_wht), @res_red;
            }
            else {
                push @seen, $_;
            }
        }
    }
    push @res_blu, @nxt_res_wht, @nxt_res_red;
    return @res_blu;
}


__DATA__
1.00,Blue,12:00
40.20,White,12:00
80.30,White,12:00
120.00,White,12:00
126.00,White,12:00
162.43,White,12:00
198.86,White,12:00
235.29,White,12:00
271.72,Red,03:45
308.15,White,12:00
344.58,White,12:00
381.01,White,12:00
417.44,White,12:00
453.87,White,12:00
490.30,White,12:00
526.73,White,12:00
563.16,Red,07:45
599.59,White,12:00
636.02,White,12:00
672.45,White,12:00
708.88,White,12:00
745.31,White,12:00
781.74,White,12:00
818.17,White,12:00
854.60,Blue,12:00
891.03,White,12:00
963.89,White,12:00
1000.32,White,12:00
1036.75,Red,08:30
1073.18,White,12:00
1109.61,Red,06:00
1146.04,White,12:00
1182.47,White,12:00
1218.90,White,12:00
18516.40,Blue,12:00
1255.33,White,12:00
927.46,White,12:00

[download]

Once you experienced monks get past your laughing at my code (which does work correctly), I would like a lesson into how to approach such class of problems that involve a "window" over an array that spreads on either side of an array element. Of course, more elegant solutions to the above problem are also welcome as they will show me (and other shy monks) a better way.

Update: Responding to Limbic~Region's question, forget for the moment possible "Blue" interleaving among the four "Whites" or other anomalies and deformities. That is how the OP on the SQLite stated the problem, so that is how I have reproduced it above.

--

when small people start casting long shadows, it is time to go to bed

In reply to Extracting array elements on either side of a match by punkish

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.