SQL: Как корректно вернуть 100 * Some / Total, если Total может быть 0

agaaaa

Интересует запрос вида

SELECT entry, value, total, 100 * value / total AS percent FROM Table

, но не вылетающий если в таблице есть поля с total = 0.

Yulka-MOl

На вскидку:



create table test( value double, total double);

insert into test (value, total) values (1.0,100.0);

insert into test (value, total) values (2.0, 0.0);

insert into test (value, total) values (3.0,300.0);



SELECT value, total, 100 * value / total AS percent FROM test where total != 0 

UNION SELECT value, total, 0 FROM test where total = 0;

Dasar

лучше уж тогда case



SELECT value, total, case when total=0 then null else  100 * value / total end AS percent FROM test

mkrec

Если про total известно, что он неотрицательный, можно просто в знаменатель поставить total+0.00001
Если total может быть и отрицательным, лучше не усложнять функцию, а проводить сравнение имхо

agaaaa

Прекрасно, спасибо!

Yulka-MOl

я не помню case входит в стандарт SQL или нет, а под Oracle точиться влом было.

agaaaa

нет, а под Oracle точиться влом было.

Не знаю что там с Ораклом, но у меня в T-SQL работает.

mkrec

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

SCIF32

это такой способ дискредитировать математиков что-ли?
ты же физик, вот за них и говори.

Dasar

это такой способ дискредитировать математиков что-ли?
ты же физик, вот за них и говори.

предложенный мидори способ удобный для тех случаев: когда формулы сложные, но адекватные (т.е. деление на ноль происходит только в тех случаях, когда и делимое тоже ноль).
допустим:
a1..n и b1..n - не отрицательные числа
A = sum(a1. ..an)
B = sum(b1..bn)
дальше надо записать формулу вида
k1 = a1/A + b1/B;
через предложенный midori способ это записывается
k1 = a1/notnull(A) + b1/notnull(B);
где notnull функция вида return x == 0? 1: x;
через if-ы это записывается страшнее:
k1 = 0
if (A != 0)
k1 += a1/A;
if (B != 0)
k1 += b1/B;

SCIF32

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

Dasar

тем более, что в таком виде как ты написал, способ неприменим, т.к. в добавок надо описывать то, как ты потом будешь отличать реальные данные от треша.

откуда возьмется треш?

rosali

а что разве по стандарту деление на 0 само по себе не возвращает null безо всяких case-ов?
в mysql по крайней мере ничего специального для этого делать не надо:



mysql> select 1/0;

+------+

| 1/0  |

+------+

| NULL |

+------+

1 row in set (0.00 sec)

Dasar

а что разве по стандарту деление на 0 само по себе не возвращает null безо всяких case-ов?

afaik, ошибка должна быть
ps
oracle и ms sql выдают ошибку

agaaaa

Его решение тоже адекватно. Но мне больше понравилось с case'ами.

SCIF32

под трешем я имею ввиду те значения, где должен получиться inf или nan - как ты их отличишь от остальных?
например, в общем случае такого сделать нельзя, если пользоваться плавающей точкой.

Dasar

под трешем я имею ввиду те значения, где должен получиться inf или nan - как ты их отличишь от остальных?

а почему при таком решений такие числа должны появиться?

SCIF32

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

6yrop

предложенный мидори способ удобный для тех случаев: когда формулы сложные, но адекватные

я бы не доверил считать свои деньги программисту, для которого применение условного оператора вызывает сложности, и он начинает искать обходные пути.
А ваще, на SQL сложные, большие формулы никто не считает.

mkrec

я считаю не деньги, а конформации цепей. И в моих формулах особые точки могут проявлять себя во многих местах. Ставить условные операторы в таких случаях не очень разумно, так как это резко усложняет (визуально) код, и найти опечатку становится заметно труднее. Кроме того, использование сравнений заметно замедляет (на десятки процентов) программу, так как сравнения, конечно, обычно приходится вести не a==b, а |a-b|<ε или более сложные.
В данном случае ни вопросы скорости, ни вопросы ясности кода не играют никакой существенной роли. Однако мне в голову не могло прийти, что аскера интересовало столь очевидное решение просто сравнить с нулем.

AlexV769

А почему ты предложил добавлять 0.00001?
Почему не 0.000001?
Почему не 0.00000001?
Почему не 0.00000000001?
Почему не 0.00000000000001?
Почему не 0.0000000000000001?
Почему не 0.0000000000000000001?
Почему не 0.0000000000000000000001?
Почему не 0.00000000000000000000001?
Или 0.00000000000000000000000000001 ещё больше делает код нечитаемым? :smirk:

ps. Это примерно как на физпраке сказать "достаточно малая величина". Тут же (100%) получишь контрольный вопрос в лоб - насколько малая?

mkrec

> А почему ты предложил добавлять 0.00001?
потому что очевидно, что автор может сам решить, какой именно величины ему достаточно.
> Тут же (100%) получишь контрольный вопрос в лоб - насколько малая?
У тебя какие-то фобии в отношении физпрака? Я всегда знаю, насколько малой должна быть величина в конкретных случаях, когда я ее использую, и этот "контрольный вопрос в лоб" я задаю себе перед тем, как использовать величину, а не после.

agaaaa

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

Оставить комментарий