MySQL: функция SUM применяется к формуле, содержащейся в поле, выбранном другим запросом

Мне нужно выполнить select SUM(), где это формула, содержащаяся в поле, выбранном другим запросом. Пример:

table_A (поле «формула» содержит в каждой ячейке арифметическое выражение с участием столбцов из таблицы B):

+------------+--------------+------------+
| Product_id | related_prod |  formula   |
+------------+--------------+------------+
| U1         | C2           | col2-col1  |
| U2         | C3           | col3-col2  |
| U3         | C4           | col3-col1  |
+------------+--------------+------------+

таблица_B:

+------------+---------+------------+----------+------+------+------+
| Product_id | year_id | company_id | month_id | col1 | col2 | col3 |
+------------+---------+------------+----------+------+------+------+
| C2         |    2017 |          1 |        2 |  100 |  200 |  300 |
| C3         |    2017 |          1 |        2 |  400 |  500 |  600 |
| C4         |    2017 |          1 |        2 |  700 |  800 |  900 |
+------------+---------+------------+----------+------+------+------+

Затем я делаю следующий запрос:

SELECT 
SUM(totals.relaz) as final_sum, 
totals.relaz as 'col', 
totals.prod as 'prod', 
totals.cons as 'cons', 
m.company_id, m.month_id, m.year_id, FROM `table_B` m, 
    ( SELECT formula as relaz, 
     related_prod as prod,
     p.product_id as cons FROM table_A p  ) 
     AS totals 
WHERE m.product_id=totals.prod 
GROUP BY m.company_id, m.year_id, m.month_id, m.product_id, totals.cons

После выбора я ожидаю, что, учитывая, например, единственный продукт «U1», соответствующая строка будет

+-----------+-----------+------+------+------------+----------+---------+
| final_sum |    col    | prod | cons | company_id | month_id | year_id |
+-----------+-----------+------+------+------------+----------+---------+
|       100 | col2-col1 | C2   | U1   |          1 |        2 |    2017 |
+-----------+-----------+------+------+------------+----------+---------+

Вместо этого я получаю

+-----------+-----------+------+------+------------+----------+---------+
| final_sum |    col    | prod | cons | company_id | month_id | year_id |
+-----------+-----------+------+------+------------+----------+---------+
|         0 | col2-col1 | C2   | U1   |          1 |        2 |    2017 |
+-----------+-----------+------+------+------------+----------+---------+

то есть поле final_sum всегда установлено в 0, несмотря на то, что поле 'col' содержит правильное уравнение.

Что я делаю неправильно?

Заранее спасибо Алекс


mysql mariadb
person Alessio Rocchi    schedule 31.05.2018    source источник
comment
Такого рода проблемы (как я думаю, вы уже догадались) являются симптомами плохого дизайна.   -  person Strawberry    schedule 31.05.2018

Ответы (2)


arrow_upward
0
arrow_downward

Вы пытаетесь получить сумму из строкового столбца (table_A.formula). Результатом будет 0. MySQL/MariaDB не будет пытаться преобразовывать строки в ссылки на столбцы и вычислять формулу в строке.

Другое дело, что вы должны перечислить все столбцы, не входящие в агрегатную функцию, в GROUP BY.

Чтобы получить желаемый результат, используйте:

SELECT 
  SUM(CASE 
      WHEN a.formula = 'col2-col1' THEN b.col2-b.col1 
      WHEN a.formula = 'col3-col1' THEN b.col3-b.col1 
      WHEN a.formula = 'col3-col2' THEN b.col3-b.col2
     END
     ) AS final_sum, 
  a.formula as 'col', 
  a.related_prod as 'prod', 
  a.Product_id as 'cons', 
  b.company_id, 
  b.month_id, 
  b.year_id
FROM table_B b
  JOIN table_A a on a.related_prod=b.Product_id 
GROUP BY a.formula, a.related_prod, a.Product_id, b.company_id, b.month_id, b.year_id
person slaakso    schedule 31.05.2018
comment
Прежде всего, спасибо за ваш ответ. Мне было интересно что-то вроде этого... Проблема в том, что содержимое поля формулы может постоянно меняться, поэтому его невозможно обернуть внутри СУММ(СЛУЧАЙ()). Итак... нет никакого способа передать такое значение функции SUM без интерполяции MySQL? - person Alessio Rocchi; 31.05.2018
comment
Для этого потребуется логика уровня приложения, анализирующая столбцы из текста и вычисляющая формулу. SQL не предназначен для такого использования. Сколько комбинаций у вас может быть. У вас есть более 3 столбцов данных, если да, вы можете пересмотреть структуру базы данных. - person slaakso; 31.05.2018
comment
Приложение уже делает это. Я пытался найти способ выполнить все через SQL. - person Alessio Rocchi; 31.05.2018
comment
В других языках есть функция eval(). В SQL такого нет. - person Rick James; 01.06.2018

arrow_upward
0
arrow_downward

может создать хранимую процедуру, которая извлекает строку col2-col1 и вставляет ее (используя CONCAT) в строку, а затем PREPAREs и EXECUTEs строку SQL.

То есть динамически создавать SQL, возможно, как в ответе @slaakso.

Это было бы грязно.

Мне нужно что-то вроде этого; Я решил сделать eval() на PHP, который был языком клиента. Я использую его для оценки VARIABLES и GLOBAL STATUS. Пример: Table_open_cache_misses / Uptime дает "промахи в секунду", которые, если они высокие, указывают на необходимость увеличения параметра table_open_cache.

person Rick James    schedule 01.06.2018