MySQL – Get Last Three Movements Per ID with LAG and GROUP BY

MySQLmysql-5.7query

I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements.

So I tried grouping by the id and used MAX(date), and used LAG to get the three last movements, but I'm getting an error from (PARTITION BY id ORDER BY id..).

If I delete the two LAG functions, then the query runs. I don't know if I'm missing something there. I'm using MySQL Workbench 8.0 C.E (edit. I'm probably using another version of MySQL 5.7)

SELECT
    id,
    MAX(lastdate),
    LAG(Move, 2, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move1,
    LAG(Move, 1, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move2,
    Move AS Move3 ,
    action_ticket
FROM table 
GROUP BY id

This is the table that I have:

id	lastdate	move	action ticket
12	25/02/20	up	scale
12	26/02/20	down	scale
12	27/02/20	left	solved
15	23/02/20	left	scale
15	22/02/20	up	scale
15	25/02/20	right	solved

And the table that I want to get is:

id	lastdate	move1	move2	move3	action ticket
12	27/02/20	up	down	left	solved
15	25/02/20	up	left	right	solved

Any help is really appreciated. Thanks a lot

Maria J.

Edit. Thank you very much for all the help !

Best Answer

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn
              FROM test )
SELECT id,
       MAX(`date`) `date`,
       MAX(CASE WHEN rn = 3 THEN move END) move1,
       MAX(CASE WHEN rn = 2 THEN move END) move2,
       MAX(CASE WHEN rn = 1 THEN move END) move3,
       MAX(CASE WHEN rn = 1 THEN action_ticket END) action_ticket
FROM cte
GROUP BY id;

fiddle

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM 
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;

MySQL InnoDB – Optimize Slow Query with 2 Million Rows

Your query formatted a bit:

SELECT DATE_FORMAT(dateCreated, '%Y-%m-%d') AS date,
       eventPriority,
       count(*) AS total
FROM `events`
WHERE customerName="Customer A"
  AND processed=1
  AND ticketStatus="CREATE"
  AND dateCreated >= "2015-07-01"
  AND dateCreated <= "2015-10-01"
GROUP BY date, eventPriority
ORDER BY `date` ASC;

The explain shows that the trends index is used and it is defined as

KEY `trends` (
    `customerName`(50),
    `processed`,
    `ticketStatus`,
    `dateCreated`,
    `eventName`(128),
    `eventPriority`
);

As we can see, the index contains all the columns of your query such a way that we can quickly get all events of "Customer A" which are "processed" and have status "CREATED" and then we can just read the right range for dateCreated. That seems really good, but as we can see from the EXPLAIN, there is one additional step after those - for all the index items found to satisfy the conditions it takes the ID (PRIMARY KEY is a part of every secondary index in InnoDB) and locates the row in the "main" part of the table. The values of those IDs are quite randomly distributed (even if we expect that autoincrement somehow coincides with the dateCreated, it is still probabůy not any continuous set, because events of many customers are inside that date range) so that means the query is doing ~140k random lookups by primary key to locate all the rows and build the temp table used for grouping.

The index contains all the columns used in your query (even the eventPriority) so it looks like it might be covering and this last step not necessary. But because the `customerName`(50) part, MySQL is afraid that it will not be able to exactly check the customerName value, because if there were a row with text longer than 50 characters, the index would contain only a part of it. We can see that it is not needed for the "Customer A" as it is quite short - but this optimization is not yet implemented so MySQL just has to get the full row.

When you remove that limit from the index, MySQL can see that it has all the data to resolve your query just by reading the index items (as even eventPriority is included - and eventName but that can be skipped). So you save tens of thousands of "random" lookups because the index contains what you need and it is sorted such a way that it is accessible fast.

===

There might be one more possibility for making it even faster. Currently the GROUP BY date means grouping by result of a function, that has to be computed for every matching row so it is not possible to use index for that. You might create another column in the table for storing the result of DATE_FORMAT(dateCreated, '%Y-%m-%d') - it might be managed by a trigger, by your application or using a new MySQL feature - generated columns. Then you would just add that to your index (probably the best place would be just before dateCreated).

But it is probably not needed unless some customer generates really many events for some date range or if you needed to group very long ranges.

Best Answer

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

MySQL InnoDB – Optimize Slow Query with 2 Million Rows

Related Question