Выбор номера строки главного/подчиненного элемента без подзапроса
Итак, я смотрю на таблицу под названием LINEITEM, в которой перечислены несколько номеров элементов, которые находятся в расположении «Главный/Компонентный». Их называют комплектами. Идея состоит в том, что комплект может содержать любое количество подэлементов. Вот краткий пример:
+----------+---------+---------+----------+-----------------+
| ORDER_NO | ITEM_ID | LINE_NO | KIT_FLAG | KIT_MASTER_LINE |
+----------+---------+---------+----------+-----------------+
| 858710 | 15291 | 1 | Y | 0 |
| 858710 | 113 | 2 | K | 1 |
| 858710 | 15279 | 3 | K | 1 |
| 858710 | 15292 | 4 | K | 1 |
| 858710 | 15321 | 5 | Y | 0 |
| 858710 | 106 | 6 | K | 5 |
| 858710 | 15563 | 7 | K | 5 |
| 858710 | 15564 | 8 | K | 5 |
| 858710 | 15296 | 9 | Y | 0 |
| 858710 | 116 | 10 | K | 9 |
| 858710 | 15479 | 11 | K | 9 |
| 858710 | 15480 | 12 | K | 9 |
+----------+---------+---------+----------+-----------------+
Таким образом, заказ 858710 здесь состоит из 3 комплектов. Строка 1 — это «основной» набор со значением «Y» в KIT_FLAG, а строки 2, 3 и 4 и все компоненты внутри этого набора со значением «K». Пока мы не дойдем до строки 5, где объявлен второй комплект, состоящий из строк 6, 7 и 8.
Мне нужно показать в каждом подэлементе, какой элемент строки является «мастером комплекта», к которому он принадлежит.
Мой желаемый результат - это то, что в настоящее время показано в столбце KIT_MASTER_LINE. Я даже могу выполнить это с помощью подзапроса:
SELECT ORDER_NO, ITEM_ID, LINE_NO, KIT_FLAG,
ISNULL((
SELECT MAX(LINE_NO)
FROM LINEITEM AS l2
WHERE
l2.LINE_NO < li.LINE_NO
AND li.KIT_FLAG = 'K'
AND l2.KIT_FLAG = 'Y'
AND l2.ORDER_NO = li.ORDER_NO
), 0) AS 'KIT_MASTER_LINE'
FROM LINEITEM li
ORDER BY LINE_NO
... Однако план выполнения не менее ужасен и занимает много-много минут, сканируя большую таблицу.
Думаю, мне любопытно, есть ли лучший способ сделать это с помощью оконных функций или подобных. Моя в остальном невероятно быстрая процедура и все ее оптимизации разрушаются из-за одного зависания, связанного с необходимостью дважды вызывать таблицу LINEITEM.
Я ценю любое понимание.
Можете ли вы опубликовать план выполнения? Как насчет определений таблиц, выборочных данных и приблизительного количества строк из вашей базы данных.
Здесь не показаны еще около 20 столбцов предсказуемых типов данных, которые выбраны. План выполнения: brentozar.com/pastetheplan/?id=rkLzGu9Y4 Есть честный шанс, как у меня это так хорошо, как я получаю. Просто что-то в нутре мне кричит, что это можно сделать с перегородками.
Что произойдет, если вы выберете на основе только номера заказа? он попадает в ключ? у вас есть номер заказа FK для родительской таблицы заказов?
>Что произойдет, если вы выберете на основе только номера заказа? Он будет сканировать кластеризованный индекс, как и ожидалось, поскольку нет внешнего предложения where. > Он нажимает на клавишу? Я не слежу за тобой здесь. >Есть ли у вас номер заказа FK для родительской таблицы заказов? Да. Но я не понимаю, как это помогает мне вычислять KIT_MASTER_LINE более чистым способом, чем сейчас.
Ответы 1
В конце концов я смог найти решение, вычитая количество циклов из номеров строк. Нет коррелированного подзапроса и выполняется за секунды. Ву!
SELECT *,
CASE WHEN KIT_FLAG = 'K'
THEN LINE_NO - ROW_NUMBER() OVER (PARTITION BY KIT_FLAG, [Group] ORDER BY LINE_NO)
ELSE 0
END AS 'KIT_MASTER_LINE'
FROM (
SELECT ORDER_NO, ITEM_ID, LINE_NO, KIT_FLAG,
ROW_NUMBER() OVER (ORDER BY ORDER_NO, LINE_NO) - ROW_NUMBER() OVER (PARTITION BY KIT_FLAG ORDER BY ORDER_NO, LINE_NO) AS [Group]
FROM LINEITEM
) AS [GroupTable]
ORDER BY ORDER_NO, LINE_NO
Вы пытались использовать внешнее применение вместо подзапроса?