I have some stat based data in a table ... I would like to query for ranges in an attempt to do some correlation.
EX:
Table measurements
pkMeasurmentId int
Subject string
Time DateTime
Value double
All values are between 0 and 1
my data has dips and troughs ... I would like to narrow these dips and troughs and get there size ...
ex:
....
....
.... trend coming down
1:00 .23
1:02 .27
1:03 .25
1:05 .29
1:07 .28
1:08 .31
1:09 .35
1:11 .41
1:12 .42
1:13 .45
1:15 .48
1:17 .45
1:19 .44
1:20 .42
1:21 .43
1:23 .39
1:25 .37
what I want to query for here is my large points of change in directionality (1:00 and 1:15) ... Yes I know I could use max and min if I wanted to specify an appropriate time window but I dont want to do this I want to find multiples of these types of numbers in the set (i.e. there can be 3-4 directionality changes in the set).
I know how to do this in code on my own using curve fitting but figured there might be a way to do this in SQL? as the data is already in SQL this would be a bit easier than me reading it out and doing libear calculations on it. Suggestions for linear analysis are welcomed as well (hence put in the math section)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You could use variations on this theme:
SELECT M.*
FROM measurements AS M
WHERE M.Value>=All (
Select Value
From measurements
Where Time Between M.Time - #0:05# and M.Time + #0:05#
)
OR M.Value<=All (
Select Value
From measurements
Where Time Between M.Time - #0:05# and M.Time + #0:05#
)
This shows local maximas and minimas, highest or lowest in +- 5 minutes.
Cheers!
(°v°)
It shows all values that are higher or lower than all others in a +-5 min. range. In other words, local peaks.
\ _
\_/\ _/ \_
\/ \
------*------
The * is lowest in the +-5m range, so it's selected. I showed you the Access syntax, and just noticed you use MS-SQL. I think #0:05# would have to be something like convert(datetime,'0:05') or cast('0:05' as datetime); you'll have to check it out.
> I guess what I am looking for is a regression + correlation coefficient.
Regression is used for linear trends, and loosely also for other least-square fitting methods. You need some hypothesis on the function that your data should be following. For temperatures, you would have a complex time-based cyclic wave function; for exchange rates you don't have a function as it follows a random walk.
Correlation is also used for linear trends, almost never as a loose reference to remaining variance after best fit. So I'm not sure I follow.
(°v°)
yeh sorry it defaults that way ... gotta be careful to catch it.
per curve fitting: http://en.wikipedia.org/wi
Well, there is a misunderstanding, then. Sorry if I don't understand your question. I simply don't see how we can help you further if you don't tell us the function you are interested in.
I did provide an answer to your original question; here is my test db (Access):
http://www.ee-stuff.com/Ex
I can add the best fitting sine curve or the best fitting third degree polynomial function if you like...
(°v°)
> M.Value>=All which as you say is access.
In fact, it's quite plain SQL. See for example:
ALL (Transact-SQL)
http://msdn2.microsoft.com
Subquery Fundamentals
http://technet.microsoft.c
As for polynomial best fits, see these links:
Least Squares Fitting--Polynomial [for the theory]
http://mathworld.wolfram.c
Least Squares & Data Fitting [source code in java]
http://www.mste.uiuc.edu/e
(°v°)
Business Accounts
Answer for Membership
by: aburrPosted on 2007-06-27 at 07:01:12ID: 19372303
I am a bit unsure as to your example. You mention four data elements but your example shows only two or three. I am assuming that your example is two of your four columns.
It would appear that you have one maximum at 1:15. From your data it would be hard to say that 1:00 is a minimum but the suggestion below could be modified to identify it.
Extract your data and find the difference between each adjacent point. When that difference changes sign you have your max or min. (When the sign goes from + to you have a max, when from to + a min.)