Несколько Row_Number () вызовы в единственном SQL-запросе
В C++ assert() очень удобный инструмент. Я не только предоставляю ему условие оценить, но также и сообщение, указывающее что случилось:
assert( isConditionValid && "ABC might have failed because XYZ is wrong." );
, Когда нет никакой фактической переменной для проверки или Вы оказываетесь в ситуации, которая никогда не должна была происходить (обработчик 'по умолчанию' переключателя ()), это работает также:
assert( 0 && "Invalid parameter" );
Это не только утверждает в режиме отладки, но также и говорит Вам, что пошло не так, как надо одновременно.
я получил это из "Стандартов Кодирования C++", если я помню правильно.
3 ответа
Для каждого ROW_NUMBER требуется, чтобы сначала были отсортированы строки. Поскольку ваши два RN имеют разные условия ORDER BY, запрос должен выдать результат, затем упорядочить его для первых RN (он может быть уже упорядочен), создать RN, затем заказать его для второго RN и дать второй результат RN. Просто не существует волшебной пыльцы пикси, которая могла бы материализовать значение номера строки без учета того, где строка находится в требуемом порядке.
Некоторое нестандартное мышление: если вам нужны эти данные часто и / или быстро, а базовый набор данных не часто меняются (для достаточно высоких значений «часто»), не могли бы вы предварительно вычислить любое из этих значений и сохранить их в какой-либо форме предварительно агрегированной таблицы?
(Да, это демонормализация, но если вам нужна производительность превыше всего остального , стоит задуматься.)
Я не уверен, что он может распараллелить это, потому что он должен выполнять несекционированное сканирование (в зависимости от населения и квадратных миль). Они будут конфликтовать с каждым диском, поэтому он должен сначала поместить все в память, по крайней мере, один раз, а затем он может иметь право на распараллеливание, если он достаточно большой.
В любом случае следующие действия значительно (40%) ) быстрее для меня:
;WITH cte AS (
SELECT
StateID
,TimeDimID
,ConstructionStatusID
,NumberOfRows = COUNT(*) OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID)
,PopulationSizeRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY PopulationSize)
,SquareMilesRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY SquareMiles)
,PopulationSize
,SquareMiles
FROM TestMedian
)
, ctePop AS (
SELECT MinPopNum = MIN(PopulationSizeRowNum)
, MaxPopNum = MAX(PopulationSizeRowNum)
, StateID, TimeDimID, ConstructionStatusID
, MedianPopulationSize= AVG(PopulationSize)
FROM cte T
WHERE PopulationSizeRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2)
GROUP BY StateID, TimeDimID, ConstructionStatusID
)
, cteSqM AS (
SELECT MinSqMNum = MIN(SquareMilesRowNum)
, MaxSqMNum = MAX(SquareMilesRowNum)
, StateID, TimeDimID, ConstructionStatusID
, MedianSquareMiles= AVG(SquareMiles)
FROM cte T
WHERE SquareMilesRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2)
GROUP BY StateID, TimeDimID, ConstructionStatusID
)
SELECT s.StateID, s.TimeDimID, s.ConstructionStatusID
, MinPopNum, MaxPopNum, MedianPopulationSize
, MinSqMNum, MaxSqMNum, MedianSquareMiles
FROM ctePop p
JOIN cteSqM s ON s.StateID = p.StateID
AND s.TimeDimID = p.TimeDimID
AND s.ConstructionStatusID = p.ConstructionStatusID
Кроме того, сами сортировки должны быть распараллелены, когда они станут достаточно большими. Тем не менее, вам потребуется не менее 100 000 тестовых строк, прежде чем это может произойти.
Хорошо, да, я получаю параллелизм после того, как загружу его достаточно с помощью этого оператора:
INSERT INTO TestMedian
SELECT abs(id)%3,abs(id)%2,abs(id)%5, abs(id), colid * 10000
From master.sys.syscolumns, (select top 10 * from master.dbo.spt_values)a