Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Деление на ноль там, где быть не может.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
declare  @data table (id int , sum1 money, sum2 int )

insert into @data 
	values (1, 2, 0), (1, 2, 0)


select case when sum2 <> 0
-- при sum2 = 0 сюда никогда не попадем
			then sum(sum1 / sum2) over (partition by id)

		end
	from @data


Код выводит ошибку - "Сообщение 8134, уровень 16, состояние 1, строка 8 Обнаружена ошибка: деление на ноль."


Нашел выход - но по ощущениям - костыль чистой воды:
select case when sum2 <> 0
			then sum(
					case when sum2 = 0 then 0 else sum1 / sum2 end) over (partition by id)

		end
	from @data


Где я не прав? Как обойти? Или приведенный вариант обхода единственный верный?
16 янв 15, 20:10    [17130427]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Galyamov Rinat
Где я не прав?
Это же оконная функция, она вычисляется для всей таблицы, а не для одной строки.
Вот она и вычисляется.
А ваш CASE просто выводит зараннее расчитанное значение в конкретной строке, или не выводит - но оно всё равно расчитано.
Конечно, в данном случае, по вашим данным, получается, что ни для одной строки значение не выводится, но это уже лругой вопрос.

Своим "костылём" вы меняете не регулирование вывода результата (как в первом CASE), а расчётом результата - в расчёте не будут учитываться строки, в которых sum2=0

Т.е. это не "костыль", который правит ошибку оптимизатора, а изменение логики самого расчёта.
16 янв 15, 21:30    [17130703]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
alexeyvg
Т.е. это не "костыль", который правит ошибку оптимизатора, а изменение логики самого расчёта.
Т.е. получается, вы неправильно представляете себе оконную функцию. По смыслу оконной функции её применение не совсем понятно в CASE по условию значений полей.
Либо какой то очень навороченный случай, либо надо писать по другому.
16 янв 15, 21:34    [17130720]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
o-o
Guest
по-моему, надо считать в лоб и все тут:
когда не 0, делим, когда 0 -- NULL или 0 по вкусу, и суммировать это:
insert into @data 
	values (1, 2, 0), (1, 2, 0)

select SUM(case when sum2 <> 0 then sum1 / sum2 end) over (partition by id)
from @data
---
NULL
NULL

select SUM(case when sum2 <> 0 then sum1 / sum2 else 0 end) over (partition by id)
from @data
---
0,00
0,00
16 янв 15, 22:30    [17130910]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
o-o
по-моему, надо считать в лоб и все тут:
когда не 0, делим, когда 0 -- NULL или 0 по вкусу, и суммировать это
Ну, это от задачи зависит. Тут непонятна задача ТС - показывать или не показывать для разных строк значение агрегата, посчитанного для всей таблицы, либо при подсчёте по разному учитывать значения в строках?
16 янв 15, 23:06    [17131014]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
alexeyvg
o-o
по-моему, надо считать в лоб и все тут:
Ну, это от задачи зависит.


Задача провести пропорциональное распределение с закидыванием на последнюю строку остатков:
case
							

								when Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii < Ostatok_Oplati
									then Summa_Nachisl_4_Raspr_Usl
							
								else 
										case when ROW_NUMBER () over (partition by id_Kategorii order by id_Uslugi) = 1
												then
														Ostatok_Oplati -
															sum(round(Ostatok_Oplati * Summa_Nachisl_4_Raspr_Usl / Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii, 2)) over (partition by id_Kategorii)
															+ round(Ostatok_Oplati * Summa_Nachisl_4_Raspr_Usl / Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii, 2)

												else round(Ostatok_Oplati * Summa_Nachisl_4_Raspr_Usl / Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii, 2)
											end

							end Oplata


При этом в case с оконной функцией при Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii = 0 по условиям выборки никогда не попадает.

По вашим пояснениям понял следующее:

Оптимизатор просчитывает значение всех оконных функций для всех строк, а потом, на основании условий в case принимает решение об отображении значения в поле.

Всем спасибо. Тема закрыта.
17 янв 15, 08:53    [17131543]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Galyamov Rinat
При этом в case с оконной функцией при Summa_Nachisl_4_Raspr_Itogo_Po_Kategorii = 0 по условиям выборки никогда не попадает.

По вашим пояснениям понял следующее:

Оптимизатор просчитывает значение всех оконных функций для всех строк, а потом, на основании условий в case принимает решение об отображении значения в поле.

Всем спасибо. Тема закрыта.
Я вроде написал, что это не поведение оптимизатора, а логика запроса.

Это всё равно что на разъяснение оператор + складывает два числа написать "Оптимизатор просчитывает значение ..."

Это не оптимизатор, это вы говорите серверу - "посчитай мне для всех строк значение sum1/sum2, независимо от значений поля sum2"

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

Например, запрос:
select sum1/sum2
from T
where sum2 <> 0
может исполниться сервером так, что будет "деление на 0".
Вот это как раз поведение оптимизатора, приводящее к ошибке.

А в вашем случае это вы так сказали серверу вычислять, это не оптимизатор.
18 янв 15, 01:01    [17133407]     Ответить | Цитировать Сообщить модератору
 Re: Деление на ноль там, где быть не может.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
alexeyvg
Я вроде написал, что это не поведение оптимизатора, а логика запроса.


Пусть будет логика запроса.
Спасибо.
18 янв 15, 16:14    [17134218]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить