Как посчитать горизонтальные значения в базе данных?

Question

Предполагая, что у меня есть БД с горизонтальной структурой, как это:

ID | NAME | DATA1 | DATA2 | DATA3 | DATA4 | DATA5 | DATA6 | DATA7
 1 |  mmm |   0   |   1   |   0   |   3   |   5   |   1   |   0
 2 |  bbb |   0   |   0   |   0   |   1   |   0   |   1   |   1

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

так, как я думал, это цикл, охватывающий все поля, и считать, или COUNT() каждый DATA поле, так SUM() те 7 запросы... у кого-нибудь есть другая идея?

в этом случае результат, так "count every DATA field with a value over 0" будет = 7

Answer 1

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

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

Есть несколько способов решить эту проблему. Метод, предложенный @bluefeet, вероятно, является одним из более простых и менее эффективных. Вы можете попробовать как минимум две альтернативы:

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

   SELECT
     COUNT(DATA1 > 0 OR NULL) +
     COUNT(DATA2 > 0 OR NULL) +
     COUNT(DATA3 > 0 OR NULL) +
     COUNT(DATA4 > 0 OR NULL) +
     COUNT(DATA5 > 0 OR NULL) +
     COUNT(DATA6 > 0 OR NULL) +
     COUNT(DATA7 > 0 OR NULL) AS TOTAL
   FROM yourtable
   ;
   

   (The OR NULL Трюк объясняется здесь.)

2. Отключить DATA столбцы, использующие перекрестное соединение с виртуальной таблицей, затем примените условие к столбцу без поворота:

   SELECT
     COUNT(*) AS TOTAL
   FROM (
     SELECT
       CASE s.col
         WHEN 'DATA1' THEN DATA1
         WHEN 'DATA2' THEN DATA2
         WHEN 'DATA3' THEN DATA3
         WHEN 'DATA4' THEN DATA4
         WHEN 'DATA5' THEN DATA5
         WHEN 'DATA6' THEN DATA6
         WHEN 'DATA7' THEN DATA7
       END AS DATA
     FROM yourtable
     CROSS JOIN (
       SELECT 'DATA1' AS col
       UNION ALL SELECT 'DATA2'
       UNION ALL SELECT 'DATA3'
       UNION ALL SELECT 'DATA4'
       UNION ALL SELECT 'DATA5'
       UNION ALL SELECT 'DATA6'
       UNION ALL SELECT 'DATA7'
     ) s
   ) s
   WHERE DATA > 0
   ;
   

   (В некотором смысле, это похоже на предложение @ bluefeet, оно просто не использует никаких СОЮЗОВ.)

Answer 2

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

select count(*) total
from
(
  select id, name, 'data1' col, data1 as value
  from yourtable
  union all
  select id, name, 'data2' col, data2 as value
  from yourtable
  union all
  select id, name, 'data3' col, data3 as value
  from yourtable
  union all
  select id, name, 'data4' col, data4 as value
  from yourtable
  union all
  select id, name, 'data5' col, data5 as value
  from yourtable
  union all
  select id, name, 'data6' col, data6 as value
  from yourtable
  union all
  select id, name, 'data7' col, data7 as value
  from yourtable
) src
where value > 0

Смотрите SQL Fiddle с демо