How to analyze item's path through system

Question

I'm looking for a little bit of direction for how to analyze a problem. I work for a small manufacturing company. We paint about 150 items per day. Those items then go to Quality Control. About 70% pass QC. The remaining 30% have to be repaired in some way.

We have 5 different repair categories:Repaint, Reclear, Remake, Reglaze, Fix

Every time an order gets QC'd my system inputs some data in a "Repairs" mysql table. If it passes QC, it's given a category of Great. It's structure is like this:

id | Repair | Date
 5 | repaint| 2013-01-01
 6 | reclear| 2013-01-01
 5 | great  | 2013-01-02  ...etc

I need to be able to perform analysis on what actions are happening. I'd like to know what 'paths' items are going down. For example. What percentage of items have these categories Reclear->Repaint->Great. What percentage have Repaint->Repaint->Remake->Great (every item should eventually end with 'Great)

I'm kind of stuck on where to start in figuring out how to analyze this.

Should I be keeping track of the repair number in the table? If I did that then maybe I could use a self join to select orders where repairnum=1 AND repair=Repaint joined with repairnum=2 AND repair='Great' This would tell me which orders went down the path Repaint->Great I'm a little hesitant to go this route because 1) I don't want to have to do a query and get the repairnumber before I insert a new row into the table and 2) It seems like I'd have to have some pretty nasty querys to analyze items that have 5 or 6 (or more) repairs.

Perhaps someone can point me in the right direction?

My app is in php and mysql.

My first thought was that you don't need a separate "repair number", because you have the date when each repair was made, but since MySQL doesn't support Custom Aggregates, I'm not sure how to group the results so that you have a single row with a "path" for each product. — IMSoP, Mar 28 '13 at 19:50
Aha! There is an aggregate that will help though: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field I will expand into an answer. — IMSoP, Mar 28 '13 at 19:55
You essentially have a quality control result for every item, right? 70% pass on the first test; all the rest get "repaired" until they pass? — Mike Sherrill 'Cat Recall', Mar 28 '13 at 20:21

score 0 · Answer 1 · answered Mar 28 '13 at 20:03

You don't need a separate "repair number", because you have the date when each repair was made, so can order by that (assuming you store time as well if more than one repair can be made in a day).

The "path" for an item is the list of its repairs, in order of date. If you just say SELECT repair FROM repairs WHERE id=5 ORDER BY date ASC you'll get them as rows.

The trick is to turn these into a single value representing the whole path, using GROUP_CONCAT - SELECT GROUP_CONCAT(repair ORDER BY date ASC SEPARATOR '->') FROM repairs WHERE id=5

Once you have that, you can run that for all products in the DB using a GROUP BY, and then look for patterns in it with HAVING:

SELECT 
    id,
    GROUP_CONCAT(repair ORDER BY date ASC SEPARATOR '->') as path
FROM
    repairs
GROUP BY
    id
HAVING
    path = 'Repaint->Repaint->Remake->Great'

Note that I don't have a copy of MySQL to try this out with, so I may have made a mistake, but the manual suggests that the above should work.

How to analyze item's path through system

1 Answers1