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