代码之家 › 专栏 › 技术社区 › Benjamin Oakes

在SQLite3中计算多列平均值

average survey sqlite3 sql

Benjamin Oakes · 技术社区 · 16 年前

我需要平均一些值柱 -明智的时尚(如果我做的是列平均,我可以用 avg() ). 我的具体应用要求我在求平均值时忽略空值。这是一个非常简单的逻辑,但在SQL中似乎非常困难。有没有一种优雅的计算方法?

我正在使用SQLite3,不管它值多少钱。

细节

我有一张有调查的桌子:

| q1 | q2    | q3    | ... | q144 |
|----|-------|-------|-----|------|
| 1  | 3     | 7     | ... | 2    |
| 4  | 2     | NULL  | ... | 1    |
| 5  | NULL  | 2     | ... | 3    |

(这些只是一些示例值和简单的列名。有效值为1到7和NULL。)

我需要这样计算一些平均值:

q7 + q33 + q38 + q40 + ... + q119 / 11 as domain_score_1
q10 + q11 + q34 + q35 + ... + q140 / 13 as domain_score_2
...
q2 + q5 + q13 + q25 + ... + q122 / 12 as domain_score_14

domain_score_1 (共11项),我需要做:

Input:  3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1

(3 + 5 + 7 + 2 + 3 + 1 + 5 + 1) / (11 - 3)
27 / 8
3.375

我考虑的一个简单算法是:

输入:

3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1

通过转换值获取非零的数目>0到1和总和:

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1
1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 1
8

把这两个数字分开

27 / 8
3.375

但这似乎比这需要更多的编程。有没有我不知道的优雅的方法?

除非我误解了什么, 平均值() 这不管用。我想做的示例:

select avg(q7, q33, q38, ..., q119) from survey;

输出:

SQL error near line 3: wrong number of arguments to function avg()

5 回复 | 直到 16 年前

Shog9 16 年前

在标准SQL中

SELECT 
(SUM(q7)+SUM(q33)+SUM(q38)+SUM(q40)+..+SUM(q119))/
(COUNT(q7)+COUNT(q33)+COUNT(q38)+COUNT(q40)+..+COUNT(q119)) AS domain_score1 
FROM survey

如果null和COUNT不计算null,那么SUM将合并为0。

编辑:选中 http://www.sqlite.org/lang_aggfunc.html SQLite符合;如果sum()要溢出,可以改用total()。

Welbog 16 年前

AVG 已忽略空值并执行所需操作:

函数的作用是:返回一个组中所有非空X的平均值。看起来不像数字的字符串和BLOB值被解释为0。avg()的结果总是一个浮点值,只要至少有一个非空输入,即使所有输入都是整数。当且仅当没有非空输入时,avg()的结果为空。

从 http://www.sqlite.org/lang_aggfunc.html

处理列,而不是行。所以如果你打开桌子你可以用 平均值 没有你面临的问题。让我们看一个小例子:

你有一张桌子,看起来像这样:

ID  | q1  | q2  | q3
----------------------
1   | 1   | 2   | NULL
2   | NULL| 2   | 56

你想把q1和q2平均在一起,因为它们在同一个域中,但是它们是分开的列,所以你不能。但如果你把桌子改成这样:

ID  | question | value
-----------------------
1   | 1        | 1
1   | 2        | 2
1   | 3        | NULL
2   | 1        | NULL
2   | 2        | 2
2   | 3        | 56

然后你可以很容易地取两个问题的平均值:

SELECT AVG(value)
FROM Table
WHERE question IN (1,2)

如果您想要每个ID的平均值而不是全局平均值,则可以按ID分组:

SELECT ID, AVG(value)
FROM Table
WHERE question IN (1,2)
GROUP BY ID

Marcus Adams 16 年前

这将是一个可怕的查询,但您可以这样做:

SELECT AVG(q) FROM
((SELECT q7 AS q FROM survey) UNION ALL
(SELECT q33 FROM survey) UNION ALL
(SELECT q38 FROM survey) UNION ALL
...
(SELECT q119 FROM survey))

这会将列转换为行并使用 AVG() 功能。

SELECT AVG(q) FROM
((SELECT q7 AS q FROM survey WHERE survey_id = 1) UNION ALL
(SELECT q33 FROM survey WHERE survey_id = 1) UNION ALL
(SELECT q38 FROM survey WHERE survey_id = 1) UNION ALL
...
(SELECT q119 FROM survey WHERE survey_id = 1))

如果您将q列规范化到它们自己的表中,每行一个问题,并且引用返回到survey中,您将有更轻松的时间。调查和问题之间有一对多的关系。

Babar 16 年前

SurveyTable(SurveyId, ...)
SurveyRatings(SurveyId, QuestionId, Rating)

之后,您可以像

SELECT avg(Rating) WHERE SurveyId=?

OMG Ponies 16 年前

用途:

SELECT AVG(x.answer)
  FROM (SELECT s.q7 AS answer
          FROM SURVEY s
        UNION ALL
        SELECT s.q33
          FROM SURVEY s
        UNION ALL    
       SELECT s.q38
         FROM SURVEY s
       ...
       UNION ALL
       SELECT s.q119
         FROM SURVEY s) x

不要使用 UNION