根据数据库中的列值生成直方图 [英] Generating a histogram from column values in a database

问题描述

假设我有一个数据库列"grade"，如下所示:

Let's say I have a database column 'grade' like this:

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

SQL中是否有非平凡的方式来生成像这样的直方图?

Is there a non-trivial way in SQL to generate a histogram like this?

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

其中2表示1级出现两次，1s表示{2..5}出现一次，0表示完全不出现6级.

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.

我不介意直方图是否每计数一行.

I don't mind if the histogram is one row per count.

如果这很重要，那么perl CGI将通过unixODBC/FreeTDS访问该数据库为SQL Server.

If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.

编辑:感谢您的快速回复！只要我能确定哪个直方图值属于哪个等级，就可以不出现不存在的值(如上例中的6级).

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.

推荐答案

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

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

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...

这篇关于根据数据库中的列值生成直方图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！