Блог

Главная - Вопросы по программированию - Есть ли способ выполнять агрегатные функции для числовых значений в поле varchar с начальными $ или конечными%?

Есть ли способ выполнять агрегатные функции для числовых значений в поле varchar с начальными $ или конечными%?

#mysql

#mysql

Вопрос:

У меня есть набор данных с записями, поступающими из другой системы, над которой я не имею никакого контроля. В одном поле есть значения типа «200 долларов» и «20%». Я хочу проанализировать числовые данные с помощью некоторых агрегатных функций (среднее значение, медиана и т.д.), Как мне удалить начальные $ или конечные% для целей вычисления, фактически не удаляя символы из базы данных?

Комментарии:

1. Да, но вам придется выполнить некоторые манипуляции со строками, чтобы удалить весь этот пух, которого в любом случае не должно было быть в этих столбцах

2. Я не могу с вами не согласиться. Я просто надеялся, что есть способ обрабатывать данные по мере их получения.

Ответ №1:

Вы можете использовать функцию REPLACE() в mysql для замены ваших значений пустой строкой. И вы можете использовать CAST (), если требуется какое-либо приведение.

 REPLACE('$', REPLACE('%', str, ''), '') // check both '$', '%' occurrences and replace
CAST(str AS UNSIGNED)

Если вам нужно выполнить определенную манипуляцию над ‘$’ или ‘%’, то вы должны сначала проверить метку ‘$’ или ‘%’ в вашем значении с помощью LOCATE(),

 LOCATE('$', str) > 0 // if '$' in the str
LOCATE('%', str) > 0 // if '%' in the str

Ответ №2:

Да, это возможно. Обычно есть 2 способа заставить это работать:

  1. Перед агрегированием вы обрабатываете строку с помощью REPLACE(), чтобы вырезать нечисловые части. Это работает лучше всего, если у вас есть столбец с фиксированными нечисловыми частями, например, когда вы суммируете проценты или что-то подобное. Затем, когда у вас будет «чистая» строка, вы можете преобразовать ее в целое число по своему усмотрению. Рассмотрим:

    ВЫБЕРИТЕ ПРИВЕДЕНИЕ (ЗАМЕНИТЬ(‘%’, ‘25%’, «) КАК БЕЗЗНАКОВЫЙ) КАК число

Восстановив числа, вы можете затем сделать что-то вроде

 SELECT  
AVG(CAST(REPLACE('%', myPct, '') AS UNSIGNED))   '%' AS averagePct
FROM myTable

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

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