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!
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.
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
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
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.
you really need an app, function, or procedure that will return the rows required.
No. I'll need a query for different tables with different start times.
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?
this isn't that fast and it works for every 10 seconds...
CountOf, TimeUpdated+'0' AS TimeUpdated
COUNT(*) AS CountOf, LEFT(CONVERT(varchar(8),time_updated_column), LEN(time_updated_column)-1) AS TimeUpdated
GROUP BY LEFT(CONVERT(varchar(8),time_updated_column), LEN(time_updated_column)-1)
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...