[SQL] нюбский вопрос

kill-still

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

SELECT p1,p2,p3,

	SELECT SUM(p4) FROM (подзапрос) AS t2 WHERE t2.p4 < t1.p4

FROM (подзапрос) AS t1

hprt

какая версия сервера?
ps да, и еще напиши, пожалуйста, запрос как можно более точно - мне что-то кажется, что написанное при любом "(подзапросе)" работать не будет

kill-still

SQL Server 2005 \ MySQL 5
з.ы. а используя только стандарт .92 никак?

hprt

я не готов отвечать по мускулю, по 2005 - посмотри, я там пост обновил примерно в одно время с твоим ответом

kill-still

В подзапросе ничего криминального нет - самые простые функции только.
на самом деле проблема не первый раз возникает (я обобщил поэтому хотелось бы решение при условии сферического коня в вакууме.

hprt

ну тогда условно скажу - смотри в сторону CTE и оконных функций (ну там всякие SUM OVER (.... Ну это все про 2005. Если есть конкретика - можно точнее сказать. Еще судя по всему тебе нужен нарастающий итог - фак или топ 10 в ветке mssql на sql.ru. Поиск там же тоже должен помочь. Насчет стандартов - хз, это все хорошо, но кросс-бд решения по-моему очень редки - никто полностью не поддерживает + есть уникальные особенности, которые игнорировать не хочется.
А твой сферический запрос работать точно не будет в 2005 :), можешь подставить (select 1,2,3,4) as T(p1,p2,p3,p4) и попробовать

hwh2010



SELECT p1,p2,p3,

	SELECT SUM(p4) FROM (подзапрос1) AS t2 WHERE t2.p4 < t1.p4

FROM (подзапрос1) AS t1
или подразумевается это:


SELECT t1.p1,t1.p2,t1.p3, sum(t2.p4)

FROM (подзапрос1) AS t1, (подзапрос2) AS t2 

where t2.p4<t1.p4

group by t1.*

или я неверно понял задачу

hprt

ну вот я подумал на select ... , (select sum ...) from ..., однако такой вариант тоже был - поэтому и попросил уточнить

6yrop

как упростить конструкцию (подзапрос очень громоздкий чтобы подзапрос выполнялся один раз
даже если "(подзапрос)" будет тупо скопипищен в два места, он вряд ли будет выполняться два раза — сиквел сервер умный :) . SQL запрос это всего лишь запись того, что ты хочешь получить в результате, как это получить сиквел сервер решает сам (если хинты не использовать, их не рекомендуют использовать). Посмотри план запроса.
То есть проблема только в том, что не хорошо копипастить код подзапроса. По моим сведениям, в SQL92 копипаста можно избежать только через виьюхи, но это не всегда удобно. В MSSQL2005 для твоего случая подойдут Common Table Expressions (оператор WITH перед SELECT).

hwh2010

ну да, это если подзапросы никак не связаны

hprt

ну тут можно сказать про скалярные функции, которые даже будучи написанными всего лишь раз будут дергаться постоянно, что сильно снижает производительность, даже если там идет тупо число+1

6yrop

В ответ на:
code:
SELECT p1,p2,p3,
SELECT SUM(p4) FROM (подзапрос1) AS t2 WHERE t2.p4 < t1.p4
FROM (подзапрос1) AS t1
или подразумевается это:
code:
SELECT t1.p1,t1.p2,t1.p3, sum(t2.p4)
FROM (подзапрос1) AS t1, (подзапрос2) AS t2
where t2.p4<t1.p4
group by t1.*
эти запросы не эквивалентны, например, второй возвратит пустое множество на таблице из одной строки, а первый нет.

6yrop

ну тут можно сказать про скалярные функции, которые даже будучи написанными всего лишь раз будут дергаться постоянно, что сильно снижает производительность, даже если там идет тупо число+1
ну тк если речь об этом, то причем тут подзапрос? эта проблема и на просто таблице будет. Я так понял, в первом посте основной упор был на проблему громоздкого подзапроса.
Оконной функции, по моему, такой нет.

hwh2010

эти запросы не эквивалентны, например, второй возвратит пустое множество на таблице из одной строки, а первый нет.
тупанул, да. Надо left join

kill-still

или подразумевается это:
code:
SELECT t1.p1,t1.p2,t1.p3, sum(t2.p4)
FROM (подзапрос1) AS t1, (подзапрос2) AS t2
where t2.p4<t1.p4
group by t1.*
так нельзя - у тебя в group by и в конечной выборке разное количество полей.
а должно быть одинаковое, потому что аггрегирующие функции уменьшают детализацию.
 а в оконную функцию ты условие t2.p4<t1.p4 не сможешь запихать
в общем твой запрос работать не будет

hwh2010

так нельзя - у тебя в group by и в конечной выборке разное количество полей.а должно быть одинаковое
lol what? Это неверно. Верно такое утверждение:
если поле стоит в выборке без агрегатных функций, то оно должно быть упомянуто в group by
и то mysql-щики такое разрешают, выдавая одно из многих, вообще говоря, значений

kill-still

они не то чтобы неэквивалентные - второй запрос вообще нерабочий. и дело даже не только в различии количества полей...
спасибо просто огромное за WITH - совсем забыл про эту фичу :pray: :)

kill-still

Оконной функции, по моему, такой нет.
угу, к сожалению нет. а было бы прикольно накладывать окно для аггрегации к строке по буленовскому выражению скажем.

hprt

Ну я просто не стал бы однозначно говорить, что подзапрос будет выполняться один раз. Оконных функций таких (если опять таки нарастающий итог иметь в виду) - в 2005 нету (однако вроде как в стандарте в оракле работает. А СТЕ в принципе можно и рекурсивно использовать для этой задачи, не только как способ минимизации громоздкого запроса (хотя изврат на мой взгляд)

hprt

а первый работает? :D

kill-still

конечно работает.

hprt

  

/*------------------------

SELECT p1,p2,p3,

SELECT SUM(p4) FROM (select 1 p1, 2 p2, 3 p3, 4 p4) AS t2 WHERE t2.p4 < t1.p4

FROM (select 1 p1, 2 p2, 3 p3, 4 p4) AS t1



------------------------*/

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'SELECT'.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'FROM'.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'AS'.

SQL Server parse and compile time: 

   CPU time = 0 ms, elapsed time = 1 ms.

ладно, тебе виднее наверное, как обмануть сервер ;)
Если в мускуле работает - ну, там много что есть непонятного :D

hprt

ну и так, чтоб честность эксперимента показать...


/*------------------------

SELECT t1.p1,t1.p2,t1.p3, sum(t2.p4)

FROM (select 1 p1, 2 p2, 3 p3, 4 p4) AS t1, (select 1 p1, 2 p2, 3 p3, 4 p4) AS t2 

where t2.p4<t1.p4

group by t1.p1,t1.p2,t1.p3

------------------------*/

SQL Server parse and compile time: 

   CPU time = 0 ms, elapsed time = 3 ms.

p1          p2          p3          

----------- ----------- ----------- -----------



(0 row(s) affected)





SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server parse and compile time: 

   CPU time = 0 ms, elapsed time = 1 ms.

kill-still

если вместо зачения выборка - её надо обособлять: 
SELECT p1,p2,p3,

(

 SELECT SUM(p4) FROM (select 1 p1, 2 p2, 3 p3, 4 p4) AS t2

 WHERE t2.p4 < t1.p4

) AS t4sum

FROM (select 1 p1, 2 p2, 3 p3, 4 p4) AS t1  





----------------



p1	p2	p3	t4sum

1	2	3	NULL
:p

hprt

серьезно, что ли?
а что тогда сразу поточнее запрос не написал, вроде просил? ну и то, что первый запрос работает, а второй нет вроде как не я писал

mbolik1

А чем такой вариант не устраивает:
 


SELECT p1,p2,p3, SELECT SUM(t1.p4) OVER (ORDER BY t1.p4 RANGE BETWEEN UNBOUNDED PRECEDING AND 1 preceding)

FROM (подзапрос) AS t1

Судя по google и MySQL и SQL/2005 это умеют.
P.s. Писалось по синтаксису Oracle, т.ч. возможны ошибки
UPD: Нет, не умеют, а жаль.

hprt

К сожалению, Шурик, в 2005 агрегатные функции над окнами поддерживают только PARTITION BY :(
Оставить комментарий
Имя или ник:
Комментарий: