SitePoint Sponsor

User Tag List

Results 1 to 25 of 26

Hybrid View

  1. #1
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Average of ten minute period.

    I've got a process that updates the table every ten minutes. Now I am trying to graph an average 24 hours over the period of several months. So I need the output to be like this:
    • average of all entries in each individual column between 00:00-00:10
    • average of all entries in each individual column between 00:10-00:20
    • average of all entries in each individual column between 00:02-00:30
    • average of all entries in each individual column between 00:30-00:40
    • etc, etc...

    How do I do that?


    Here's my table:

    CREATE TABLE IF NOT EXISTS `snr` (
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `10714` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10729` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10744` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10758` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10773` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10788` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10803` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10817` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10832` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10847` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10862` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10876` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10891` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10906` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10921` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10935` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10964` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `10994` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `11023` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `11053` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `11067` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `11097` float(4,2) UNSIGNED NOT NULL DEFAULT '0',
    `11126` float(4,2) UNSIGNED NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600) AS `FROM`
         , FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600+600) AS `TO`
         , AVG(`10714`) AS avg_10714
         , AVG(`10729`) AS avg_10729
         , AVG(`10744`) AS avg_10744
         , ...
      FROM snr   
    GROUP
        BY FLOOR(UNIX_TIMESTAMP(updated)/600)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  4. #4
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something wrong there. It only prints out one line. Here's the output. http://satellites-xml.org/print-signal-level.php

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    Something wrong there. It only prints out one line.
    1. please confirm that your data actually contains timestamps outside that range
    2. please show your exact query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1. please confirm that your data actually contains timestamps outside that range
    2. please show your exact query
    1) Here's a dump of the table (select * from table order by updated desc). http://satellites-xml.org/28E-SNR.php
    2) here's the exact query:

    SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600) AS `FROM`
    , FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(updated)/600)*600+600) AS `TO`
    , AVG(`10714`) AS avg_10714
    , AVG(`10729`) AS avg_10729
    , AVG(`10758`) AS avg_10758
    , AVG(`10773`) AS avg_10773
    , AVG(`10803`) AS avg_10803
    , AVG(`10817`) AS avg_10817
    , AVG(`10832`) AS avg_10832
    , AVG(`10847`) AS avg_10847
    , AVG(`10862`) AS avg_10862
    , AVG(`10876`) AS avg_10876
    , AVG(`10891`) AS avg_10891
    , AVG(`10906`) AS avg_10906
    , AVG(`10921`) AS avg_10921
    , AVG(`10935`) AS avg_10935
    , AVG(`10964`) AS avg_10964
    , AVG(`10994`) AS avg_10994
    , AVG(`11023`) AS avg_11023
    , AVG(`11053`) AS avg_11053
    , AVG(`11067`) AS avg_11067
    , AVG(`11097`) AS avg_11097
    , AVG(`11126`) AS avg_11126
    FROM snr

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    2) here's the exact query:
    dude, where's your GROUP BY clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Somehow I left it out, sorry.

    Anyway this is not what I wanted.

    Imagine I have collected data for a year. I want the average of all the data inserted into `10714` between 00:00 and 00:10. Next line I want the average of all the data inserted into `10714` between 00:10 and 00:20. Etc, etc. And I want this for all columns. So in total it would output 144 lines (6 per hour over 24 hours).

    I've updated the link above to include the GROUP BY.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    Anyway this is not what I wanted.
    but it's what you asked for

    okay, so you want to disregard date, and focus only on the time, right?

    may i ask why you want separate lines for each average? that means instead of a simple query, you are now looking for a humoungous UNION query of 21 different SELECTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
         , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
         , AVG(`10714`) AS avg_10714
         , AVG(`10729`) AS avg_10729
         , AVG(`10744`) AS avg_10744
         , ...
      FROM snr   
    GROUP
        BY FLOOR(TIME_TO_SEC(TIME(updated))/600)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's what you asked for
    Sorry if I wasn't clear, but it is what I was trying to ask for.

    Anyway thanks a lot, it works perfectly.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    Anyway thanks a lot, it works perfectly.
    you're welcome

    let's recap what you learned today...

    FROM_UNIXTIME()
    UNIX_TIMESTAMP()
    FLOOR()
    TIME()
    TIME_TO_SEC()
    SEC_TO_TIME()

    and
    GROUP BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I've got 2 queries.

    Query 1 returns my daily trend (since the current records began. Query 2 returns trend over the last 24 hours. Is it possible to combine the two and divergence from the daily trend. For example if daily trend between 00:00 and 00:10 = 13.50, and today between 00:00 and 00:10 = 13.20 the result for that period will be -0.30.

    Here are the two queries.
    Code:
    Query 1:
    SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
         , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
         , round((AVG(`10773`) + AVG(`10788`) + AVG(`10803`) + AVG(`10817`) + AVG(`10862`) + AVG(`10876`) + AVG(`10921`))/7, 2) AS `2E-QPSK`
      FROM snr
    WHERE
        updated > '2014-02-06 06:00:00'
    GROUP
        BY FLOOR(TIME_TO_SEC(TIME(updated))/600)
    
    
    Query 2:
    SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
         , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
         , round((`10773` + `10788` + `10803` + `10817` + `10862` + `10876` + `10921`)/7, 2) AS `2E-QPSK`
      FROM snr
    WHERE
        updated >= now() - INTERVAL 1 DAY 
    ORDER
        BY updated

  14. #14
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both queries give 144 rows, one for each ten minute period.

    Edit, the above is true, but probably because the new data comes in every 10 minutes. Can a GROUP BY be added if necessary for the query?

  15. #15
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So if I change query 2 to the following is it possible?

    Code:
    SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`
         , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
         , round((`10773` + `10788` + `10803` + `10817` + `10862` + `10876` + `10921`)/7, 2) AS `2E-QPSK`
      FROM snr
    WHERE
        updated >= now() - INTERVAL 1 DAY 
    GROUP
        BY FLOOR(TIME_TO_SEC(TIME(updated))/600)
    ORDER
        BY updated

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    So if I change query 2 to the following is it possible?
    not really, because you are applying GROUP BY but without using AVG

    if the rows are coming in only once every 10 minutes, why do you need to produce averages over 10 minute intervals?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not really, because you are applying GROUP BY but without using AVG

    if the rows are coming in only once every 10 minutes, why do you need to produce averages over 10 minute intervals?
    I don't know. I was following your query. There is one data entry per 10 minute interval but it happens every day. So the average was so I could get the average value over several days, and compare it to the result of the last 24 hours.

    Anyway thank you very much for your help so far.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    So the average was so I could get the average value over several days, and compare it to the result of the last 24 hours
    okay, that makes sense

    the solution will involve a join

    specifically, take your grouped averages query and make it into a subquery, then join it back to the table in the non-grouped query for the last 24 hours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    specifically, take your grouped averages query and make it into a subquery, then join it back to the table in the non-grouped query for the last 24 hours
    I haven't any idea how to do that. I guess I'll just have to do 2 queries and sort out the result with PHP.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    I haven't any idea how to do that. I guess I'll just have to do 2 queries and sort out the result with PHP.
    you give up way too easily

    hang on a few minutes, i'll do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT avgs.`FROM`
         , avgs.`TO`
         , avgs.`2E-QPSK` AS `avgs_2E-QPSK`
         , data.updated
         , data.`2E-QPSK` AS `data_2E-QPSK`
      FROM ( SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`  
                  , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
                  , round((AVG(`10773`) +                                             
                           AVG(`10788`) +                                             
                           AVG(`10803`) +                                             
                           AVG(`10817`) +                                             
                           AVG(`10862`) +                                             
                           AVG(`10876`) +                                             
                           AVG(`10921`))/7, 2) AS `2E-QPSK`                           
               FROM snr                                                               
             WHERE                                                                    
                 updated > '2014-02-06 06:00:00'                                      
             GROUP                                                                    
                 BY FLOOR(TIME_TO_SEC(TIME(updated))/600)  
           ) AS avgs                                       
    LEFT OUTER 
      JOIN ( SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(snr.updated))/600)*600) AS `FROM`
                  , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(snr.updated))/600)*600+600) AS `TO`
                  , ROUND((`10773` + 
                           `10788` + 
                           `10803` + 
                           `10817` + 
                           `10862` + 
                           `10876` + 
                           `10921`)/7, 2) AS `2E-QPSK`
               FROM snr
              WHERE                                 
                  updated >= now() - INTERVAL 1 DAY 
           ) AS data
        ON data.`FROM` = avgs.`FROM`
    ORDER
        BY data.updated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the help. Just a couple of questions... is the subquery like having a separate table as far as the outer query is concerned? And why does the result com out in reverse order (newest first)?

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bokehman View Post
    Thanks for all the help. Just a couple of questions... is the subquery like having a separate table as far as the outer query is concerned?
    exactly!

    Quote Originally Posted by bokehman View Post
    And why does the result com out in reverse order (newest first)?
    because i forgot the DESC in the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •