如何计算数据库上的水平值？ [英] how to count horizontal values on a database?

问题描述

假设我有一个水平结构像这样的数据库：

assuming that I have a db that have an horizontal structure like this:

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

the informations are the data fields and I would like to count all the times that a certain discriminant, such as "is more than 0"

我认为它是循环锻造所有字段和计数，或 COUNT（）每个 DATA 字段，因此 SUM（）那些 7 查询...任何人都有其他想法？

the way I thought it is loop trought all the fields, and count, or COUNT() each DATA field, so SUM() those 7 queries... anyone has another idea?

在这种情况下的结果，所以 0将= 7

in this case the result, so "count every DATA field with a value over 0" would be = 7

推荐答案

没有内置语法，动态地，即没有明确地命名它们的一组列。如果你想要动态性，你需要查询元数据来获取所需的列名，然后动态构建最终查询。

There's no built-in syntax that would allow you to refer to a set of columns dynamically, i.e. without explicitly naming them. If you want dynamicity, you'll need to query metadata to grab the required column names, then build the final query dynamically.

但在此之前，你仍然需要有一个想法动态查询应该如何执行作业本身。因此，您首先需要在有限的列集合上解决问题。

But before that you'd still need to have an idea how exactly the dynamic query should go about performing the job itself. So, you'll first to need to solve the problem on a finite column set.

有多种方法可以解决这个问题。 @bluefeet建议的方法可能是更清晰，更低效的方法之一。您可以尝试至少两个备选项：

There's more than one way to solve this problem. The method suggested by @bluefeet is probably one of the clearer as well as less efficient ones. You could try at least two alternatives:

1. 使用条件聚合单独计算每列，并将所有结果合计在一个表达式中：

1. Count every column separately using conditional aggregation and add up all the results in one expression:

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
;

（ OR NULL a href =http://stackoverflow.com/questions/5011239/why-do-i-need-or-null-in-mysql-when-counting-rows-with-a-conditiontitle =为什么我需要OR NULL在MySQL中计数行条件>这里。）

(The OR NULL trick is explained here.)

不能转移 DATA 列使用到虚拟表的交叉连接，然后将条件应用于非转义列：

Unpivot the DATA columns using a cross join to a virtual table, then apply the condition to the unpivoted column:

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的建议类似，它不使用任何UNION 。）

(In a way, this is similar to @bluefeet's suggestion, it just doesn't employ any UNIONs.)

这篇关于如何计算数据库上的水平值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！