Как я могу удалить конечные непрерывные записи в разделе с определенным значением?

Я использую последнюю версию SQL Server и имею следующую проблему. Учитывая приведенную ниже таблицу, требование довольно просто состоит в том, чтобы удалить «завершающие» записи в каждом _category разделе, в котором есть _value = 0. В этом контексте трейлинг означает, что когда записи расположены в порядке _date, любая серия или непрерывный блок записей с _value = 0 в конце списка должны быть удалены. Записи с _value = 0, у которых есть последующие записи в разделе с некоторым ненулевым значением, должны остаться.

create table #x (_id int identity, _category int, _date date, _value int)

insert into #x values (1, '2022-10-01', 12)
insert into #x values (1, '2022-10-03', 0)
insert into #x values (1, '2022-10-04', 10)
insert into #x values (1, '2022-10-06', 11)
insert into #x values (1, '2022-10-07', 10)

insert into #x values (2, '2022-10-01', 1)
insert into #x values (2, '2022-10-02', 0)
insert into #x values (2, '2022-10-05', 19)
insert into #x values (2, '2022-10-10', 18)
insert into #x values (2, '2022-10-12', 0)
insert into #x values (2, '2022-10-13', 0)
insert into #x values (2, '2022-10-15', 0)

insert into #x values (3, '2022-10-02', 10)
insert into #x values (3, '2022-10-03', 0)
insert into #x values (3, '2022-10-05', 0)
insert into #x values (3, '2022-10-06', 12)
insert into #x values (3, '2022-10-08', 0)

Я вижу несколько способов сделать это. Путь грубой силы состоит в том, чтобы пропустить записи через курсор в порядке дат и получить идентификатор любой записи, где _value = 0, и посмотреть, сохраняется ли он, пока категория не изменится. Я стараюсь избегать T-SQL, если могу сделать это в запросе.

С этой целью я подумал, что могу применить некоторые трюки с пробелами и островами и сделать что-то с оконными функциями. Я чувствую, что может быть способ использовать last_value() для этого, но пока я вижу, что это полезно только для определения разделов, которые имеют критерии, а не для того, чтобы помочь мне получить идентификаторы записей для удаления.

Желаемый результат — удаление записей 10, 11, 12 и 17.

Цените любую помощь.

sql sql-server gaps-and-islands

19.11.2022 02:59

Шаблоны Angular PrimeNg

Как привнести проверку типов в наши шаблоны Angular, использующие компоненты библиотеки PrimeNg, и настроить их отображение с помощью встроенной...

Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript

Если вы веб-разработчик (или хотите им стать), то вы наверняка гик и вам нравятся "Звездные войны". А как бы вы хотели, чтобы фоном для вашего...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Начала с розового дизайна

Pink Design - это система дизайна Appwrite с открытым исходным кодом для создания последовательных и многократно используемых пользовательских...

Шлюз в PHP

API-шлюз (AG) - это сервер, который действует как единая точка входа для набора микросервисов.

14 Задание: Типы данных и структуры данных Python для DevOps

проверить тип данных используемой переменной, мы можем просто написать: your_variable=100

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Я не уверен, что ваше требование требует подхода с пробелами и островами. Простая логика существования должна работать.

SELECT _id, _catrgory, _date, _value
FROM #x x1
WHERE _value <> 0 OR
    EXISTS (
        SELECT 1
        FROM #x x2
        WHERE x2._category = x1._category AND
              x2._date > x1._date AND
              x2._value <> 0
    );

См. этот db<.fiddle для демонстрации, включая оператор удаления, который инвертирует условие WHERE выше.

— 19.11.2022 03:42

Это хорошо. Будет интересно сравнить планы между вашим подходом и предложенным @forpas.

— 19.11.2022 16:10

19.11.2022 03:13

Используя общие табличные выражения, вы можете использовать:

WITH CTE_NumberedRows AS (
    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY _category ORDER BY _date)
    FROM #x
),
CTE_Keepers AS (
    SELECT _category, rnLastKeeper = MAX(rn)
    FROM CTE_NumberedRows
    WHERE _value <> 0
    GROUP BY _category
)
DELETE NR
FROM CTE_NumberedRows NR
LEFT JOIN CTE_Keepers K
    ON K._category = NR._category
WHERE NR.rn > ISNULL(K.rnLastKeeper, 0)

См. эту скрипку db<> для рабочей демонстрации.

Обновлено: мой исходный пост не обрабатывал крайний случай с нулевым значением. Это было исправлено выше вместе с некоторыми изменениями в именах. (Оригинал все еще можно найти здесь.

Сообщение Тима Бигелейзена может быть более простым подходом.

На самом деле я предпочитаю оконный подход везде, где это возможно. Я просто не мог придумать ваш запрос :-)

— 19.11.2022 03:50

Почему вы предпочитаете оконную функцию @timbiegeleisen? Ваш ответ очень ясен и прост?

— 19.11.2022 06:51

Подход с использованием оконных функций может быть более эффективным.

— 19.11.2022 06:57

19.11.2022 03:17

Ответ принят как подходящий

Предполагая, что все _values больше или равны 0, вы можете использовать оконную функцию MAX() в обновляемом CTE:

WITH cte AS (
  SELECT *, 
         MAX(_value) OVER (
           PARTITION BY _category 
           ORDER BY _date 
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
         ) max
  FROM #x
)  
DELETE FROM cte
WHERE max = 0;

Если есть отрицательные _value, используйте MAX(ABS(_value)) вместо MAX(_value).

Смотрите демо.

Никаких отрицательных значений, и этот подход больше всего похож на тот, который я пытался выкинуть из головы. У меня нет привычки использовать предложение фрейма, которого мне не хватало, я думаю. Спасибо.

— 19.11.2022 16:07

19.11.2022 12:26