Two max of two peaks.
-
Hi, I need to return the maximum values of several peaks. for example.
I want to mark, this three top.
Any ideas? Maybe from SQL side?
-
Enumerate each value with ROW_NUMBER() and do a +1 offset self join. Then look for where the "lo" value is greater than the "hi" value (the +1 rownumber). If you have 2012 or better, you might be able to do a previous row thing to make it even simpler.
See the article at the first link in my signature line below under "Helpful Links" if you'd like a working code example.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (4/14/2015)
Enumerate each value with ROW_NUMBER() and do a +1 offset self join. Then look for where the "lo" value is greater than the "hi" value (the +1 rownumber). If you have 2012 or better, you might be able to do a previous row thing to make it even simpler.See the article at the first link in my signature line below under "Helpful Links" if you'd like a working code example.
Your bigger issue is going to be how to detect the bigger trends: you could potentially have "sawtooth" data where every other point is a "peak". Picking out those three points is easy for you, but is anything but trivial for a system to do for you.
This is something baked into what is call time series analysis in statistics. It's a bit outside of my expertise, but might be worth looking into.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Agreed. That's why I asked for some real test data. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply