Web lists-archives.com

Re: how to select the record with one sql statement?

Hello sea,

On 8/13/2018 7:01 PM, sea wrote:
    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?

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.

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