Results 1 to 7 of 7

Thread: SQL for frequency distribution

  1. #1
    Join Date
    Aug 2004
    Posts
    4

    SQL for frequency distribution

    I am a beginner in SQL and I need help in returning frequency distribution.

    I have a table that has row updates in random time (it could update every second or no updates for several seconds). I need a query that returns the number of updates every 15 seconds. HELP!

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    please post table definition.

    if you truely mean updates, and are updating existing rows, there is no way to query from the table.

    you need to "log" the updates somewhere that can be queried or put a trigger on the table that will capture the updates.

  3. #3
    Join Date
    Aug 2004
    Posts
    4
    Here's a sample data....


    Serial Number ID Time (in seconds)

    1 A01 2922
    2 B01 2969
    3 A02 3015
    4 A03 3015
    5 B02 3015
    6 A04 3015

    etc..


    I need a query that returns

    Period Start Time Updates

    1 2922 1
    2 2937 0
    3 2952 0
    4 2967 1
    5 2982 0
    6 2997 0
    7 3012 4
    8 3027 etc

  4. #4
    Join Date
    Mar 2003
    Posts
    468
    are your period start times constant. if so then you could create a seperate table that maintained the periods you are interested in and then you would need to corrolate back to the update rows.

    else,
    you really need an app, function, or procedure that will return the rows required.

  5. #5
    Join Date
    Aug 2004
    Posts
    4
    No. I'll need a query for different tables with different start times.

  6. #6
    Join Date
    Aug 2004
    Posts
    4
    I'm trying not to reinvent the wheel here. I am assuming that there must already be a query developed to return the number of occurrences within a specified time period. This has got to be an often-used statistical analysis that someone has already developed an SQL query for.

    ... or am I grasping at straws here?

  7. #7
    Join Date
    Aug 2004
    Posts
    3
    this isn't that fast and it works for every 10 seconds...


    SELECT
    CountOf, TimeUpdated+'0' AS TimeUpdated
    FROM (SELECT
    COUNT(*) AS CountOf, LEFT(CONVERT(varchar(8),time_updated_column), LEN(time_updated_column)-1) AS TimeUpdated
    FROM your_table
    GROUP BY LEFT(CONVERT(varchar(8),time_updated_column), LEN(time_updated_column)-1)
    ) dt


    I basically convert the number to a string, remove the last digit, and then group them, basically grouping any numbers by the "tens" digit and not the "ones" digit, I use the derived table to append the removed "ones" digit back (+'0')

    you could use this basic approach but convert your numbers to base 15 and I think that would properly group by 15 seconds

    you could always use a loop, set a variable at the min time and another to the max time, select the count of all rows above the min time and below the min time + your interval and then increment your min time by the interval and continue until your min time > max time...

Posting Permissions

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