shawn l.green Re: how to select the record with one sql statement?
Aug 18, 2018; 13:36
Re: how to select the record with one sql statement?
On 8/13/2018 7:01 PM, sea wrote: > helle, > I have a table, like this: > pigId day weigt > pig1 2018-1-1 21 > pig2 2018-1-1 31 > pig3 2018-1-1 41 > pig1 2018-1-2 22 > pig2 2018-1-2 31 > pig3 2018-1-2 40 > pig1 2018-1-3 23 pig2 2018-1-3 30 > pig3 2018-1-3 41 > ..... > > only the pig1'weight increase continuously for 3 days. Giving the input: num_of_day(weight increasing continuously for num_of_day); expecting the output: certain_day, pigId; from certain_day, pigId'weight increasing continuously for num_of_day. How to select the records in one sql statement? > thanks >
I've thought about this a bit (since your question appeared on the list) and I break down the tasks you need to perform in my head like this. (Others on the list may have different ways to approach the same problem)
task 1 - For each bucket, a pigId value, assemble an ordered list (not a set) of each weight sorted by time. (not hard)
task 2 - Within each ordered list, compare the values of every consecutive pair. (several ways to do this)
task 3 - Iterate over those "consecutive value differences" generated in task 2 looking for the longest sequence of positive non-zero values for each pigId. (this is not really a set-oriented process so normal SELECT or GROUP BY command patterns will not handle it with any efficency)
I'm afraid that attempting all of that sequencing and iteration using just a single set-based SQL command is not going to be practical. Using one or more cursors within a stored procedure is your best bet for this type of sequential trend analysis.
I could easily imagine the first step as a INSERT...SELECT...ORDER BY... command going to a new table with an autoincrement column on it (to provide a global sequence number across all of your individual pigId values) . The second step could do a self join to that table where the ON clause could look like a.pigId = b.pigID AND a.seq-1 = b.seq
But at that point, counting the length of sequences (and remembering when each trend became positive) needs a loop. That's where even complicated set-wise SQL fails you and you need to shift into using the SQL of stored programs.
Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!