SQL Server: запрос функции T-SQL. Часть II
Содержание:
1. Часть I;
2. Часть II (Вы читаете данный раздел);
3. Часть III.
Такие задачи часто пытаются решать с использованием рекурсивных запросов. Эти решения могут быть изящными, но они не очень эффективны. Также применяется метод, известный как «подстановочное обновление» (quirky update), который очень эффективен, но не гарантирует результата, поскольку зависит от физического порядка обработки. Пока я не нашел эффективного, гарантирующего результат решения на основе наборов для этой задачи и вынужден использовать итеративные решения (на основе T-SQL или CLR).
Пример простого итеративного решения с использованием курсора
В коде выше приведен пример простого итеративного решения T-SQL с использованием курсора.
Программный код определяет переменную курсора и использует ее, чтобы извлекать транзакции по одной в хронологическом порядке. Величины накапливаются в переменной с именем @totalqty. После извлечения каждой строки программный код проверяет, не превышает ли накопленная величина емкость контейнера. Если происходит превышение, переменной с именем @depletionqty присваивается значение @totalqty, а затем текущее значение @totalqty обнуляется. После этого программный код записывает сведения о текущей транзакции (txid и qty) наряду с текущими значениями @totalqty и @depletionqty в табличную переменную. После прохода по всем транзакциям код запрашивает табличную переменную, чтобы получить желаемый резул ьтат.
Решение с использованием RESET WHEN
Решение с использованием RESET WHEN (не поддерживается в SQL Server 2017)
Недостатки применения итеративных решений хорошо известны. Вопрос в том, существует ли удачная альтернатива на основе набора. До настоящего времени я не нашел таковой для поставленной задачи с использованием существующих инструментов Т-SQL, но хотел бы, чтобы она когда-нибудь появилась. Как отмечалось выше, компания Teradata поддерживает оконный оператор с именем RESET WHEN, который пересоздает оконный раздел при выполнении определенного условия. Ценность этого предложения в том, что в условии можно использовать оконную функцию и вы можете узнать, что аккумулировано до предыдущей строки. В нашей задаче оконный раздел пересоздается, когда сумма величин от начала секции до предыдущей строки превышает лимит входного контейнера (код выше). Помните, что на сегодня этот программный код не поддерживается в SQL Server. Если бы он поддерживался, то были бы получены такие выходные данные, как показано на экране ниже.
Результаты решения с использованием RESET WHEN
Как мы видим, оконный раздел пересоздан после транзакций с идентификаторами 2, 5 и 8.
Чтобы получить окончательный желаемый результат, присваиваем общей величине контейнера (назовем ее totalqty) значение О, когда сумма с накоплением превышает лимит контейнера, и значение суммы с накоплением в противном случае. Затем можно вычислить величину убывания (назовем ее depletionqty) как сумму с накоплением за вычетом общей величины. В коде ниже приведен полный программный код решения.
Полный программный код решения
Как видите, решение простое, компактное и изящное.
Недавно Шарон Ример из компании Naya Technologies представил вариант этой задачи на основе заказа от одного из клиентов компании.
Требовалось вычислить, сколько раз контейнер превышает входной лимит. Для этой цели следует иметь такое же определение для СТЕ С и использовать внешний запрос для подсчета.
Решение с выражением и внешним запросом
Полное решение выглядит таким образом, как показано в коде выше. Опять-таки компактно и изящно.
Вы не IT-специалист, а волевая женщина, владеющая маникюрным салоном, которую интересует автоматизация своего бизнеса, а не какие-то там запросы функции T-SQL. Именно поэтому спешу познакомить вас с СРМ для Маникюрного салона. Это отличная программа для записи клиентов, которая значительно упростит ваши рабочие будни и поднимет КПД производственного процесса.
1. Часть I;
2.
3. Часть III.
Решение на основе курсора
Такие задачи часто пытаются решать с использованием рекурсивных запросов. Эти решения могут быть изящными, но они не очень эффективны. Также применяется метод, известный как «подстановочное обновление» (quirky update), который очень эффективен, но не гарантирует результата, поскольку зависит от физического порядка обработки. Пока я не нашел эффективного, гарантирующего результат решения на основе наборов для этой задачи и вынужден использовать итеративные решения (на основе T-SQL или CLR).
Пример простого итеративного решения с использованием курсора
В коде выше приведен пример простого итеративного решения T-SQL с использованием курсора.
Программный код определяет переменную курсора и использует ее, чтобы извлекать транзакции по одной в хронологическом порядке. Величины накапливаются в переменной с именем @totalqty. После извлечения каждой строки программный код проверяет, не превышает ли накопленная величина емкость контейнера. Если происходит превышение, переменной с именем @depletionqty присваивается значение @totalqty, а затем текущее значение @totalqty обнуляется. После этого программный код записывает сведения о текущей транзакции (txid и qty) наряду с текущими значениями @totalqty и @depletionqty в табличную переменную. После прохода по всем транзакциям код запрашивает табличную переменную, чтобы получить желаемый резул ьтат.
Решение с использованием RESET WHEN
Решение с использованием RESET WHEN (не поддерживается в SQL Server 2017)
Недостатки применения итеративных решений хорошо известны. Вопрос в том, существует ли удачная альтернатива на основе набора. До настоящего времени я не нашел таковой для поставленной задачи с использованием существующих инструментов Т-SQL, но хотел бы, чтобы она когда-нибудь появилась. Как отмечалось выше, компания Teradata поддерживает оконный оператор с именем RESET WHEN, который пересоздает оконный раздел при выполнении определенного условия. Ценность этого предложения в том, что в условии можно использовать оконную функцию и вы можете узнать, что аккумулировано до предыдущей строки. В нашей задаче оконный раздел пересоздается, когда сумма величин от начала секции до предыдущей строки превышает лимит входного контейнера (код выше). Помните, что на сегодня этот программный код не поддерживается в SQL Server. Если бы он поддерживался, то были бы получены такие выходные данные, как показано на экране ниже.
Результаты решения с использованием RESET WHEN
Как мы видим, оконный раздел пересоздан после транзакций с идентификаторами 2, 5 и 8.
Чтобы получить окончательный желаемый результат, присваиваем общей величине контейнера (назовем ее totalqty) значение О, когда сумма с накоплением превышает лимит контейнера, и значение суммы с накоплением в противном случае. Затем можно вычислить величину убывания (назовем ее depletionqty) как сумму с накоплением за вычетом общей величины. В коде ниже приведен полный программный код решения.
Полный программный код решения
Как видите, решение простое, компактное и изящное.
Недавно Шарон Ример из компании Naya Technologies представил вариант этой задачи на основе заказа от одного из клиентов компании.
Требовалось вычислить, сколько раз контейнер превышает входной лимит. Для этой цели следует иметь такое же определение для СТЕ С и использовать внешний запрос для подсчета.
SELECT COUNT (CASE WHEN runsum >
@maxallowedqty THEN 1 END)
AS timesexceeded
FROM C;
Решение с выражением и внешним запросом
Полное решение выглядит таким образом, как показано в коде выше. Опять-таки компактно и изящно.
Вы не IT-специалист, а волевая женщина, владеющая маникюрным салоном, которую интересует автоматизация своего бизнеса, а не какие-то там запросы функции T-SQL. Именно поэтому спешу познакомить вас с СРМ для Маникюрного салона. Это отличная программа для записи клиентов, которая значительно упростит ваши рабочие будни и поднимет КПД производственного процесса.
