代码之家 › 专栏 › 技术社区 › Thorsten79

从数据库中的列值生成直方图

histogram sql-server sql

Thorsten79 · 技术社区 · 16 年前

假设我有一个数据库列“grade”,如下所示:

|grade|
|    1|
|    2|
|    1|
|    3|
|    4|
|    5|

SQL中有没有一种非常简单的方法可以生成这样的直方图?

|2,1,1,1,1,0|

我不介意柱状图是每计数一行。

如果这很重要,那么数据库就是由perl CGI通过unixODBC/FreeTDS访问的SQL Server。

编辑: 谢谢你的快速回复!如果不存在值(如上面示例中的6级),只要我能确定哪个直方图值属于哪个级别,就可以了。

7 回复 | 直到 16 年前

Ilya Volodin 16 年前

SELECT COUNT(grade) FROM table GROUP BY grade ORDER BY grade

还没有验证它,但它应该可以工作。但是,它不会显示6s等级的计数,因为它根本不在表中。。。

KyleMit Steven Vachon 6 年前

group ranges together 这样地:

SELECT FLOOR(grade/5.00)*5 As Grade, 
       COUNT(*) AS [Grade Count]
FROM TableName
GROUP BY FLOOR(Grade/5.00)*5
ORDER BY 1

此外,如果您想标记全系列,您可以使用CTE提前获得地板和天花板。

With GradeRanges As (
  SELECT FLOOR(Score/5.00)*5     As GradeFloor, 
         FLOOR(Score/5.00)*5 + 4 As GradeCeiling
  FROM TableName
)
SELECT GradeFloor,
       CONCAT(GradeFloor, ' to ', GradeCeiling) AS GradeRange,
       COUNT(*) AS [Grade Count]
FROM GradeRanges
GROUP BY GradeFloor, CONCAT(GradeFloor, ' to ', GradeCeiling)
ORDER BY GradeFloor

笔记 :在某些SQL引擎中,您可以 GROUP BY 顺序列索引,但如果希望在 SELECT 语句,您还需要按它分组,因此也需要将范围复制到组表达式中。

选择2 case statements to selectively count values into arbitrary bins and then unpivot them 获取包含值的逐行计数的步骤

cjk 16 年前

使用临时表获取缺少的值:

CREATE TABLE #tmp(num int)
DECLARE @num int
SET @num = 0
WHILE @num < 10
BEGIN
  INSERT #tmp @num
  SET @num = @num + 1
END


SELECT t.num as [Grade], count(g.Grade) FROM gradeTable g
RIGHT JOIN #tmp t on g.Grade = t.num
GROUP by t.num
ORDER BY 1

MatBailie 16 年前

Gamecat对DISTINCT的使用对我来说似乎有点奇怪,我回到办公室后必须尝试一下。。。

我会做的方式是相似的,虽然。。。

SELECT
    [table].grade        AS [grade],
    COUNT(*)             AS [occurances]
FROM
    [table]
GROUP BY
    [table].grade
ORDER BY
    [table].grade

为了克服0次发生时数据不足的问题,您可以将JOIN保留在包含所有有效等级的表上。COUNT(*)将计算空值,但COUNT(grade)不会计算空值。

DECLARE @grades TABLE (
   val INT
   )  

INSERT INTO @grades VALUES (1)  
INSERT INTO @grades VALUES (2)  
INSERT INTO @grades VALUES (3)  
INSERT INTO @grades VALUES (4)  
INSERT INTO @grades VALUES (5)  
INSERT INTO @grades VALUES (6)  

SELECT
    [grades].val         AS [grade],
    COUNT([table].grade) AS [occurances]
FROM
    @grades   AS [grades]
LEFT JOIN
    [table]
        ON [table].grade = [grades].val
GROUP BY
    [grades].val
ORDER BY
    [grades].val

Devon 5 年前

根据Shlomo Priymak的文章 How to Quickly Create a Histogram in MySQL

SELECT grade, 
       COUNT(*) AS 'Count',
       RPAD('', COUNT(*), '*') AS 'Bar' 
FROM grades 
GROUP BY grade

grade   Count   Bar
1       2       **
2       1       *
3       1       *
4       1       *
5       1       *

Seibar 16 年前

select Grade, count(Grade)
from MyTable
group by Grade

Sylvain Ayrault 9 年前

我以Ilya Volodin在上面所做的为基础,这应该允许你选择一系列你想在结果中分组的分数:

DECLARE @cnt INT = 0;

WHILE @cnt < 100 -- Set max value
BEGIN
SELECT @cnt,COUNT(fe) FROM dbo.GEODATA_CB where fe >= @cnt-0.999 and fe <= @cnt+0.999 -- set tolerance
SET @cnt = @cnt + 1; -- set step
END;