Professional Web Applications Themes

Help with data over time query - MySQL

I record search terms over time on my website. Every day I record a term searched and the frequency of that term. I can run this query to find the top 5 terms searched yesterday: select term,count from query where date_id = 72 order by count desc limit 5; I would like to find the top 5 terms searched within a week. This means that I need to find the top 5 terms over seven days and then add their `count`. What is the best way to get this result? Do I need to just produce a long dataset from ...

  1. #1

    Default Help with data over time query

    I record search terms over time on my website. Every day I record a
    term searched and the frequency of that term.

    I can run this query to find the top 5 terms searched yesterday:

    select term,count from query where date_id = 72 order by count desc
    limit 5;

    I would like to find the top 5 terms searched within a week. This
    means that I need to find the top 5 terms over seven days and then add
    their `count`. What is the best way to get this result? Do I need to
    just produce a long dataset from mysql and use a scripting language to
    crunch the numbers or is there a smarter way to do this with efficient
    mysql queries?

    Thanks!

    Sandro Guest

  2. #2

    Default Re: Help with data over time query

    On Apr 2, 6:46 pm, "Sandro" <com> wrote: 

    There's a smarter way with efficient mysql queries - but what the hell
    is a date_id?

    strawberry Guest

  3. #3

    Default Re: Help with data over time query

    On Apr 2, 12:22 pm, "strawberry" <com> wrote: 




    >
    > There's a smarter way with efficient mysql queries - but what the hell
    > is a date_id?[/ref]

    date_id correlates to a table of dates. Essentially, date_id is a
    foreign key to a table of dates.

    Sandro Guest

  4. #4

    Default Re: Help with data over time query

    On Apr 2, 8:31 pm, "Sandro" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > date_id correlates to a table of dates. Essentially, date_id is a
    > foreign key to a table of dates.[/ref]


    If I was doing this I'd have a structure like this:

    search_terms(search_term_id(INT)*,search_term(VARC HAR))

    search_log(search_term_id*(INT),timestamp*(DATETIM E),ip*)

    * = PRIMARY KEY

    Then it would be straightforward to use mysql's built-in time and date
    functions (INTERVAL springs to mind) to achieve what you're after. I
    can't imagine where date_id fits into this - unless maybe you're
    trying to include days in which no activity took place - in which case
    I still think you'd just join the tables on a component of the
    datetime timestamp


    strawberry Guest

  5. #5

    Default Re: Help with data over time query

    On 2 Apr 2007 12:31:19 -0700, Sandro wrote: 
    >
    > date_id correlates to a table of dates. Essentially, date_id is a
    > foreign key to a table of dates.[/ref]

    You *really* love normalization, don't you? (:

    --
    42. When I capture the hero, I will make sure I also get his dog, monkey,
    ferret, or whatever sickeningly cute little animal capable of untying
    ropes and filching keys happens to follow him around.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Query Missing Some Data
    By g1zm0guy in forum Coldfusion Database Access
    Replies: 7
    Last Post: January 17th, 09:30 AM
  3. long query time, oracle clob data type
    By JohnAbbott in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: July 6th, 09:17 PM
  4. Replies: 2
    Last Post: August 6th, 07:08 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