MS SQL 2005: оконные функции
|
SELECT * FROM sample s1 WHERE ID_Trans in ( SELECT top 2 ID_Trans FROM sample s2 WHERE s1.ID_Customer = s2.ID_Customer ORDER BY amount DESC ) |
Более того, разница в скорости будет ощутимо расти с увеличением количества данных в таблице, поскольку в первом случае алгоритм довольно прост – внутренним запросом нумеруются записи внутри групп, практически за одну сортировку, а затем фильтром во внешнем запросе отсекаются все лишние записи. Во втором же случае, внутренний подзапрос выполняется заново, для каждой записи в таблице. Все это очень хорошо видно на планах запросов. На втором плане количество ожидаемых выполнений подзапроса – пятнадцать, так как в тестовой табличке 15 записей.
План запроса с аналитической функцией:
|
Операция Стоимость Количество ---------------------------------------------------------- |--Filter(WHERE:([Expr1003]<(3))) 0.022873 1 |--Sequence Project(...) 0.022866 1 |--Segment 0.022866 1 |--Segment 0.022866 1 |--Sort(ORDER BY:(...)) 0.022864 1 |--Clustered Index Scan(...) 0.006423 1 |
План запроса без использования аналитической функции:
|
Операция Стоимость Количество ----------------------------------------------------------- |--Nested Loops(Left Semi Join …) 0.18998 1 |--Clustered Index Scan(…) 0.00642 1 |--Filter(WHERE:(…)) 0.18350 15 |--Top(TOP EXPRESSION:((2))) 0.18348 15 |--Filter(WHERE:(…)) 0.18348 15 |--Sort(ORDER([Amount] DESC)) 0.18343 15 |--Clustered Index Scan(…) 0.00665 15 |
Ложка дегтя
Все это, конечно, здорово и замечательно, но есть некоторые негативные моменты, которые уменьшают радость от получения нового инструмента. Он, конечно, хорош, но пока что еще очень беден и не развит. Не считая встроенных агрегирующих функций, в SQL 2005 реализовано всего 4 ранжирующих функции, в то время как в ANSI SQL 2003 больше 30 различных типов аналитических функций...
Обидно и другое.. Как можно заметить, в синтаксисе для аналитических агрегатов отсутствует возможность указать сортировку внутри «окна». Для обычных, встроенных агрегатов это не имеет никакого значения, но в SQL Server 2005 появится возможность писать свои собственные агрегаты на CLR-совместимых языках, которыми, при желании, можно было бы расширить список функций, и вот для этих самодельных агрегирующих функций подобная возможность могла бы быть весьма полезной. Без возможности указать порядок сортировки записей в «окне» невозможно использовать целый класс агрегирующих функций, зависимых от порядка обработки данных. В принципе, ничто не мешает в процессе работы собственной агрегирующей функции складывать данные в некоторую коллекцию, сортировать их там должным образом, а затем обрабатыватать в требуемом порядке, но, очевидно, это не идеальное решение, так как приходится выполнять работу сервера.
Самое забавное, что при написании пользовательской агрегирующей функции можно указать с помощью специального атрибута, зависит ли результат от порядка обработки записей, но в отсутствие возможности указать этот самый порядок обработки, данный атрибут бесполезен. Видимо, те ребята, которые писали поддержку пользовательских агрегирующих функций, предусмотрели возможность создания функций, зависимых от порядка обработки данных, а вот у тех, кто писал аналитические функции, руки пока не дошли. Очень хочется верить, что к релизу дойдут...
Так же навевает грустные мысли очень бедный механизм указания «окна» для аналитической функции. На данный момент есть только один способ задать это «окно» – группировка. То есть, «окно» можно задать только с помощью указания колонки, одинаковые значения записей в которой являются признаком принадлежности к «окну». Однако возможности указания «окна» могут быть гораздо шире, но в текущей версии все это великолепие пока что отсутствует.