Выберите самую низкую дату из диапазона и исключите другой диапазон

У меня есть таблица (назовем ее audit), которая выглядит так:

+--------------------------------------------------------------------------+
| id | recordId | status | mdate                   | type  | relatedId     |
+--------------------------------------------------------------------------+
| 1  | 3006     | A      | 2013-04-03 23:59:01.275 | type1 | 1             |
| 2  | 3025     | B      | 2013-04-04 00:00:02.134 | type1 | 1             |
| 3  | 4578     | A      | 2013-04-04 00:04:30.033 | type2 | 1             |
| 4  | 7940     | C      | 2013-04-04 00:04:32.683 | type1 | <NULL>        |
| 5  | 3006     | D      | 2013-04-04 00:04:32.683 | type1 | <NULL>        |
| 6  | 4822     | E      | 2013-04-04 00:04:32.683 | type2 | <NULL>        |
| 7  | 3006     | A      | 2013-04-04 00:06:54.033 | type1 | 2             |
| 8  | 3025     | C      | 2013-04-04 00:06:54.033 | type1 | 2             |

... и так далее для миллионов строк. И еще одна таблица, которую мы назовем related:

+-------------+
| id | source |
+-------------+
| 1  | src_X  |
| 2  | src_Y  |
| 3  | src_Z  |
| 4  | src_X  |
| 5  | src_X  |

... и так далее для сотен тысяч строк.

В обеих таблицах больше столбцов, но это все, что нам нужно для описания проблемы. Столбец relatedId присоединяется к таблице related. recordId также соединяется с другой таблицей, и в audit будет несколько записей с одним и тем же recordId.

Я пытаюсь создать запрос, который выдаст следующий результат:

+-----------------+
| source  | count |
+-----------------+
| src_X   | 1643  |
| src_Y   | 255   |
| NULL    | 729   |
+-----------------+

Подсчет представляет собой количество записей в audit, которые имеют данный type (например, "type1") и находятся в пределах набора статусов (например, "A", "B", "C"), которые затем оставляются внешними, присоединяются к related и группируются по source.

Загвоздка в том, что я хочу включать только записи из audit, которые находятся в определенном диапазоне дат, и я также хочу объединить только от audit до related самой старой записи в этом диапазоне для каждого recordId. Кроме того, я хочу игнорировать любые записи, соответствующие критериям type и status, но имеющие запись для того же recordId, которая старше диапазона дат.

Итак, чтобы уточнить данные из приведенного выше примера: допустим, мне нужен тип type1 и значения статуса "A", "B", "C" с диапазоном дат от 2013-04-04 до 2013-04-05. Ряды 2 и 4 будут включены в подсчет. Строка 3 исключена, так как содержит неверный type. Строка 5 исключена, так как статус неверен. Строка 6 исключена, поскольку и статус, и тип неверны. Строка 1 исключена, поскольку она находится за пределами диапазона дат. Строка 7 также исключается, так как есть еще одна строка (строка 1), которая соответствует критериям статуса и типа с тем же recordId, который старше начала диапазона дат. Строка 8 исключается, так как и строка 8, и строка 2 имеют одинаковые recordId и соответствуют критериям, но мы учитываем только самую старую запись из двух в пределах диапазона.

Другими словами, я хочу подсчитывать только первый раз, когда запись для данного RecordId появляется в таблице и находится в пределах целевого диапазона дат.

Мы придумали следующее:

WITH data (recordId, id) AS (
    SELECT a.recordId, MIN(a.id)
    FROM audit a
    WHERE a.status in ('A','B','C')
        AND type = 'type1'
    GROUP BY a.recordId
)
SELECT r.source, COUNT(*)
FROM data d
    JOIN audit a ON d.id = a.id
    LEFT JOIN related r ON a.relatedId = r.id
WHERE a.mdate >= '2013-04-04 00:00:00.000'
    and a.mdate < '2013-04-05 00:00:00.000' 
GROUP BY r.source

Это будет выполняться на MSSQL Server 2008 и в настоящее время основано на том факте, что идентификаторы таблиц аудита генерируются автоматически. Поскольку идентификаторы генерируются в момент вставки записи, а mdate также является отметкой времени вставки, а записи никогда не обновляются после вставки, я думаю, что это нормально. Похоже, что запрос дает правильный результат на ограниченном наборе тестовых данных, но я надеялся на второе мнение.

  • Этот запрос выглядит нормально?
  • Можно ли улучшить его работу?

sql sql-server query-performance
person dave.c    schedule 26.06.2013    source источник
comment
Диапазон дат в вычисляемом табличном выражении, вероятно, улучшит производительность.   -  person Dan Bracuk    schedule 26.06.2013
comment
Хорошая точка зрения. Добавление AND a.mdate < '2013-04-05 00:00:00.000' к вычисляемой таблице поможет ограничить количество возвращаемых записей.   -  person dave.c    schedule 26.06.2013
comment
Чтобы улучшить производительность запросов, позаботьтесь и об индексации. Используйте индексирование полей предложения WHERE, полей соединения и снова проверьте производительность.   -  person    schedule 26.06.2013

Ответы (1)


arrow_upward
4
arrow_downward

Вы можете использовать функцию ROW_NUMBER() для ранжирования записей на основе RecordId и mDate, а затем ограничить результаты тем, где первое появление происходит между указанными вами датами.

WITH data  AS 
(   SELECT  a.relatedId, a.mdate, rn = ROW_NUMBER() OVER(PARTITION BY a.RecordId ORDER BY a.mdate)
    FROM    audit a
    WHERE   a.status in ('A','B','C')
    AND     type = 'type1'
)
SELECT  r.source, [Count] = COUNT(*)
FROM    data d
        LEFT JOIN related r 
            ON d.relatedId = r.id
WHERE   d.rn = 1
AND     d.mdate >= '2013-04-04 00:00:00.000'
AND     d.mdate < '2013-04-05 00:00:00.000' 
GROUP BY r.source;

Я не уверен, что это будет работать лучше, чем ваше текущее решение, но решит проблему использования хронологических вставок. Если хронологические вставки не являются проблемой, вы можете изменить ORDER BY в функции ROW_NUMBER(), чтобы использовать идентификатор, так как сортировка по кластеризованному ключу будет быстрее.

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

Этот скрипт SQL показывает, что два запроса (мой и ваш) заканчиваются одним и тем же результатом , однако, когда вы посмотрите на статистику ввода-вывода, вы увидите, что для вашего запроса вы получаете:

(2 row(s) affected)
Table 'Related'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Audit'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Используя ROW_NUMBER(), вы получаете:

(2 row(s) affected)
Table 'Related'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Audit'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ключевым фактором является то, что на одно логическое чтение меньше. Беглый взгляд на план выполнения показывает, что решение ROW_NUMBER() имеет на одну ветвь меньше и оценивается в 37 % от стоимости пакета, тогда как ваше решение составляет 63 %, поэтому на этом небольшом наборе данных это может показаться производительностью. улучшение.

введите здесь описание изображения

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

Например, глядя на план выполнения для CTE, это составляет 50% стоимости запроса для моего запроса:

введите здесь описание изображения

Добавляя этот индекс:

CREATE INDEX IX_Audit_ALL ON Audit (recordId, MDate, RelatedID, status, type)

Мне удалось сократить это до 18% от стоимости запроса.

введите здесь описание изображения

Однако на практике, не зная больше, я не могу с уверенностью сказать, что этот индекс (а) поможет этому запросу с вашими данными и (б) что он не вызовет других проблем с вашей базой данных, замедляя вставку/обновления.

person GarethD    schedule 26.06.2013
comment
Спасибо за исчерпывающий ответ! - person dave.c; 26.06.2013