mysql - 生成表示行在组中的索引的列

Question

我有一个案例，我有一张表，它描述了特定日期的测量值。但是，存在不同类型的测量(可能具有相同的日期)。该表看起来像这样:

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

(注意省略了1/2019-01-03和2/2019-01-03和2/2019-01-04!)

不同类型之间没有可比性，因此需要严格区分。问题是我必须将此表与另一个表连接起来，该表的顺序不是基于日期，而是使用基于基线日期(从计数开始的地方)的偏移/索引方法(整数)!请注意，此索引计算行数，而不是“自...以来的天数”方面的偏移量。

为了这里的这个例子，我们假设它看起来像这样(从现实生活中简化而来):

Table: type_master

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

这就是为什么我想要“每个组中的行索引计数”。所以 SQL 语句的结果集(将在 MySQL/MariaDB 上执行)应该看起来像这样:

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!)

(索引应按日期升序排列——您可以假设那些被跳过的日期也是有意跳过的)。

我已经开始研究 https://stackoverflow.com/a/5351692/6350762 中提到的想法

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

这让我的索引计数非常好 - 但它忽略了组的变化并继续计数，即使在新组开始时也是如此。

您还可以考虑以下内容:

• 如果可能，所有这些最终都应该在一个 View 中结束。
• 编写 MySQL 过程是可能的，但会导致不需要的副作用 - 如果是这样的话，那么我还可以在运行在数据库之上的应用程序服务器上实现必要的逻辑。但是，由于数据访问的原因，最好将逻辑存储在数据库中。
• 良好的性能会很好，但不是最重要的:measurements 上的记录数大约为 40k； type_master 上的记录数有几十条。
• 服务器在 MariaDB 10.1.26 上运行。 (稍后根据评论更新)

关于如何使用可接受的 SQL 语句实现此目的有什么想法吗？

Answer 1

在 MySQL 8+ 和最新版本的 MariaDB 中，您可以使用窗口函数:

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;

在旧版本中，您可以使用变量。这有点棘手；我认为逻辑是:

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