Styjun

I have a case where I have a table, which describes measurements at a certain date. However, there are different types of measurements (which may have the same date). The table looks something like this:

Table: measurements

type_id date value
--------------------------------
1 2018-12-31 40
1 2019-01-01 42
1 2019-01-02 43
1 2019-01-04 44
2 2019-01-01 80
2 2019-01-02 79
2 2019-01-05 78

(Note that 1/2019-01-03 and 2/2019-01-03 and 2/2019-01-04 are omitted!)

The different types are not comparable with each other and thus need to be strictly separate from each other.
The problem is that I have to join this table against another table, whose order is not based on dates, but uses an offset/index approach (integer) based on a baseline date (from where counting starts)! Note that this index counts the lines and is not an offset in terms of "days since ...".

For the sake of this example here, let's assume it looks like this (simplified from real life):

Table: type_master

type_id base_date
--------------------------
1 2019-01-01
2 2019-01-01
3 2018-12-22
...

That is why I would like to have the "lines index count within in each group". So the resultset of an SQL statement (which will be executed on a MySQL/MariaDB) should look something similar like this:

type_id date value index_in_group
-------------------------------------------
1 2018-12-31 40 null (and not -1!)
1 2019-01-01 42 1
1 2019-01-02 43 2
1 2019-01-04 44 3 (and not 4!)
2 2019-01-01 80 1
2 2019-01-02 79 2
2 2019-01-05 78 3 (and not 5!)

(indexing shall happen using the ascending order by date - you may assume that those dates which are skipped are also skipped intentionally).

I already started playing around with the idea mentioned in https://stackoverflow.com/a/5351692/6350762

select @n := @n + 1 index, m.*
from (select @n:=0) initvars, measurements m

which gives me the index counting quite well - but it ignores the change of group and keeps on counting, even when the new group started.

You may also consider the following:

• If possible, all this should eventually end up in a view.


• Writing a MySQL procedure would be possible, but would result in side-effects which are unwanted - if that was the case, then I could also implement the necessary logic on the application server which is running on top of the database. However, having the logic in the database would be prefered due to reasons of data access.


• Good performance would be nice, but is not of paramount importance: the number of records on measurements is in the order of 40k; the number of records on type_master is at some dozens.


• The server is running on MariaDB 10.1.26. (Updated later on based on a comment)

Any ideas how I can achieve this with an acceptable SQL statement?

In MySQL 8+ and in recent versions of MariaDB, you can do use window functions:

select m.*,
 (case when m.date >= tm.base_date 
 then row_number() over (partition by type_id,
 m.date >= tm.base_date
 order by m.date
 )
 end) as index_in_group
from measurements m left join
 type_master tm
 on m.type_id = tm.type_id;

In older versions, you can use variables. This is a little tricky; I think the logic is:

select m.*,
 (@rn := if(m.date < tm.base_date, NULL,
 if(@t = m.type_id, @rn + 1,
 if(@t := m.type_id, 1, 1)
 )
 )
 ) as index_in_group
from (select m.*, tm.base_date
 from measurements m left join
 type_master tm
 on m.type_id = tm.type_id
 order by m.type_id, m.date
 ) m cross join
 (select @t := -1, @rn := 0) params