Professional Web Applications Themes

Find count during larger period grouped by smaller period - MySQL

Hello group, I have the following problem: The table I use for recording events is this: CREATE TABLE `Test` ( `Timestamp` timestamp NOT NULL, `Source` varchar(20) default NULL, `Dest` varchar(20) default NULL, `Event` varchar(20) default NULL, `Data` blob, PRIMARY KEY (`Timestamp`) ) I want to find how many events happened during one hour, starting every 15 minutes, thus: 00:00 - 01:00 34 events 00:15 - 01:15 43 events 00:30 - 01:30 22 events ... ... 23:00 - 00:00 26 events 23:15 - 00:15 29 events 23:30 - 00:30 36 events 23:45 - 00:45 28 events Any hints pointing me in ...

  1. #1

    Default Find count during larger period grouped by smaller period

    Hello group,

    I have the following problem:
    The table I use for recording events is this:

    CREATE TABLE `Test` (
    `Timestamp` timestamp NOT NULL,
    `Source` varchar(20) default NULL,
    `Dest` varchar(20) default NULL,
    `Event` varchar(20) default NULL,
    `Data` blob,
    PRIMARY KEY (`Timestamp`)
    )

    I want to find how many events happened during one hour, starting every 15
    minutes, thus:

    00:00 - 01:00 34 events
    00:15 - 01:15 43 events
    00:30 - 01:30 22 events
    ...
    ...
    23:00 - 00:00 26 events
    23:15 - 00:15 29 events
    23:30 - 00:30 36 events
    23:45 - 00:45 28 events

    Any hints pointing me in a direction toward any solution is highly
    appreciated

    Kind regards, Hans


    Hans Guest

  2. #2

    Default Re: Find count during larger period grouped by smaller period

    >I have the following problem:
    >The table I use for recording events is this:
    >
    >CREATE TABLE `Test` (
    > `Timestamp` timestamp NOT NULL,
    > `Source` varchar(20) default NULL,
    > `Dest` varchar(20) default NULL,
    > `Event` varchar(20) default NULL,
    > `Data` blob,
    > PRIMARY KEY (`Timestamp`)
    >)
    >
    >I want to find how many events happened during one hour, starting every 15
    >minutes, thus:
    >
    >00:00 - 01:00 34 events
    >00:15 - 01:15 43 events
    >00:30 - 01:30 22 events
    >..
    >..
    >23:00 - 00:00 26 events
    >23:15 - 00:15 29 events
    >23:30 - 00:30 36 events
    >23:45 - 00:45 28 events
    >
    >Any hints pointing me in a direction toward any solution is highly
    >appreciated
    One approach to this is to take the time, divide it by the interval
    involved, truncate it to an integer, and then GROUP BY this value.
    Then you can use count() to find the number of rows grouped.
    You might want to multiply it back so the value you're
    GROUP BYing also represents the start of the interval.


    from_unixtime(truncate(unix_timestamp(event_time)/900, 0))

    might be appropriate if event_time falls within the range representable
    by a UNIX timestamp and all daylight savings time shifts are multiples
    of 15 minutes (not true, I believe, of Saudi Arabian solar time).

    If you are using an appropriate interval, like 1 month, 1 day, 1
    hour, 10 minutes, 1 minute, etc., you can just take a date stamp
    string and lop off the portions of the string that don't count with
    left(), then GROUP BY that.

    YYYY-MM-DD HH:MM:SS
    _______________X

    left(event_time, 15) gives you 10's of minutes and up, but
    this approach won't work for 15 minutes. Depending on your version
    of MySQL, the timestamp may have a different format than a datetime,
    and the length kept may have to change accordingly.

    Gordon L. Burditt
    Gordon Burditt Guest

  3. #3

    Default Re: Find count during larger period grouped by smaller period


    "Gordon Burditt" <gordonb.omcuxburditt.org> wrote in message
    news:129j116cassq962corp.supernews.com...
    > >I have the following problem:
    >>The table I use for recording events is this:
    >>
    >>CREATE TABLE `Test` (
    >> `Timestamp` timestamp NOT NULL,
    >> `Source` varchar(20) default NULL,
    >> `Dest` varchar(20) default NULL,
    >> `Event` varchar(20) default NULL,
    >> `Data` blob,
    >> PRIMARY KEY (`Timestamp`)
    >>)
    >>
    >>I want to find how many events happened during one hour, starting every 15
    >>minutes, thus:
    >>
    >>00:00 - 01:00 34 events
    >>00:15 - 01:15 43 events
    >>00:30 - 01:30 22 events
    >>..
    >>..
    >>23:00 - 00:00 26 events
    >>23:15 - 00:15 29 events
    >>23:30 - 00:30 36 events
    >>23:45 - 00:45 28 events
    >>
    >>Any hints pointing me in a direction toward any solution is highly
    >>appreciated
    >
    > One approach to this is to take the time, divide it by the interval
    > involved, truncate it to an integer, and then GROUP BY this value.
    > Then you can use count() to find the number of rows grouped.
    > You might want to multiply it back so the value you're
    > GROUP BYing also represents the start of the interval.
    I have thought of this approach, but then I only get the count() during the
    interval, i.e
    00:00 - 00:15
    00:15 - 00:30
    etc...

    because
    00:08 / 00:15 ( <---just to simplify ) evaluates to 0,
    00:23 / 00:15 = 1,
    so the record from 00:23 is not grouped into the first line of the result,
    only the second.

    Te rephrase my question:
    Say I have the following counts
    from
    00:00 - 00:15 there are 8 events
    00:15 - 00:30 there are 2 events
    00:30 - 00:45 there are 3 events
    00:45 - 01:00 there are 6 events
    01:00 - 01:15 there are 5 events
    01:15 - 01:30 there are 7 events
    01:30 - 01:45 there are 1 events
    01:45 - 02:00 there are 4 events

    Then I want in my results:
    00:00 - 01:00 ( 8 + 2 + 3 + 6 ) = 19 events
    00:15 - 01:15 ( 2 + 3 + 6 + 5 ) = 16
    00:30 - 01:30 ( 3 + 6 + 5 + 7 ) = 21
    00:45 - 01:45 ( 6 + 5 + 7 + 1 ) = 19
    01:00 - 02:00 ( 5 + 7 + 1 + 4 ) = 17

    >
    > from_unixtime(truncate(unix_timestamp(event_time)/900, 0))
    >
    > might be appropriate if event_time falls within the range representable
    > by a UNIX timestamp and all daylight savings time shifts are multiples
    > of 15 minutes (not true, I believe, of Saudi Arabian solar time).
    >
    All times are in UTC, so that's not an issue.

    Thanks for thinking.

    Gr, Hans



    Hans Guest

  4. #4

    Default Re: Find count during larger period grouped by smaller period


    "Hans" <nieuwslezerzonnet.nl> wrote in message
    news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...
    > Hello group,
    >
    > I have the following problem:
    > The table I use for recording events is this:
    >
    > CREATE TABLE `Test` (
    > `Timestamp` timestamp NOT NULL,
    > `Source` varchar(20) default NULL,
    > `Dest` varchar(20) default NULL,
    > `Event` varchar(20) default NULL,
    > `Data` blob,
    > PRIMARY KEY (`Timestamp`)
    > )
    >
    > I want to find how many events happened during one hour, starting every 15
    > minutes, thus:
    >
    > 00:00 - 01:00 34 events
    > 00:15 - 01:15 43 events
    > 00:30 - 01:30 22 events
    > ..
    > ..
    > 23:00 - 00:00 26 events
    > 23:15 - 00:15 29 events
    > 23:30 - 00:30 36 events
    > 23:45 - 00:45 28 events
    >
    > Any hints pointing me in a direction toward any solution is highly
    > appreciated
    >
    > Kind regards, Hans
    Anyone ???


    Hans Guest

  5. #5

    Default Re: Find count during larger period grouped by smaller period

    yeah, it's a tough one!

    a script that is described as 'beyond excellence' is provided at
    [url]http://www.thescripts.com/forum/thread80220.html[/url]

    maybe it will help

    Hans wrote:
    > "Hans" <nieuwslezerzonnet.nl> wrote in message
    > news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...
    > > Hello group,
    > >
    > > I have the following problem:
    > > The table I use for recording events is this:
    > >
    > > CREATE TABLE `Test` (
    > > `Timestamp` timestamp NOT NULL,
    > > `Source` varchar(20) default NULL,
    > > `Dest` varchar(20) default NULL,
    > > `Event` varchar(20) default NULL,
    > > `Data` blob,
    > > PRIMARY KEY (`Timestamp`)
    > > )
    > >
    > > I want to find how many events happened during one hour, starting every 15
    > > minutes, thus:
    > >
    > > 00:00 - 01:00 34 events
    > > 00:15 - 01:15 43 events
    > > 00:30 - 01:30 22 events
    > > ..
    > > ..
    > > 23:00 - 00:00 26 events
    > > 23:15 - 00:15 29 events
    > > 23:30 - 00:30 36 events
    > > 23:45 - 00:45 28 events
    > >
    > > Any hints pointing me in a direction toward any solution is highly
    > > appreciated
    > >
    > > Kind regards, Hans
    >
    > Anyone ???
    strawberry Guest

  6. #6

    Default Re: Find count during larger period grouped by smaller period

    Hans wrote:
    > "Hans" <nieuwslezerzonnet.nl> wrote in message
    > news:4498ffb1$0$31644$e4fe514cnews.xs4all.nl...
    Plase only use a single line here.
    >> Hello group,
    >>
    >> I have the following problem:
    >> The table I use for recording events is this:
    >>
    >> CREATE TABLE `Test` (
    >> `Timestamp` timestamp NOT NULL,
    >> `Source` varchar(20) default NULL,
    >> `Dest` varchar(20) default NULL,
    >> `Event` varchar(20) default NULL,
    >> `Data` blob,
    >> PRIMARY KEY (`Timestamp`)
    >> )
    >>
    >> I want to find how many events happened during one hour, starting every 15
    >> minutes, thus:
    >>
    >> 00:00 - 01:00 34 events
    >> 00:15 - 01:15 43 events
    >> 00:30 - 01:30 22 events
    >> ..
    >> ..
    >> 23:00 - 00:00 26 events
    >> 23:15 - 00:15 29 events
    >> 23:30 - 00:30 36 events
    >> 23:45 - 00:45 28 events
    >>
    >> Any hints pointing me in a direction toward any solution is highly
    >> appreciated
    >>
    >> Kind regards, Hans
    >
    > Anyone ???
    In principle this is not that hard, but I'm sure nobody here will
    implement it that way.

    The first thing you need is a query, that gives you the events grouped
    by 15 minutes blocks. I name it q1 in the second part:

    SELECT COUNT(*) AS quantity,
    4*HOUR(`Timestamp`)+CEIL(MINUTE(`Timestamp`)/15) AS quarter
    FROM Test
    GROUP BY 2

    Then you make a multiple self join, fetching all quarters belonging
    together:

    SELECT quarter1.quarter,
    IFNULL(quarter1.quantity,0)+...+(IFNULL(quarter4.q uantity,0)
    FROM (q1) AS quarter1
    LEFT JOIN (q1) AS quarter2 ON MOD(quarter1.quarter+1,4*24)=quarter2.quarter
    LEFT JOIN (q1) AS quarter3 ON MOD(quarter1.quarter+2,4*24)=quarter3.quarter
    LEFT JOIN (q1) AS quarter4 ON MOD(quarter1.quarter+3,4*24)=quarter4.quarter


    BUT(!)

    - Every quarter has to contain at least one event. If not you have to
    create a dummy table that contains all 4*24=96 quarters (=96 Entries
    0-95). This quarters table is then the one used in the FROM clause.
    - The query does not include day boundaries, but that can be easily
    added via the definition of quarters in the first query and fixing the
    second argument to MOD().
    - It will return what you want, but it will not use any index and thus
    will be horrible slow. When every quarter has events, the unfiltered
    result set has over 80 Million records.
    - It is plain ugly!

    Go and do some application programming. A moving window over one query
    of the type q1 will do exactly what you want.

    HTH
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

Similar Threads

  1. Resize larger flash ad to a smaller size ad?
    By JC Creative in forum Macromedia Flash Ad Development
    Replies: 1
    Last Post: March 27th, 02:58 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