Mysql函数里面的MySql函数和session / user变量

Question

I would like to create a MySql function that will return an incremental row count as long as the given id is the same and if the id changes function would reset the count starting from 1.

Below is a result I am looking for, where you can see as long as the itemId (on left column) remains the same, the Count on right column will increments, and when itemId changes the Count will restart from 1.

In my mind, the MySql function like the one below would do the incremental counting and resetting, but unfortunately it returns 1 for each row. My thought was to provide the current itemId to the function and the function would compare the sent in id to to the one saved in @n session variable from last row, and as long as the id's are the same the function would return incremented row count, else it would reset to 1. Can anybody guide me to why this function is not working? Or is there a better way to achieves the result I am looking for?

CREATE FUNCTION `nth`(id int) RETURNS tinyint(4)
BEGIN
declare ln tinyint;
if @saved_id = id then
    set @n := @n+1;
    set ln = @n;
else
    set @saved_id := id;
    set @n := 1;
    set ln = @n;
end if;
RETURN ln;
END

The Mysql version I am using is 5.7

Here is the example query I am using, the itemId is foreign key

select id, itemId, started_at 'Start', stopped_at Stop, nth(started_at) 'Count'
from events
order by itemId, stopped_at

Answer 1

You don't need to define a UDF for this. You can achieve this within a SELECT query itself. In newer versions of MySQL (8.0.2 and above), it is achievable using ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY id)

In older version, we can use the user-defined variables. In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same itemId values come together, with further sorting between them based on id.

Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the numbering ("count"). It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number ("Count") accordingly.

SELECT
  dt.id, 
  dt.Start,
  dt.Stop, 
  @rn := CASE WHEN dt.itemId = @itm THEN @rn + 1
              ELSE 1 
         END AS Count, 
  @itm := dt.itemId AS itemId 
FROM
(
  SELECT 
    id, 
    itemId, 
    started_at AS Start, 
    stopped_at AS Stop 
  FROM events
  ORDER BY itemID, id
) AS dt
CROSS JOIN (SELECT @itm := 0, @rn := 0) AS user_init_vars