Вопрос:
У меня есть таблица с транзакционными записями, которые являются дубликатами. Это неплохие дубликаты, но им нужно присваивать значения, чтобы сделать их уникальными для таблицы. Использование записей – это смотреть на них со временем, я использую ROW_NUMBER, чтобы разделить их по ID и ORDER BY Time.
Иногда это работает. Иногда это терпит неудачу.
пример
CASE WHEN [Record] = ‘Start’ THEN 0 ELSE -1 + ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [Date] DESC END AS [RowNo]
Ожидаемые результаты
ID, Date, RowNo 2002, 12:30, 1 2002, 12:29, 2 2002, 12:29, 3 2002, 12:29, 4 2002, 12:28, 5 3212, 01:10, 1 3212, 01:09, 2 3212, 01:08, 3
Фактические результаты
ID, Date, RowNo 2002, 12:30, 1 2002, 12:29, 2 <—Right 2002, 12:29, 2 <—Wrong 2002, 12:29, 3 <—Right 2002, 12:28, 4 3212, 01:10, 1 3212, 01:09, 2 3212, 01:08, 3
Есть ли лучший подход к обеспечению точности?
Лучший ответ:
Мммм. ROW_NUMBER() работает с дублирующимися записями. (Когда есть дубликаты, это не детерминировано, какая строка получит какое значение.)
В вашем заявлении есть что-то еще, что не показано. Операция объединения, GROUP BY, некоторая дополнительная фильтрация. Мы не знаем, что это такое, но мы знаем, что ROW_NUMBER() работает с дубликатами.
РЕДАКТИРОВАТЬ
Это подозрительно:
CASE WHEN [Record] = ‘Start’ THEN 0 ELSE -1 + ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [Date] DESC END AS [RowNo]
Я никогда не видел аналитической функции внутри выражения CASE подобного этому раньше. Мне кажется, что ROW_NUMBER() будет оцениваться только в контексте ELSE. (Я не уверен, как SQL Server фактически обрабатывает это.)
Если бы я хотел, чтобы RowNo был 0, 1, 2,… Я бы просто вычитал 1…
ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [Date] DESC) — 1 AS RowNo
Если есть требование включить условие в [Record] = ‘Start’ качестве части оценки ROW_NUMBER, я бы включил это в PARTITION
OVER(PARTITION BY [ID],CASE WHEN [Record]=’Start’ THEN 0 END ORDER BY …)
Если мне нужно было манипулировать значением, возвращаемым ROW_NUMBER(), я бы нашел способ получить эту оценку в контексте встроенного представления или CTE, чтобы получить это оцениваемое и возвращенное, а затем muck with return значение во внешнем запросе.
Дело в том, что ROW_NUMBER() отлично работает с дубликатами. Если вы получаете результаты, которых вы не ожидаете, это потому, что в заявлении происходит что-то еще, например, выражение CASE или соединение или что-то в этом роде.
Ответ №1
Другой способ получить уникальный идентификатор для каждой строки – использовать виртуальный столбец %% Physloc %% (или %% lockres %% в SQL Server 2005), который позволяет идентифицировать физический адрес строки:
SELECT Id, Date, %%Physloc%% FROM [your_table]
Таким образом, вы получаете лучшие результаты для огромных таблиц, чем ROW_NUMBER.
