按原始顺序进行SQL分区

Question

这是MySQL的原始表：

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

当我使用select Id, Num, row_number() over(partition by Num) from t时，MySQL会自动打乱Num列的顺序。但是，我希望保持Num列的顺序不变。具体来说，理想的输出应该是这样的：

+----+-----+-----+
| Id | Num | row |
+----+-----+-----+
| 1  |  1  |  1  |
| 2  |  1  |  2  |
| 3  |  1  |  3  |
| 4  |  2  |  1  |
| 5  |  1  |  1  |
| 6  |  2  |  1  |
| 7  |  2  |  2  |
+----+-----+-----+

如何编写这个MySQL查询？

Answer 1

这是一个缺口和孤岛问题。我建议使用行号之间的差异来识别组。

如果id总是在没有间隙的情况下递增：

select id, num, 
row_number() over(partition by num, id - rn order by id) rn
from (
select t.*, row_number() over(partition by num order by id) rn
from mytable t
) t
order by id

否则，我们可以用另一个row_number():生成我们自己的递增id

select id, num, 
row_number() over(partition by num, rn1 - rn2 order by id) rn
from (
select t.*, 
row_number() over(order by id) rn1,
row_number() over(partition by num order by id) rn2
from mytable t
) t
order by id

DB Fiddle上的演示-对于您的样本数据，两个查询都会产生：

id|num|rn-：|--：|-：1|1|12|1|23|1|34|2|15|1|16|2|17|2|2

Answer 2

您可以通过编写自己的row_number来更好地控制其分区。

set @prev_num = null;
set @row_number = 0;
select
id,
-- Reset row_number to 1 whenever num changes, else increment it.
@row_number := case
when @prev_num = num then 
@row_number + 1
else
1
end as `row_number`,
-- Emulate lag(). This must come after the row_number.
@prev_num := num as num
from foo
order by id;

Answer 3

与Schwern提出的解决方案想法相同。只是MySQL中的另一种语法风格，我觉得它非常简单易用。

Select
id
, num
, value
from
(select
T.id,
T.num,
if( @lastnum = T.num, @Value := @Value + 1,@Value := 1) as Value,
@lastnum := T.num as num2
from
mytable T,
( select @lastnum := 0,
@Value := 1 ) SQLVars
order by
T.id) T;

DB小提琴链接-https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e04692841d091ccd54ee3435a409c67a