center1010
asked on
T-sql to find "peaks" and "mins" in set of numbers
I need 1 or 2 T-sql Queries , to find peaks (in red) , and the second the mins (in blue) in a set of numbers like in the picture
the results are:
peaks 7 ,10 , 7
mins 4 , 6
the results are:
peaks 7 ,10 , 7
mins 4 , 6
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank you. Please indicate you use version 2012 in your questions - it helps us choose the most effective solutions. Cheers, Paul
The solution is fine, it just will give you many peaks just being a little bump in an otherwise rising or sinking trend. eg in a sequence 0,100,99,300,400 the 100 will be a max and 99 a min peak, though thei're not very distinctive peaks.
There's lot's of mathematical analysis software for stock exchange going in that direction.
You should perhaps calculate the diffs to get slopes and then find distinct peaks where slopes change their sign clearly and don't just slinger about zero.
You'd do such analysis with arrays, collections in memory much faster then via SQL, SQL is not the best language to use for mathematical analysis of mass data, though the source might be a database, that's not a problem of course.
Bye, Olaf.
@PortletPaul I agree knowing the SQL Server version will help in many cases. It's a pitty EE stopped introducing newer sections and 2008 is still the last section. On the other side it would scatter the scatter the questions too much, there should be sections and version specifications, that would be nicer, not only for MSSQL.
There's lot's of mathematical analysis software for stock exchange going in that direction.
You should perhaps calculate the diffs to get slopes and then find distinct peaks where slopes change their sign clearly and don't just slinger about zero.
You'd do such analysis with arrays, collections in memory much faster then via SQL, SQL is not the best language to use for mathematical analysis of mass data, though the source might be a database, that's not a problem of course.
Bye, Olaf.
@PortletPaul I agree knowing the SQL Server version will help in many cases. It's a pitty EE stopped introducing newer sections and 2008 is still the last section. On the other side it would scatter the scatter the questions too much, there should be sections and version specifications, that would be nicer, not only for MSSQL.
ASKER
Thank you all, i am useing sql 2012 and 2014 but EE have only SQL 2005-2008 versions on the ASK sections
Yes, why I cannot say, but do please use a tag or state the version in the question. It will save time.
specifically it will be useful to have the LEAD/LAG functions (available from sql 2012)