Блог

В моем наборе данных есть столбцы, содержащие такие числа, как 83.420 , 43.317 , 149.317 , … и эти столбцы хранятся в виде строки. Точка в числах не представляет десятичную точку, т. Е. Число 83.420 в основном 83420 и т. Д. Один из способов удалить эту точку из чисел в этом столбце-использовать TRANSLATE функцию следующим образом:

SELECT translate('83.420', '.', '')

который возвращается 83420 . Но как я могу применить эту функцию ко всем строкам в наборе данных? Я попробовал это сделать, однако у меня ничего не вышло:

SELECT translate(SELECT num_column FROM my_table, '.', '')

Я сталкиваюсь с ошибкой SQL Error [42601]: ERROR: syntax error at end of input . Есть идеи, как я могу применить функцию translate полностью к одному столбцу данных? или есть какая-нибудь лучшая идея использовать вместо translate этого ?

Вы даже можете привести результат к числовому значению, как это:

 SELECT translate(num_column, '.', '')::integer from the_table;
-- average:
SELECT avg(translate(num_column, '.', '')::integer from the_table;
 

или использовать replace

 SELECT replace(num_column, '.', '')::integer from the_table;
-- average:
SELECT avg(replace(num_column, '.', '')::integer) from the_table;
 

Пожалуйста, обратите внимание, что хранение чисел в виде форматированного текста — (очень) плохая идея. Вместо этого используйте собственный числовой тип.

Два варианта.

Накрыть стол:

 create table string_conv(id integer, num_column varchar);
insert into string_conv values (1, 83.420), (2, 43.317), (3, 149.317 );
select * from string_conv ;
 id | num_column 
---- ------------
  1 | 83.420
  2 | 43.317
  3 | 149.317
 

Первый вариант оставить в строковом поле:

 update string_conv set num_column = translate(num_column, '.', '');
 select * from string_conv ;
 id | num_column 
---- ------------
  1 | 83420
  2 | 43317
  3 | 149317
 

Вышеизложенное изменяет формат значений на месте. Однако я имею в виду, что если новые данные будут поступать в старом формате «XX.XXX», то эти значения придется преобразовать.

Второй вариант преобразовать в целочисленный столбец:

 truncate string_conv ;
insert into string_conv values (1, 83.420), (2, 43.317), (3, 149.317 );
alter table string_conv alter COLUMN num_column type integer using translate(num_column, '.', '')::int;
select * from string_conv ;
 id | num_column 
---- ------------
  1 |      83420
  2 |      43317
  3 |     149317
d string_conv 
              Table "public.string_conv"
   Column   |  Type   | Collation | Nullable | Default 
------------ --------- ----------- ---------- ---------
 id         | integer |           |          | 
 num_column | integer |           |          | 


 

Этот параметр изменяет формат значений и тип столбца, в котором они хранятся. Проблема в том, что с этого момента новые значения должны были бы быть совместимы с новым типом. Это означало бы изменение входных данных с «XX.XXX» на «XXXXX».