Loading ...
Sorry, an error occurred while loading the content.

Re: [PBML] Do I need an inner loop or ?

Expand Messages
  • merlyn@stonehenge.com
    ... Jonathan I hope this is mostly a perlish question. Jonathan I have tables that look something like this: Jonathan
    Message 1 of 7 , Dec 5, 2005
    • 0 Attachment
      >>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:

      Jonathan> I hope this is mostly a perlish question.
      Jonathan> I have tables that look something like this:

      Jonathan> +----+------------+----------+------+------+------+
      Jonathan> | id | date | time | type | val1 | val2 |
      Jonathan> +----+------------+----------+------+------+------+
      Jonathan> | 1 | 2005-08-01 | 04:02:00 | O | | 95 |
      Jonathan> | 2 | 2005-08-01 | 06:08:00 | S | .006 | 92 |
      Jonathan> | 3 | 2005-08-01 | 07:30:00 | S | .007 | 124 |
      Jonathan> | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
      Jonathan> | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
      Jonathan> | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
      Jonathan> | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
      Jonathan> | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
      Jonathan> | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |
      Jonathan> | 10 | 2005-08-02 | 08:00:00 | O | | 128 |
      Jonathan> | 11 | 2005-08-02 | 15:08:00 | S | .005 | 70 |
      Jonathan> | 12 | 2005-08-02 | 18:38:00 | S | .008 | 143 |
      Jonathan> | 13 | 2005-08-02 | 21:45:00 | S | .007 | 113 |


      Jonathan> I'm trying to put data from a single date into %val.
      Jonathan> This code puts all dates into %val. It's about as close
      Jonathan> as I've come.

      First, why are date and time two separate columns?

      Second, to do a sum, do it in the database:

      SELECT date, sum(val1), sum(val2)
      FROM your_table
      GROUP BY 1;

      --
      Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
      <merlyn@...> <URL:http://www.stonehenge.com/merlyn/>
      Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
      See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
    • Jonathan Mangin
      ... From: Randal L. Schwartz To: Jonathan Mangin Cc: Sent: Monday,
      Message 2 of 7 , Dec 5, 2005
      • 0 Attachment
        ----- Original Message -----
        From: "Randal L. Schwartz" <merlyn@...>
        To: "Jonathan Mangin" <jon.mangin@...>
        Cc: <perl-beginner@yahoogroups.com>
        Sent: Monday, December 05, 2005 10:45 AM
        Subject: Re: [PBML] Do I need an inner loop or ?


        >>>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:
        >
        > Jonathan> I hope this is mostly a perlish question.
        > Jonathan> I have tables that look something like this:
        >
        > Jonathan> +----+------------+----------+------+------+------+
        > Jonathan> | id | date | time | type | val1 | val2 |
        > Jonathan> +----+------------+----------+------+------+------+
        > Jonathan> | 1 | 2005-08-01 | 04:02:00 | O | | 95 |
        > Jonathan> | 2 | 2005-08-01 | 06:08:00 | S | .006 | 92 |
        > Jonathan> | 3 | 2005-08-01 | 07:30:00 | S | .007 | 124 |
        > Jonathan> | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
        > Jonathan> | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
        > Jonathan> | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
        > Jonathan> | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
        > Jonathan> | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
        > Jonathan> | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |
        > Jonathan> | 10 | 2005-08-02 | 08:00:00 | O | | 128 |
        > Jonathan> | 11 | 2005-08-02 | 15:08:00 | S | .005 | 70 |
        > Jonathan> | 12 | 2005-08-02 | 18:38:00 | S | .008 | 143 |
        > Jonathan> | 13 | 2005-08-02 | 21:45:00 | S | .007 | 113 |
        >
        >
        > Jonathan> I'm trying to put data from a single date into %val.
        > Jonathan> This code puts all dates into %val. It's about as close
        > Jonathan> as I've come.
        >
        > First, why are date and time two separate columns?
        >
        > Second, to do a sum, do it in the database:
        >
        > SELECT date, sum(val1), sum(val2)
        > FROM your_table
        > GROUP BY 1;
        >

        I don't know where you got the idea I'm trying to sum anything.
        Did you look at my question?
      • merlyn@stonehenge.com
        ... Jonathan I don t know where you got the idea I m trying to sum anything. Jonathan Did you look at my question? Sorry, I ignored your code, because it had
        Message 3 of 7 , Dec 5, 2005
        • 0 Attachment
          >>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:

          Jonathan> I don't know where you got the idea I'm trying to sum anything.
          Jonathan> Did you look at my question?

          Sorry, I ignored your code, because it had an SQL call but didn't show
          the prepare, and referred to $row[6] when you had only columns 0-5 in
          your sample table. If you don't show the actual code you're using,
          it's hard to make comments on it.

          So instead, I looked at your text, which talked about getting stuff
          for a given date into one place, and presumed you were trying to sum
          something.

          Perhaps you could show *actual* code, then ask a question that makes
          sense related to that code.

          --
          Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
          <merlyn@...> <URL:http://www.stonehenge.com/merlyn/>
          Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
          See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
        • Charles K. Clarkson
          ... What should the data in %val look like? I assume you have confused the array starting index as 0 and as 1 at various points of your code. I also assume
          Message 4 of 7 , Dec 5, 2005
          • 0 Attachment
            Jonathan Mangin <> wrote:

            : I'm trying to put data from a single date into %val.
            : This code puts all dates into %val. It's about as close as I've
            : come.

            What should the data in %val look like? I assume you have
            confused the array starting index as 0 and as 1 at various points
            of your code. I also assume that the query did not return the id
            field. And finally I assume you want a hash of array of arrays
            keyed by date.

            For my example, I will assume fetchrow_array() returns values
            like this. (The time column should be able to be adjusted by the
            db to return the HH:MM format and avoid the substr() call in
            perl.)

            +------------+----------+------+------+------+
            | date | time | type | val1 | val2 |
            +------------+----------+------+------+------+
            | 2005-08-01 | 04:02:00 | O | | 95 |
            | 2005-08-01 | 06:08:00 | S | .006 | 92 |

            my %data_by_date;
            while ( my( $date, $time, @record ) = $sth->fetchrow_array() ) {
            push @{ $data_by_date{ $date } }, [ substr($time, 0, 5), @record ];
            }

            my $date1 = Date::Simple->new($bdate);
            $data_by_date{ $date1 } will now return an array of arrays.


            : my $date1 = Date::Simple->new($bdate);
            : # my $date2 = Date::Simple->new($edate);
            :
            : my %vals = ();
            : my @temp = ();
            : my @data = ();

            Yuck!

            my( %vals, @temp, @data );


            : while (my @row = $sth->fetchrow_array) {
            : if ($row[0] eq $date1) {

            As described in your table, row[0] is not the date, row[1] is.


            : $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];

            As described in your table, row[6] does not exist.


            : @temp = ($row[0], \%vals);

            This resets @temp each time through. All previous values will
            be lost.


            : next;
            : }

            YMMV, but I prefer "else" to "next" in this situation. I think
            it gives a better hint to what is happening.


            if ( $row[0] eq $date1 ) {
            $vals{ $row[6] } = [ substr($row[1],0,5), $row[3], $row[4], $row[5]
            ];
            @temp = ( $row[0], \%vals );

            } else {
            push @data, [@temp];
            $date1 = $date1->next;
            }


            HTH,

            Charles K. Clarkson
            --
            Mobile Homes Specialist
            254 968-8328
          • Jonathan Mangin
            It s true enough, I have a hard time asking questions properly owing to wide and deep ignorance. I include a snippet of table only to show normalized
            Message 5 of 7 , Dec 5, 2005
            • 0 Attachment
              It's true enough, I have a hard time asking questions
              properly owing to wide and deep ignorance. I include
              a snippet of table only to show "normalized" nature.
              Many more columns from three similar tables are involved.
              Poor example data:

              | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
              | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
              | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
              | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
              | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
              | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |

              my $date1 = Date::Simple->new($bdate);
              # my $date2 = Date::Simple->new($edate);

              # Been moved many times. You'd be yucky too. (Helps prove reality :)
              my %vals = ();
              my @temp = ();
              my @data = ();
              while (my @row = $sth->fetchrow_array) {

              # Compare date
              if ($row[0] eq $date1) {

              # Phantom $row[6] is time column in HHMM format
              $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];

              # If true...
              # $vals{1500} = [15:00, S, .005, 72]

              next;

              # If still true...
              # $vals{1832} = [18:32, S, .008, 144]
              # $vals{2137} = [21:37, S, .007, 114]
              }


              # Moved for clarity(?)
              @temp = ($row[0], \%vals);
              # @temp = ('2005-08-01', \%vals);
              push @data, [@temp];

              # (%vals, @temp) = ();
              $date1 = $date1->next;
              }

              Now go back and do it all again for 2005-08-02 and subsequent days
              resulting in @data = [@temp, @temp, @temp...]

              Don't understand much of the following though you seem know
              approximately what I need. Does using the time as key change
              appreciably what you've done?

              > my %data_by_date;
              > while ( my( $date, $time, @record ) = $sth->fetchrow_array() ) {
              > push @{ $data_by_date{ $date } }, [ substr($time, 0, 5), @record ];
              > }
              >
              > my $date1 = Date::Simple->new($bdate);
              > $data_by_date{ $date1 } will now return an array of arrays.
              >
              > : @temp = ($row[0], \%vals);
              >
              > This resets @temp each time through. All previous values will
              > be lost.
              >
              >
              > : next;
              > : }
              >
              > YMMV, but I prefer "else" to "next" in this situation. I think
              > it gives a better hint to what is happening.
              >
              >
              > if ( $row[0] eq $date1 ) {
              > $vals{ $row[6] } = [ substr($row[1],0,5), $row[3], $row[4], $row[5]
              > ];
              > @temp = ( $row[0], \%vals );
              >
              > } else {
              > push @data, [@temp];
              > $date1 = $date1->next;
              > }
              >
              >
              > HTH,
              >
              > Charles K. Clarkson
              > --
              > Mobile Homes Specialist
              > 254 968-8328
              >
              Charles,
              I wonder if you can put it all together in one piece for me
              to study? Thanks for the time you've given me already.

              --Jon
            • Charles K. Clarkson
              ... You can become less ignorant by learning. Many of traits needed for asking good questions are also the traits needed to be a good programmer.
              Message 6 of 7 , Dec 5, 2005
              • 0 Attachment
                Jonathan Mangin <> wrote:

                : It's true enough, I have a hard time asking questions properly
                : owing to wide and deep ignorance.

                You can become less ignorant by learning. Many of traits
                needed for asking good questions are also the traits needed to be
                a good programmer.

                http://www.catb.org/~esr/faqs/smart-questions.html


                : I include a snippet of table only to show "normalized" nature.
                : Many more columns from three similar tables are involved.

                Then show us the columns actually returned or stick to just
                the six columns in your example code. This was one problem Randal
                had with your code. You referred to the seventh column ($row[6])
                with only 6 columns (@row[0..5]) in the example code.

                Tell us what you want in clear concise terms. Forget all the
                variables, just show us what the data structure is suppose to look
                like by using the data in a table of supplied data.


                HTH,

                Charles K. Clarkson
                --
                Mobile Homes Specialist
                254 968-8328
              Your message has been successfully submitted and would be delivered to recipients shortly.