I want to to find a way to get my appropriate result in 1 mysql query

user_8779111 0
Viewed 109

I have a table name order_history where I store both old_status and new_status of company orders. the schema of table :

CREATE TABLE order_history (
  id int(11) NOT NULL AUTO_INCREMENT,
  old_status longtext COLLATE utf8_unicode_ci,
  new_status longtext COLLATE utf8_unicode_ci,
  created_at datetime NOT NULL,
  order_id int(11) DEFAULT NULL,
  PRIMARY KEY (id)
 }

The insert to populate is :

INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (1, '56', '714', '2020-12-20 21:37:54', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (2, '714', '61', '2020-12-20 21:37:56', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (3, '61', '713', '2020-12-20 21:38:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (4, '713', '42', '2020-12-20 21:38:26', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (5, '42', '51', '2020-12-20 21:59:17', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (6, '56', '714', '2020-12-20 22:21:27', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (7, '714', '61', '2020-12-20 22:21:29', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (8, '61', '713', '2020-12-20 22:24:28', 94471496);
INSERT INTO order_history (id, old_status, new_status, created_at, order_id) VALUES (9, '713', '42', '2020-12-20 22:24:43', 94471496);

And Now the question I want to find the TIMEDIFF of created_ats between rows that new_status=61 and rows that new_status=42 and old_status=713.

So in the example the affected rows should be (2,4,7,9) , and the right answer will be the TIMEDIFF between rows with ids (2,4) and rows with ids (7,9). But my query returns 3 results instead of 2 and it also calculate the TIMEDIFF between rows (2,9). How can I exclude this result? Here is my query:

select *
from (select oschStart.order_id as order_id, TIMEDIFF(oschEnd.created_at, oschStart.created_at) as confirm_time
      from (select osch1.order_id, osch1.created_at
            from order_history osch1
            where osch1.old_status = 713
              and osch1.new_status = 42
           ) oschEnd
               join (select osch1.order_id, osch1.created_at
                     from order_history osch1
                     where osch1.new_status = 61
      ) oschStart
                    on oschStart.order_id = oschEnd.order_id and oschEnd.created_at > oschStart.created_at) order_time;
aggregate-functions mysql
2 Answers

A simpler approach is to use a correlated sub query

select *,
        timediff(
        (select created_at from order_history oh1 
            where oh1.order_id = oh.order_id and
                  oh1.id > oh.id and
                  oh1.old_status = '713' and oh1.new_status = '42'
         order by oh1.id asc limit 1),oh.created_at) diff
from order_history oh
where  new_status = 61;
user_61524000

Why you have the unwanted results?

oschStart will result rows[2,7] and oschEnd will result rows [4,9]. Joining these subqueries will result in 4 rows [(2,4),(2,9),(7,4),(7,9)]. Your condition (on oschStart.order_id = oschEnd.order_id and oschEnd.created_at > oschStart.created_at) will result in these three rows: [(2,4),(2,9),(7,9)]. It wont prune (2,9) because also 9[created_date] > 2[created_date]. So your query will match a oschStart with all oschEnds that occurs after it. But You need it to be matched with the first occurring oschEnd

Solution

Use group by. If you group by your query results on a field and put other fields on your select part, Mysql will fill those fields with first row of that "group". So assuming that order_history is sorted on created_date you may use this query:

select order_time.id , order_time.*
from (
    select oschStart.id as id, oschStart.order_id as order_id, 
    TIMEDIFF(oschEnd.created_at, oschStart.created_at) as confirm_time
      from (select osch1.order_id, osch1.created_at
            from order_history osch1
            where osch1.old_status = 713
              and osch1.new_status = 42
           ) oschEnd
               join (select osch1.id as id, osch1.order_id, osch1.created_at
                     from order_history osch1
                     where osch1.new_status = 61
      ) oschStart
                    
on oschStart.order_id = oschEnd.order_id 
and oschEnd.created_at > oschStart.created_at)
 order_time
 group by order_time.id;
user_3747520
Related