У меня есть таблица значений, которую некоторые требуют внимания:
| ID | AddedDate |
|---------|-------------|
| 1 | 2010-04-01 |
| 2 | 2010-04-01 |
| 3 | 2010-04-02 |
| 4 | 2010-04-02 |
| 5 | NULL | <----------- needs attention
| 6 | 2010-04-02 |
| 7 | 2010-04-03 |
| 8 | 2010-04-04 |
| 9 | 2010-04-04 |
| 2432659 | 2016-06-15 |
| 2432650 | 2016-06-16 |
| 2432651 | 2016-06-17 |
| 2432672 | 2016-06-18 |
| 2432673 | NULL | <----------- needs attention
| 2432674 | 2016-06-20 |
| 2432685 | 2016-06-21 |
Я хочу выбрать строки, где AddedDate имеет значение null, и я хочу выбрать строки вокруг него. В этом примере вопроса было бы достаточно сказать строки, где ID равен ± 3. Это означает, что я хочу:
| ID | AddedDate |
|---------|-------------|
| 2 | 2010-04-01 | ─╮
| 3 | 2010-04-02 | │
| 4 | 2010-04-02 | │
| 5 | NULL | ├──ID values ±3
| 6 | 2010-04-02 | │
| 7 | 2010-04-03 | │
| 8 | 2010-04-04 | ─╯
| 2432672 | 2016-06-18 | ─╮
| 2432673 | NULL | ├──ID values ±3
| 2432674 | 2016-06-20 | ─╯
Примечание: на самом деле это таблица из 9-миллиметровых рядов, а 15k - внимание.
Сначала я создаю запрос, который строит диапазоны, которые меня интересуют:
SELECT
ID-3 AS [Low ID],
ID+3 AS [High ID]
FROM Items
WHERE AddedDate IS NULL
Low ID High ID
------- -------
2 8
2432670 2432676
Поэтому моя первоначальная попытка использовать это работает:
WITH dt AS (
SELECT ID-3 AS Low, ID+3 AS High
FROM Items
WHERE AddedDate IS NULL
)
SELECT * FROM Items
WHERE EXISTS(
SELECT 1 FROM dt
WHERE Items.ID BETWEEN dt.Low AND dt.High)
Но когда я пытаюсь использовать реальные данные:
Вероятно, это более эффективный способ.
Это ваша существующая логика, переписанная с помощью движущегося макс:
WITH dt AS (
SELECT
ID, AddedDate,
-- check if there a NULL within a range of +/- 3 rows
-- and remember it ID
max(case when AddedDate is null then id end)
over (order by id
rows between 3 preceding and 3 following) as NullID
FROM Items
)
SELECT *
FROM dt
where id between NullID-3 and NullID+3
Вот один метод, который использует предложение windowing:
select i.*
from (select i.*,
count(*) over (order by id rows between 3 preceding and 1 preceding) as cnt_prec,
count(*) over (order by id rows between 1 following and 3 following) as cnt_foll,
count(addeddate) over (order by id rows between 3 preceding and 1 preceding) as cnt_ad_prec,
count(addeddate) over (order by id rows between 1 following and 3 following) as cnt_ad_foll
from items
) i
where cnt_ad_prec <> cnt_prec or
cnt_ad_foll <> cnt_foll or
addeddate is null;
order by id;
Это возвращает все строки, которые имеют NULL в столбце или находятся в трех строках в NULL.
Необходимость сравнения с подсчетом заключается в том, чтобы избежать проблем с краем на наименьших и наибольших идентификаторах.
По-другому:
SELECT i1.*
FROM Items i1, Items i2
WHERE i2.AddedDate IS NULL AND ABS(i1.ID - i2.ID) <= 3
Я надеюсь, что есть индекс в столбце AddedDate.
Просто попробовать другой подход, чем другие ответы... Как насчет использования переменной таблицы для хранения идентификаторов, которые вы хотите. Тогда вы присоединитесь. Я надеюсь, что вставка выполняется достаточно быстро, а затем SELECT может использовать кластеризованный индекс в элементах. К сожалению, у меня нет данных о вашем количестве данных для проверки его эффективности:
DECLARE @userData TABLE(
idInRange int NOT NULL
)
INSERT INTO @userData (idInRange)
SELECT DISTINCT i.Id + r
FROM Items i
CROSS JOIN (
SELECT -3 as r UNION ALL SELECT -2 as r UNION ALL SELECT -1 as r UNION ALL SELECT 0 as r UNION ALL
SELECT 1 as r UNION ALL SELECT 2 as r UNION ALL SELECT 3 as r
) yourRange
WHERE AddedDate IS NULL;
SELECT i.*
FROM @userData u
INNER JOIN Items i ON i.ID = u.idInRange
Отредактировано для добавления DISTINCT при заполнении переменной таблицы, чтобы избежать дублирования строк на случай, если есть две смежные даты NULL, а их диапазоны идентификаторов перекрываются