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

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