Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
 Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Не совсем понятно в какую ветку, но так как вопрос охватывает все СУБД решил запостить сюда.

В каких СУБД можно использовать window функций в качестве выражений? Речь идет о такого рода запросах :

SELECT (SUM(col1) OVER (ORDER BY col2))/col3 FROM ....

или

SELECT .... FROM table1 JOIN table2 ON table2.col1 = (SUM(col1) OVER (ORDER BY col2))

Искать и ставить все возможные СУБД достаточно проблематично, поэтому может кто-то работал с window функциями и знает. Интересуют прежде всего PostgreSQL, MSSQL, Oracle, MySQL, Firebird, DB2. Последние 3 правда не уверен что вообще поддерживают window функции.
19 янв 10, 15:39    [8209490]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
vadiminfo
Member

Откуда: Обнинск
Сообщений: 4802
Nitro_Junkie
Не совсем понятно в какую ветку, но так как вопрос охватывает все СУБД решил запостить сюда.

В каких СУБД можно использовать window функций в качестве выражений? Речь идет о такого рода запросах :

SELECT (SUM(col1) OVER (ORDER BY col2))/col3 FROM ....

или

SELECT .... FROM table1 JOIN table2 ON table2.col1 = (SUM(col1) OVER (ORDER BY col2))

Искать и ставить все возможные СУБД достаточно проблематично, поэтому может кто-то работал с window функциями и знает. Интересуют прежде всего PostgreSQL, MSSQL, Oracle, MySQL, Firebird, DB2. Последние 3 правда не уверен что вообще поддерживают window функции.

В Оракле есть, называются там аналитическими ф-ями.
19 янв 10, 15:48    [8209590]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
В MySQL такого нет.
19 янв 10, 15:52    [8209638]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
miksoft
В MySQL такого нет.
хотя ряд задач такого рода решается с помощью переменных.
19 янв 10, 15:53    [8209653]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Ёш
Member

Откуда:
Сообщений: 2892
Nitro_Junkie
PostgreSQL
можно, начиная с текущего релиза 8.4 http://www.postgresql.org/docs/current/static/tutorial-window.html
19 янв 10, 16:15    [8209853]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Нет, еще раз, не вопрос поддерживаются ли window функции... Вопрос можно ли их использовать не только как :
SELECT SUM(col1) OVER (ORDER BY col2) FROM ..., но и брать в скобки - делить, умножать на другие колонки, использовать в условиях Join и т.п. (как я сверху написал)

То есть как грамматика построена, после "," она смотрит, что либо window функция, либо выражение. Или window функция один из видов выражений (типа как обычная функция)
19 янв 10, 16:42    [8210087]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Хм... в PostgreSQL походу их нельзя в GROUP BY и WHERE использовать (что в моем случае не так страшно), но видимо можно в выражениях и нельзя в условиях JOIN, хотя не факт....
19 янв 10, 16:46    [8210127]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Ёш
Member

Откуда:
Сообщений: 2892
Nitro_Junkie
Хм... в PostgreSQL походу их нельзя в GROUP BY и WHERE использовать (что в моем случае не так страшно), но видимо можно в выражениях и нельзя в условиях JOIN, хотя не факт....
да:
SELECT (SUM(col1) OVER (ORDER BY col2))/col3 FROM t;
 ?column?
----------
(0 rows)

SELECT (SUM(col1) OVER (ORDER BY col2))/col3 FROM t where (SUM(col1) OVER (ORDER BY col2))/col3 != 10;
ERROR:  window functions not allowed in WHERE clause

SELECT (SUM(col1) OVER (ORDER BY col2))/col3 FROM t group by (SUM(col1) OVER (ORDER BY col2))/col3, col3, col2;
ERROR:  window functions not allowed in GROUP BY clause

SELECT (SUM(a.col1) OVER (ORDER BY a.col2))/a.col3 FROM t a join t b on (SUM(a.col1) OVER (ORDER BY a.col2))/a.col3 > 10;
ERROR:  window functions not allowed in JOIN conditions
19 янв 10, 17:02    [8210281]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Ёш, спасибо большое... С Postgre разобрались

В Oracle'е судя по примерам та же байда, видно обусловлена особенностями реализации (что означает что использовать их нужно крайне осторожно, так как видимо window функции почти никак не оптимизируются)

А вот с MS SQL не понятно, может мелкософтовцы "обошли" остальных, правда слабо верится :)
19 янв 10, 17:13    [8210402]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
А что мешает запрос с оконными/аналитическими функциями убрать в подзапрос и уже его "делить, умножать на другие колонки, использовать в условиях Join и т.п." ?
19 янв 10, 17:33    [8210621]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Nitro_Junkie
Ёш, спасибо большое... С Postgre разобрались

В Oracle'е судя по примерам та же байда, видно обусловлена особенностями реализации (что означает что использовать их нужно крайне осторожно, так как видимо window функции почти никак не оптимизируются)

А вот с MS SQL не понятно, может мелкософтовцы "обошли" остальных, правда слабо верится :)

В MSSQL нельзя order для SUM. LAG/LEAD отсутствует, "Оконные функции могут использоваться только в предложениях SELECT или ORDER BY".
19 янв 10, 17:41    [8210718]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
miksoft,

Можно, но там где мне надо не очень удобно... Кроме того это как я уже писал означает, что оптимизация таких запросов никакая...
19 янв 10, 19:05    [8211196]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
Зайцев Фёдор,

Что значит нельзя order для SUM?

Но первый запрос что писал Еш все же пройдет?
19 янв 10, 19:07    [8211202]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Nitro_Junkie,

а все-таки почитать сначала документацию на соответствующий продукт не пробовали?
20 янв 10, 01:55    [8212184]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
Nitro_Junkie
Кроме того это как я уже писал означает, что оптимизация таких запросов никакая...
Это смотря с чем сравнивать. Если с наворотами, которые требовались бы, если бы не было оконных/аналитических функций, то оптимизация будет еще какая!
20 янв 10, 09:38    [8212648]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
?
Guest
А как вообще должна выполнятся аналитическая функция например в where???
Значение аналитической функции зависит от всей выборки. Добавляем в where аналитическую функцию, значит меняется выборка, значит меняется значение аналитической функции получаем замкнутый круг. Если в выборке 2 предиката с аналитическими функциями то получается вообще ужас.
20 янв 10, 09:55    [8212749]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
?,

Точно, что-то я не сразу это заметил, что она по выборке запроса работает, а не по всем данным... Впрочем это ответ на вопрос Aaron'а - пробовал читать, но даже мелкософтавская документация не сильно структурирована, чтобы выцепить в ней необходимую информацию, например про использование Window Aggregate Functions в Where... Я уж молчу про Oracle и остальных... :)

Тогда смежный вопрос, а можно как-нить заставить чтобы ORDER BY пропускал null'ы? А то есть только nulls first и nulls last а чтобы их вообще не брать нету :(
20 янв 10, 10:54    [8213161]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
Nitro_Junkie
Тогда смежный вопрос, а можно как-нить заставить чтобы ORDER BY пропускал null'ы? А то есть только nulls first и nulls last а чтобы их вообще не брать нету :(
WHERE myfield IS NOT NULL чем не подходит?
20 янв 10, 10:58    [8213189]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
miksoft,

Тем что придется делать вложенный запрос для этой Window Function, который опять таки непонятно как оптимизируется...
20 янв 10, 11:19    [8213409]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
miksoft
Member

Откуда:
Сообщений: 38919
Nitro_Junkie
miksoft,

Тем что придется делать вложенный запрос для этой Window Function
Зачем? что мешает добавить это условие к уже имеющимся условиям в секции WHERE ?
Nitro_Junkie
, который опять таки непонятно как оптимизируется...
За Оракл не переживайте, его оптимизатор достаточно неплохо умеет раскрывать вложенные запросы.
Кстати, именно условие IS NOT NULL в ряде случаев позволяет строить значительно более быстрые планы запросов, чем без него.
20 янв 10, 11:23    [8213454]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
miksoft,

А мне не надо чтобы весь запрос фильтровался по не null'ам Order'ов, мне нужно чтобы аналитическая функция не рассматривала null'евые order'ы и давала для них null. Можно конечно вставить Case when в выражение, в order'ы null first а потом все обернуть в CASE WHEN чтоб не 0, но как-то это не очень красиво.

Почему-то при тесте на генерируемых запросах с адаптерами разных БД, Oracle показал существенно более плохие результаты чем все остальные. Конечно может его надо долго и нудно "настраивать", но не понятно почему он не может это сделать сам как другие...
20 янв 10, 12:17    [8213991]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
alecsey
Member

Откуда: Москва
Сообщений: 830
Nitro_Junkie
А мне не надо чтобы весь запрос фильтровался по не null'ам Order'ов, мне нужно чтобы аналитическая функция не рассматривала null'евые order'ы и давала для них null. Можно конечно вставить Case when в выражение, в order'ы null first а потом все обернуть в CASE WHEN чтоб не 0, но как-то это не очень красиво.
бред какой то, можно конкретную задачу?

Nitro_Junkie
Почему-то при тесте на генерируемых запросах с адаптерами разных БД, Oracle показал существенно более плохие результаты чем все остальные. Конечно может его надо долго и нудно "настраивать", но не понятно почему он не может это сделать сам как другие...
давай сюда тесты
20 янв 10, 12:42    [8214288]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
alecsey
Member

Откуда: Москва
Сообщений: 830
Nitro_Junkie
в PostgreSQL походу их нельзя в GROUP BY и WHERE использовать (что в моем случае не так страшно), но видимо можно в выражениях и нельзя в условиях JOIN, хотя не факт....
логично, если аналитическая функция находится в WHERE блоке то не понятно над каким множеством она должна вычислятся?
20 янв 10, 12:47    [8214347]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Favn
Member

Откуда:
Сообщений: 585
Nitro_Junkie
... DB2. ...не уверен что вообще поддерживают window функции.
"Птичку нашу попрошу не обижать!" (с) Есть они, конечно, в DB2, они там появились еще до появления в SQL:2003.
Nitro_Junkie
использовать их нужно крайне осторожно, так как видимо window функции почти никак не оптимизируются
И что же это Вы про Оракл так уничижительно? :) Не поверю, что в нормальной СУБД есть конструкции SQL, которые "почти никак не оптимизируются". Иначе место было бы такой СУБД с ее оптимизатором... Ну, все поняли, где.
20 янв 10, 13:03    [8214519]     Ответить | Цитировать Сообщить модератору
 Re: Использование window функций  [new]
Nitro_Junkie
Member

Откуда:
Сообщений: 1090
alecsey,

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

Тесты в каком виде ? проект выложить? В любом случае это наблюдение, может просто ораклу мало памяти на сервере дали или че-нить еще, не знаю, но целью проверить кто из СУБД быстрее пока не ставили...
20 янв 10, 13:07    [8214575]     Ответить | Цитировать Сообщить модератору
Все форумы / Сравнение СУБД Ответить