sql计算平均数、众数、中位数

CREATE TABLE s_grade (
  sno INTEGER PRIMARY KEY,
  sname VARCHAR(50) NOT NULL,
  sgrade INTEGER NOT NULL
);


INSERT INTO student (sno, sname, sgrade) VALUES
  (1001, '张三', 85),
  (1002, '李四', 73),
  (1003, '王五', 92),
  (1004, '赵六', 68),
  (1005, '钱七', 77),
  (1006, '孙八', 77),
  (1007, '周九', 77),
  (1008, '吴十', 81),
  (1009, '郑十一', 90),
  (1010, '朱十二', 62);

sno	sname	sgrade
1001	张三	85
1002	李四	73
1003	王五	92
1004	赵六	68
1005	钱七	77
1006	孙八	77
1007	周九	77
1008	吴十	81
1009	郑十一	90
1010	朱十二	62

接下来我们以上面的成绩表来举例，分享计算sgrade列的平均数、中位数、众数的sql

平均数：

SELECT AVG(sgrade) AS avg_sgrade FROM s_grade;

SELECT (sum(sgrade) - max(sgrade) - min(sgrade))/(count(*) - 2)
FROM s_grade;--去掉一个最高分，去掉一个最低分

中位数：

SELECT AVG(sgrade) 
FROM (
  SELECT sgrade, ROW_NUMBER() OVER (ORDER BY sgrade) AS rownum, 
COUNT(*) OVER () AS total_count
  FROM s_grade
) AS s
WHERE rownum= CEIL(total_count / 2.0) OR rownum = FLOOR(total_count / 2.0) + 1;

SELECT avg(sgrade)
FROM（
SELECT sgrade,
ROW_ NUMBER() OVER (PARTITION BY NULL ORDER BY sgrade) rownum,
count(*) OVER () total_count
FROM s_grade）s
WHERE rownum BETWEEN total/2 AND total/2+1；

众数：

SELECT sgrade 
FROM s_grade
GROUP BY sgrade
HAVING COUNT(*) = (
  SELECT MAX(c) FROM (
    SELECT COUNT(*) AS c
    FROM s_grade
    GROUP BY sgrade
  ) AS counts
);