Предположим, что нам необходимо свернуть все движения по выбывшей из оборота номенклатуре. Мы выполнили удаление движений по регистрам накопления и обнаружили, что нумерация записей наборов нарушилась. Это может выглядеть примерно вот так (значения в ячейках имеют демонстрационный характер для упрощения восприятия):
| _Period | _RecorderTRef | _RecorderRRef | _LineNo | _Fld123RRef |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 1 | 0x11 |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 2 | 0x22 |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 4 | 0x44 |
где
_Period - период регистра накопления;
_RecorderTRef - код типа документа, например, Документ.ПоступлениеТоваровУслуг;
_RecorderRRef - ссылка на документ (имеет тип binary(16), по факту является GUID'ом);
_LineNo - порядковый номер записи набора регистра;
_Fld123RRef - измерение, ссылка на элемент справочника "Номенклатура" (в нашем примере так).
В данной таблице не хватает записи с номером строки _LineNo равным 3. Эта строка была удалена в процессе свёртки. Если мы оставим это так как есть, то, например, при выполнении такого кода 1С (проверялось на платформе 8.3):
Набор = РегистрыНакопления.ТоварыНаСкладах.СоздатьНаборЗаписей();
Набор.Отбор.Регистратор.Установить(ДокументСсылка);
Набор.Прочитать();
Набор.Записать();
Можно неожиданно получить вот такой результат:
| _Period | _RecorderTRef | _RecorderRRef | _LineNo | _Fld123RRef |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 1 | 0x11 |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 2 | 0x22 |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 3 | 0x44 |
| 01.01.2010 12:00:00 | 0x12 | 0x01 | 4 | 0x44 |
Последняя строка в наборе регистра задублировалась. Как это произошло? Моя гипотеза такова: набор записей был успешно прочитан в оперативную память, а затем, при его записи обратно в базу данных, 1С восстановила последовательность нумерации и та строка, которая имела значение поля _LineNo равным 4 стала равна 3. В таком виде набор записывается в базу данных, но строка со значением _LineNo равным 4 из базы данных не была удалена и к ней дописываются строки со значениями _LineNo равными 1, 2 и 3, которые они получили в оперативной памяти. Таким образом получается дублирование.
В таких случаях требуется восстановить последовательность нумерации строк. Как это сделать наиболее простым способом? Это можно сделать при помощи следующей команды SQL:
SET
T.[_LineNo] = T.[RowNumber] -- Обновляем значения
FROM
(SELECT
T.[_LineNo] AS [_LineNo],
-- Нумерация по порядку средствами SQL - используем оконные функции
ROW_NUMBER() OVER(PARTITION BY T.[_RecorderRRef] ORDER BY T.[_LineNo]) AS [RowNumber]
FROM
[ИмяТаблицыРегистра] AS T
INNER JOIN
(SELECT
[_RecorderRRef] AS [_RecorderRRef],
COUNT([_LineNo]) AS [LineCount], -- Количество строк в разрезе регистратора
MAX([_LineNo]) AS [LineMax] -- Максимальное значение номера строки в разрезе регистратора
FROM
[ИмяТаблицыРегистра]
WHERE
[_Period] < @Period -- Здесь может быть любое нужное нам условие отбора записей набора регистра
GROUP BY
[_RecorderRRef]
HAVING
COUNT([_LineNo]) <> MAX([_LineNo]) -- Таким образом мы отбираем регистраторы, у которых нарушилась нумерация
) AS U
ON T.[_RecorderRRef] = U.[_RecorderRRef]) AS T; -- Фильтруем записи регистра по найденным регистраторам
Вместо [ИмяТаблицыРегистра] нужно подставить нужное имя таблицы регистра накопления. А вместо [_Period] < @Period можно использовать любое другое условие отбора нужных нам записей регистра накопления. Обращаю внимание на выражение COUNT([_LineNo]) <> MAX([_LineNo]), которое позволяет определить какие наборы записей были фрагментированы с точки зрения нумерации строк набора по порядку.
Краткое описание алгоритма:
1. Находим те регистраторы, у которых количество строк в наборе не равно максимальному значению номера строки этого набора (обнаруживаем фрагментацию).
2. Отбираем все записи регистра по полученным регистраторам, используя INNER JOIN.
3. При помощи оконной функции SQL вычисляем правильные порядковые номера строк в разрезе регистраторов.
4. Обновляем отобранные записи правильными значениями порядковых номеров строк.
Этот алгоритм аналогичным способом можно использовать для восстановления нумерации табличных частей.
Обновление от 19.07.2016
Прочитал книгу "Microsoft SQL Server 2012. Высокопроизводительный код T-SQL. Оконные функции." Ицик Бен Ган. Познакомился с алгоритмом поиска островков. Данная задача как раз из этого класса. Переписал код. В результате получился ещё более быстрый и, в тоже самое время, лаконичный код. Производительность увеличена примерно в 4 раза по сравнению с первым вариантом. Секрет успеха: избавление от внутреннего соединения и сортировки данных после него. Как следствие сократилось количество чтений и время использования CPU. Работа ведётся исключительно с кластерным индексом регистра накопления, в который входят поля _Period, _RecorderTRef, _RecorderRRef и _LineNo.
UPDATE T
SET
T.[_LineNo] = T.[RowNumber]
FROM
(SELECT
[_LineNo],
ROW_NUMBER() OVER (
PARTITION BY [_Period], [_RecorderTRef], [_RecorderRRef]
ORDER BY [_LineNo]) AS [RowNumber]
FROM
[ИмяТаблицыРегистра]
WHERE
[_Period] <= @Period) AS T
WHERE
T.[_LineNo] <> T.[RowNumber];
