SQL 从数据库中的列值生成直方图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/485409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generating a histogram from column values in a database
提问by Thorsten79
Let's say I have a database column 'grade' like this:
假设我有一个像这样的数据库列“等级”：
|grade|
| 1|
| 2|
| 1|
| 3|
| 4|
| 5|
Is there a non-trivial way in SQL to generate a histogram like this?
SQL 中是否有一种非平凡的方法来生成这样的直方图？
|2,1,1,1,1,0|
where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all.
其中 2 表示等级 1 出现两次，1s 表示等级 {2..5} 出现一次，0 表示等级 6 根本不发生。
I don't mind if the histogram is one row per count.
我不介意直方图是否为每个计数一行。
If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.
如果这很重要，数据库是由 perl CGI 通过 unixODBC/FreeTDS 访问的 SQL Server。
EDIT:Thanks for your quick replies! It is okay if non-existing values (like grade 6 in the example above) do not occur as long as I can make out which histogram value belongs to which grade.
编辑：感谢您的快速回复！如果不存在的值（如上例中的 6 级）不出现也没关系，只要我能确定哪个直方图值属于哪个级别即可。
回答by Ilya Volodin
SELECT COUNT(grade) FROM table GROUP BY grade ORDER BY grade
Haven't verified it, but it should work.It will not, however, show count for 6s grade, since it's not present in the table at all...
尚未验证它，但它应该可以工作。但是，它不会显示 6s 等级的计数，因为它根本不存在于表中......
回答by KyleMit
If there are a lot of data points, you can also group ranges togetherlike this:
如果有很多数据点，您还可以像这样将范围组合在一起：
SELECT FLOOR(grade/5.00)*5 As Grade,
COUNT(*) AS [Grade Count]
FROM TableName
GROUP BY FLOOR(Grade/5.00)*5
ORDER BY 1
Additionally, if you wanted to label the full range, you can get the floor and ceiling ahead of time with a CTE.
此外，如果您想标记整个范围，您可以使用 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
Note: In some SQL engines, you can GROUP BYan Ordinal Column Index, but with MS SQL, if you want it in the SELECTstatement, you're going to need to group by it also, hence copying the Range into the Group Expression as well.
注意：在某些 SQL 引擎中，您可以GROUP BY使用序数列索引，但是对于 MS SQL，如果您希望在SELECT语句中使用它，您还需要按它进行分组，因此也将范围复制到组表达式中。
Option 2: You could use case statements to selectively count values into arbitrary bins and then unpivot themto get a row by row count of included values
选项 2：您可以使用case 语句有选择地将值计数到任意 bin 中，然后对它们进行逆透视以逐行获取包含值的计数
回答by cjk
Use a temp table to get your missing values:
使用临时表获取缺失值：
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
回答by devon
According to Shlomo Priymak's article How to Quickly Create a Histogram in MySQL, you can use the following query:
根据 Shlomo Priymak 的文章How to Quickly Create a Histogram in MySQL，您可以使用以下查询：
SELECT grade,
COUNT(*) AS 'Count',
RPAD('', COUNT(*), '*') AS 'Bar'
FROM grades
GROUP BY grade
Which will produce the following table:
这将产生下表：
grade Count Bar
1 2 **
2 1 *
3 1 *
4 1 *
5 1 *
回答by MatBailie
Gamecat's use of DISTINCT seems a little odd to me, will have to try it out when I'm back in the office...
Gamecat 对 DISTINCT 的使用对我来说似乎有点奇怪，当我回到办公室时必须尝试一下......
The way I would do it is similar though...
虽然我这样做的方式是相似的......
SELECT
[table].grade AS [grade],
COUNT(*) AS [occurances]
FROM
[table]
GROUP BY
[table].grade
ORDER BY
[table].grade
To overcome the lack of data where there are 0 occurances, you can LEFT JOIN on to a table containing all valid grades. The COUNT(*) will count NULLS, but COUNT(grade) won't count the NULLS.
为了克服出现 0 次时缺少数据的问题，您可以 LEFT JOIN 到包含所有有效成绩的表。COUNT(*) 将计算 NULLS，但 COUNT(grade) 不会计算 NULLS。
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
回答by Seibar
select Grade, count(Grade)
from MyTable
group by Grade
回答by Sylvain Ayrault
I am building on what Ilya Volodin did above, that should allow you to select a range of grade you want to group together in your result:
我建立在 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;
