MySQL分段统计函数INTERVAL和ELT

需求

期末考试成绩出来了,结果需要分为不及格(0-59),合格(60-84),优秀(80+)三个段,老师需要统计每个段成绩的分布情况。

分数表score结构如下:

id int
user_id int
score int

使用INTERVAL函数对学生成绩分段

SELECT user_id,score,INTERVAL(score,60,85) as step from score

INTERVAL函数会根据score返回0(少于60),1(60-74),2(85以及以上)

INTERVAL返回的结果进行分组:

SELECT INTERVAL(score,60,85),COUNT(*) AS num as step from score GROUP BY INTERVAL(score,60,85)

得到:

0, 5
1, 80
2, 15

使用ELT函数为阶段命名

ETL方法会建立重命名,将1,2,3分别转化为不及格,合格,优秀,但是etl不能是从0开始的,所以我们可以在INTERVAL分组时加一个0,将0分以下的划分为0,0-59的划为1,依次类推

SELECT INTERVAL(score,0,60,85),COUNT(*) AS num as step from score GROUP BY INTERVAL(score,60,85)

得到:

1, 5
2, 80
3, 15

最终SQL:

SELECT ELT(INTERVAL(score,0,60,85),"不及格","合格","优秀"),COUNT(*) AS num as step from score GROUP BY ELT(INTERVAL(score,0,60,85),"不及格","合格","优秀")

得到:

不及格, 5
合格, 80
优秀, 15

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

*

code