Как выбрать строки и соседние строки

Фон

У меня есть таблица значений, которую некоторые требуют внимания:

| 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)

Но когда я пытаюсь использовать реальные данные:

9 миллионов строк
15 000 интересных строк
стоимость поддерева 63 318 400
это занимает несколько часов (прежде чем я сдаюсь и отменил его)

Вероятно, это более эффективный способ.

Чтение бонусов

Ответ 1

Это ваша существующая логика, переписанная с помощью движущегося макс:

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

Ответ 2

Вот один метод, который использует предложение 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.

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

Ответ 3

По-другому:

SELECT i1.*
    FROM Items i1, Items i2
        WHERE i2.AddedDate IS NULL AND ABS(i1.ID - i2.ID) <= 3

Я надеюсь, что есть индекс в столбце AddedDate.

Ответ 4

Просто попробовать другой подход, чем другие ответы... Как насчет использования переменной таблицы для хранения идентификаторов, которые вы хотите. Тогда вы присоединитесь. Я надеюсь, что вставка выполняется достаточно быстро, а затем 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, а их диапазоны идентификаторов перекрываются