#sql #string #postgresql #numeric
#sql #строка #postgresql #числовой
Вопрос:
Столбец имеет строковые значения, такие как «1/200», «3.5» или «6». Как я могу преобразовать эту строку в числовое значение в одном SQL-запросе?
Мой фактический SQL более сложный, вот простой пример:
SELECT number_value_in_string FROM table
формат number_value_in_string будет одним из:
- ##
- #.##
- #/###
Мне нужно отсортировать по числовому значению этого столбца. Но, конечно, postgres не согласен со мной в том, что 1/200 является правильным числом.
Комментарии:
1. И под «числом», я полагаю, вы имеете в виду 0,005?
2. Я не знаю ни одной базы данных, которая распознала бы дробь как число. Они выполняют действия в десятичных числах. Если вы попытаетесь преобразовать в десятичное число, остерегайтесь целочисленной математики во многих dbs 1/200 = 0.
3. @MarkByers: да, я имел в виду 0,005
4. Почему бы вам просто не сохранить его как число для начала? Или, по крайней мере, пара чисел, то есть числитель и знаменатель? В противном случае вы можете использовать регулярное выражение, чтобы разделить его и выполнить математику на части. Мне кажется слишком умным и хрупким.
5. @ScottMarlowe: я работаю над уже установленной базой кода, поэтому я не могу этого сделать. хотелось бы, чтобы я мог. моя жизнь была бы намного проще
Ответ №1:
Видя ваше имя, я не могу не опубликовать упрощение вашего ответа:
SELECT id, number_value_in_string FROM table
ORDER BY CASE WHEN substr(number_value_in_string,1,2) = '1/'
THEN 1/substr(number_value_in_string,3)::numeric
ELSE number_value_in_string::numeric END, id;
Игнорирование возможного деления на ноль.
Комментарии:
1. хороший!! спасибо, что снизили репутацию моего имени 1 за это 🙂 кстати, нет такой вещи, как ошибка деления на ноль. jk
Ответ №2:
Я бы определил сохраненную функцию для преобразования строки в числовое значение, более или менее подобное этому:
CREATE OR REPLACE FUNCTION fraction_to_number(s CHARACTER VARYING)
RETURN DOUBLE PRECISION AS
BEGIN
RETURN
CASE WHEN s LIKE '%/%' THEN
CAST(split_part(s, '/', 1) AS double_precision)
/ CAST(split_part(s, '/', 2) AS double_precision)
ELSE
CAST(s AS DOUBLE PRECISION)
END CASE
END
Затем вы можете ORDER BY fraction_to_number(weird_column)
Если возможно, я бы пересмотрел дизайн данных. Действительно ли вся эта сложность необходима?
Комментарии:
1. дело в том, что я работаю с этой огромной базой кода, и я не могу изменить базу данных. Только один sql может передаваться в базу данных в функции, которую я изменяю. Но, тем не менее, случай, КОГДА встроенный SQL, безусловно, является законным!
2. Я бы изменил таблицу, чтобы иметь пользовательский тип, и правильно сохранил это как обе части. Мне так жаль, что вам приходится с этим работать. OTOH, если это никогда не изменится, вы можете сделать это таким образом, но я бы, по крайней мере, добавил некоторое исправление ошибок, чтобы предотвратить 200/0 etc от выдачи ошибки или сохранения или чего-то еще. Или вы не можете добавлять ограничения в БД?
3. @ScottMarlowe: Я думаю, мне скоро нужно будет поговорить об этом с командой разработчиков. Но пока я не думаю, что это скоро изменится. У них будет своя причина, потому что «пользовательское» поле должно быть строкового типа, чтобы оно могло принимать практически все, что ему задано, а затем соответствующим образом обрабатывать значение в зависимости от каждого случая: (
4. Так они будут хранить 10 * 4/13 ^ 8 или что-то в этом роде в конечном итоге? Это довольно дерьмовое требование.
Ответ №3:
Этот postgres SQL делает свое дело:
select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array(number_value_in_string, '/') as parts from table) x
Вот тест этого кода:
select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array('1/200', '/') as parts) x
Вывод:
0.005
Обратите внимание, что вам нужно было бы обернуть это в case инструкцию для защиты от ошибок деления на ноль и / или проблем с выходом массива за пределы и т. Д., Если столбец не содержит косой черты
Также обратите внимание, что вы могли бы сделать это без внутреннего выбора, но вам пришлось бы использовать regexp_split_to_array дважды (по одному разу для каждой части), и вы, вероятно, понесете удар по производительности. Тем не менее, может быть проще кодировать в строке и просто принять небольшую потерю производительности.
Комментарии:
1. потрясающая идея! 1 для regexp_split_to_array()
Ответ №4:
Мне удалось решить мою проблему. Спасибо всем. Это выглядит примерно так, в одном SQL. (Я использую POSTGRESQL)
Он будет сортировать строку, входящую в виде «#», «#. #» или «1 / #»
SELECT id, number_value_in_string FROM table ORDER BY CASE WHEN position('1/' in number_value_in_string) = 1
THEN 1/substring(number_value_in_string from (position('1/' in number_value_in_string) 2) )::numeric
ELSE number_value_in_string::numeric
END ASC, id
Надеюсь, это поможет кому-то в будущем.