Блог

Предположим, у меня есть таблица A, подобная этой:

  ---- --------- -- 
| Id |  Email  |  |
 ---- --------- -- 
|  1 | a@b.com |  |
|  2 | c@d.com |  |
|  3 | a@b.com |  |
|  4 | a@b.com |  |
|  5 | a@b.com |  |
 ---- --------- -- 
 

и мой sql-запрос:

 select Email
     , case when @record=Email 
            then @cnt:=@cnt 1 
            when @record:=Email 
            then @cnt:=1 
            end as n 
  from A
     , (select @cnt:=0, @record:='') r 
 order 
    by Id;
 

Цель этого запроса — найти последовательный номер с тем же порядком электронной почты по идентификатору.

он вернет

  --------- ------ 
| Email   | n    |
 --------- ------ 
| a@b.com | NULL |
| c@d.com | NULL |
| a@b.com | NULL |
| a@b.com |    1 |
| a@b.com |    2 |
 --------- ------ 
 

Почему столбец n имеет нулевой результат, это не то, что я ожидал.

Но если я изменю значение поля электронной почты в таблице A:

  ------ ---------- 
| Id   | Email    |
 ------ ---------- 
|    1 | 1a@b.com |
|    2 | 2c@d.com |
|    3 | 1a@b.com |
|    4 | 1a@b.com |
|    5 | 1a@b.com |
 ------ ---------- 
 

он возвращает:

  ---------- ------ 
| Email    | n    |
 ---------- ------ 
| 1a@b.com |    1 |
| 2c@d.com |    1 |
| 1a@b.com |    1 |
| 1a@b.com |    2 |
| 1a@b.com |    3 |
 ---------- ------ 
 

Я думаю, что это правильный результат, но почему?

Я немного смущен, кто-нибудь может мне помочь?

Если @record=Email значение равно false, тогда исследуется следующее альтернативное условие, @record:=Email выполняется присвоение, а присвоенное значение является значением выражения.

Это выражение используется как условие, т. Е. Оно имеет числовой контекст. Выполняется неявное преобразование типа данных.

Строка, начинающаяся с буквы, дает нулевое значение во время этого преобразования. Который обрабатывается как false .

РЕГИСТР не имеет другого значения — поэтому вывод равен NULL.

Это проблема пробелов и островов, и вот один из подходов, использующих метод разницы в номерах строк:

 WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) rn1,
              ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) rn2
    FROM yourTable
)

SELECT Id, Email, COUNT(*) OVER (PARTITION BY Email, rn1-rn2 ORDER BY Id) AS cnt
FROM cte
ORDER BY Id;
 

ДЕМОНСТРАЦИЯ

Общий подход здесь заключается в создании псевдогруппы для каждого «острова» похожих электронных писем (на основе порядка, заданного Id столбцом), используя разницу в номере строки, определенную двумя различными способами, в сочетании с самим электронным письмом. Затем мы используем COUNT в качестве аналитической функции для генерации последовательностей подсчета для каждой эффективной группы электронной почты.