Professional Web Applications Themes

Query to find MAX(SUM()) of X consecutive fields - MySQL

Hello All, I have a database containing measurement data in which data is added every 5 minutes. I would like to get the hour in which the highest measurement was done, this means that all values from one hour should be added and then compared to other hours. The hour with the highest measurement should be returned. I could do this with a PHP script using a FOR-statement, but this is too slow since the data should be generated over a complete month or even year. This means that i should do 24 query's to get these values for ONE ...

  1. #1

    Default Query to find MAX(SUM()) of X consecutive fields

    Hello All,

    I have a database containing measurement data in which data is added every 5 minutes. I would like to get the hour in which the highest measurement was done, this means that all values from one hour should be added and then compared to other hours. The hour with the highest measurement should be returned.

    I could do this with a PHP script using a FOR-statement, but this is too slow since the data should be generated over a complete month or even year. This means that i should do 24 query's to get these values for ONE day, which makes about 750 querys per month.

    does anybody have any idea how to handle this?
    I am using MySQL 4.0.23 and PHP 4.3.3 on Debian 3


    --------------= Posted using GrabIt =----------------
    ------= Binary Usenet downloading made easy =---------
    -= Get GrabIt for free from [url]http://www.shemes.com/[/url] =-

    DeKoter Guest

  2. #2

    Default Re: Query to find MAX(SUM()) of X consecutive fields

    HI, i've created a table like this
    mysql> select * from t;
    +----+-----+---------------------+
    | id | mes | created |
    +----+-----+---------------------+
    | 1 | 45 | 2005-12-01 13:30:00 |
    | 2 | 4 | 2005-12-01 13:40:00 |
    | 3 | 14 | 2006-02-01 13:40:00 |
    | 4 | 1 | 2006-02-01 14:40:00 |
    | 5 | 100 | 2006-02-01 14:30:00 |
    +----+-----+---------------------+

    If you need to fetch an hour number within a day here is the query
    mysql> select sum(mes) hour_total, hour(created) hour from t where
    date(created) = '2006-02-01' group by hour(created) order by hour_total
    desc limit 1;
    +------------+------+
    | hour_total | hour |
    +------------+------+
    | 101 | 14 |
    +------------+------+
    1 row in set (0.00 sec)

    If you have to determine an hour for every day of your statistics then
    you can use a copule of queries (subqueries can be replaced by
    temporary tables. as you know. But I'd advise you to upgrade you DB
    version so that you can use subqueries)

    I guess, this is not th best way to fetch the rows. I know that Oracle
    has ytic functions that can allow one to perform such a query in a
    single sql.

    Well, anyway, here's the query.
    First, i had to create one more temporary table

    create
    table t1 as (select sum(mes) hour_total, hour(created),
    date(created) date
    from t
    group by date(created), hour(created));

    After that I can issue a query that gives me an answer I do really want
    select hour_totals.hour, max_value.date from (
    select
    max(hour_total) hour_total,
    date
    from t1
    group by
    t1.date) max_value
    inner join
    t1 hour_totals
    on hour_totals.hour_total = max_value.hour_total and max_value.date =
    hour_totals.date





    You say that you have to identify the hour of the day when the greates
    measurement was taken. right?

    robocomp Guest

  3. #3

    Default Re: Query to find MAX(SUM()) of X consecutive fields

    DeKoter wrote:
    > Hello All,
    >
    > I have a database containing measurement data in which data is added
    > every 5 minutes. I would like to get the hour in which the highest
    > measurement was done, this means that all values from one hour should
    > be added and then compared to other hours. The hour with the highest
    > measurement should be returned.
    >
    > I could do this with a PHP script using a FOR-statement, but this is
    > too slow since the data should be generated over a complete month or
    > even year. This means that i should do 24 query's to get these values
    > for ONE day, which makes about 750 querys per month.
    >
    > does anybody have any idea how to handle this?
    > I am using MySQL 4.0.23 and PHP 4.3.3 on Debian 3
    Since you are using MySQL you can do:

    SELECT HOUR(created) AS the_hour, MAX(value) AS the_value FROM values GROUP
    BY the_hour ORDER BY the_value DESC LIMIT 1


    André Hänsel Guest

Similar Threads

  1. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  2. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  3. Performing a FIND over multiple fields?
    By Jean-François Landry in forum FileMaker
    Replies: 10
    Last Post: January 19th, 01:16 PM
  4. Find mode - compare two fields
    By Scott in forum FileMaker
    Replies: 3
    Last Post: September 18th, 02:08 PM
  5. How do I find not-unique fields
    By Joseph Galron in forum FileMaker
    Replies: 5
    Last Post: August 6th, 02:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139