MySQL : find a sequential pattern appears on multiple rows

Question

I am trying to find rows in a table that follows a specific pattern. For example, there is a table with three columns, ID, PRODUCT_ID and ACTION respectively.

ID PRODUCT_ID ACTION

1    A001                 ABC
2    A001                 DHI
3    A001                 FGH
4    B111                 FGH
5    A001                 JKL
6    A001                 ZAB

I would like to retrieve the records having 3 actions in order, ABC-FGH-ZAB for same PRODUCT_ID. From above example, I would like to get the rows with IDs, 1, 3, and 6 as an answer and the row with ID 4 should be ignored since it has a different PRODUCT_ID. How do I formulate a query to have a result set like below on MySQL?

ID PRODUCT_ID ACTION

1    A001                 ABC
3    A001                 FGH
6    A001                 ZAB

I try not to use a nested query unless I have to for a performance reason . The order of ACTION is important, so FGH-ZAB-ABC should not be retrieved.

Thanks in advance

@a_horse_with_no_name - ABC, FGH, ZAB are predefined action sequences that I am looking for. It could be WAIT, RUN, END or something like that. I should have given better action examples for my question. I just chose 3-letter-string for the sake of simplicity. I hope this answers your question. — user1253337
– user1253337, Commented Mar 7, 2012 at 15:40

Michael Fredrickson · Accepted Answer · 2012-03-06 22:41:51Z

1

SELECT
    yt1.ID AS First,
    yt2.ID AS Second,
    yt3.ID AS Third
FROM
    YourTable yt1 
    JOIN YourTable yt2 
        ON yt2.ID > yt1.ID 
        AND yt2.Action = 'FGH' 
        AND yt2.Product_ID = y1.Product_ID
    JOIN YourTable yt3 
        ON yt3.ID > yt2.ID 
        AND yt3.Action = 'ZAB'
        AND yt3.Product_ID = y1.Product_ID
WHERE
    yt1.Action = 'ABC'

edited Mar 6, 2012 at 22:41

answered Mar 6, 2012 at 22:14

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1253337 Over a year ago

Thank you very much for your super fast answer. While you were answering my question, I edited my question to add one more column (PRODUCT_ID) in a table. How do you change your query to satisfy one more condition I added?

Michael Fredrickson Over a year ago

@user1253337 I've updated the answer to include the new column... if this was helpful, please mark this as the answer to your question.

user1253337 Over a year ago

Thank you very much for your updated answer. Your query worked great! The one thing that I am worrying is that the table I am dealing has almost 1 million records so triple join in the query might cause any performance issue.

Michael Fredrickson Over a year ago

@user1253337 As far as performance, if you have good indexes on your table, 1 million records shouldn't cause too much of an issue...

tribal · Accepted Answer · 2012-03-06 23:10:49Z

0

This will do the trick:

 select a.id from myActions a, (select ASCII(act) n, id from myActions) c 
 where char(c.n+1)=substring(a.act,2,1) and char(c.n+2)=substring(a.act,3,1)
 and a.id=c.id;

The table in example is:

 create table myActions( 
   id int(11) not null auto_increment,
   act char(3),
   primary key(id))


   insert into myActions (act) values
   ('ABC'),('DHI'),('EFG'),('LMN'),('XYV'),('XYZ'),('CBA'),('HIJ')

another option is a procedure.

table looks like this:

1   ABC
2   DHI
3   EFG
4   LMN
5   XYV
6   XYZ
7   CBA
8   HIJ

and result is: 1,3,4,6,8

UPDATE: at the end of the query add : and productId='A001' to get:

select a.id from myActions a, (select ASCII(act) n, id from myActions) c 
where char(c.n+1)=substring(a.act,2,1) and char(c.n+2)=substring(a.act,3,1)
and a.id=c.id and productId='A001';

You will need to specify the productId you seek as there may be two different productIds with matching sets.

edited Mar 6, 2012 at 23:10

answered Mar 6, 2012 at 23:00

tribal

6512 gold badges13 silver badges26 bronze badges

3 Comments

user1253337 Over a year ago

Thanks for your detailed answer. I learned something new from your query. I appreciate that. However, what I am trying to find is a sequence of strings (any arbitrary strings) in a certain order. This could be Mac-KFC-BK or something like that. It seems like your query retrieves 3-letter-string that has 3 letters in an alphabetical order. I think my question was not specific enough to deliver that I was trying to do. Once again, thanks for your answer.

tribal Over a year ago

hope someone else finds it useful. the sequence may be longer than 3 characters.

user1253337 Over a year ago

I believe that your query is very helpful to formulate a query handling strings. I am sorry that I do not have enough reputation yet to click 'answer is useful' button for your work.

Collectives™ on Stack Overflow

MySQL : find a sequential pattern appears on multiple rows

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related