Hello sea,
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
