数据库求分组后求，平均成数，上四分位数，下四分位数，中位数

1. 新建成绩表，包含字段（序号，班级id，学科名称，成绩，学生id）
2. 查询班级id（CLASSID ）为9的各个学科名称的平均成数，上四分位数，下四分位数，中位数

"""先创建一个数表SUBJECT，插入数据"""
import pymysql,random
dbs = pymysql.connect(host='localhost', user='root', password='root', db='demo', port=3306)
db = dbs.cursor()
# 创建成绩表 序号，班级id，学科名称，成绩，学生id
db.execute("""
        CREATE TABLE SUBJECT(
        ID INT(3) PRIMARY KEY,
        CLASSID INT(10),
        SUBJECTNAME VARCHAR(20),
        STATYHOUR float(4),
        GARDEID int(10))
        """
        )
# 插入数据
a = 0
CLASSIDS = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
SUBJECTNAMEs = ['python','java','c','c++','mysql','db','gauss']
while a<=1000: # 插入1000条数据
    a += 1
    CLASSID = CLASSIDS[random.randint(0,len(CLASSIDS)-1)]
    SUBJECTNAME = SUBJECTNAMEs[random.randint(0,len(SUBJECTNAMEs)-1)]
    STATYHOUR = round(random.uniform(1,150),2)
    GARDEID = random.randint(1,100)
    sql = "insert into SUBJECT(ID,CLASSID,SUBJECTNAME,STATYHOUR,GARDEID) values ({0},{1},'{2}',{3},{4});".format(a,CLASSID, SUBJECTNAME, STATYHOUR, GARDEID)
    db.execute(sql)
    dbs.commit()
    print(a)
db.close()
dbs.close()

先模拟一组数据库数据分析上4：excel表格使用函数 =QUARTILE(B$3:B4,1)

分析发现：
当行号 N==4的倍数+1的时候，得出的上四分位数恰好为当前行号，理解为：行号R = (N-1)/4+1 ，校验数据，若N=5得出R=2， 当N=9得出R=3， N=1得出R=1

若N非4的倍数+1，这种情况下计算套用推理公式（R = (N-1)/4*1+1）：
若N=6，得出R=2.25 ，相近行号对应2,3 ；
套用数据1，行号2,3对应的数据为2,3，
上四分位数为1,25，而当前位置和差为1，
推测出：
上四分位数==（模拟数据（最大值）-模拟数（最小值））×（R-最小行号）+模拟数最小值

1. 验证
   若N=6 得出R=2.25 — 模拟数据3对上四分位数 = （33-12）×（2.25-2）+12=17.25
   若N=7 得出R=2.5 — 模拟数据3对上四分位数 = （33-12）×（2.5-2）+12=22.5
   若N=8 得出R=2.75 — 模拟数据3对上四分位数 = （33-12）×（2.75-2）+12=27.25
   若N=10 得出R = 3.25 — 模拟数据1对上四分位数== … == 3.25
   若N=11 得出R = 3.5 — 模拟数据1对上四分位数== … == 3.5
   若N=12 得出R = 3.75 — 模拟数据1对上四分位数== … == 3.75
   … 和表格数据校验该方程成立

第一次写上四分位数sql：

with 
t1 as(	select * 
	from SUBJECT
	where CLASSID = 9
	),
t2 as (	select 
			SUBJECTNAME as b,
			round((case
				count(STATYHOUR) when '1' then max(STATYHOUR)
			else
			(
			(max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*1+1)-min(r))+min(STATYHOUR)
			) end ),2)as lowerQuartile
		from(	select 
				*
				from (	select *,
							row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
							count(*) over(partition by SUBJECTNAME) as n
						from t1
					) as a1
				where r >=((n-1)/4*1+1-0.75) and r<=((n-1)/4*1+1+0.75) 
		)as t2
	group by SUBJECTNAME)
select * from t2;


/*
c	29.8
c++	47.72
db	29.18
gauss	13.55
java	58.96
python	59.52
*/

--优化后sql，去除case ... else ...验证结果也正确，这样可以少两行代码
with 
t1 as(	select * from 
		SUBJECT
	where CLASSID = 9
	),
t2 as (	select 
			SUBJECTNAME as b,
			round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*1+1)-min(r))+min(STATYHOUR)),2)as lowerQuartile
		from(	select 
				*
				from (	select *,
							row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
							count(*) over(partition by SUBJECTNAME) as n
						from t1
					) as a1
				where r >=((n-1)/4*1+1-0.75) and r<=((n-1)/4*1+1+0.75) 
		)as a2
	group by SUBJECTNAME)
select * from t2;


/*
c	29.8
c++	47.72
db	29.18
gauss	13.55
java	58.96
python	59.52
*/

分析尝试R = (N-1)/41+1==上四分，那么中位数换位2，下四分换位*3，尝试SQL

with 
t1 as(	select * from SUBJECT
	where CLASSID = 9
	),
t2 as (	select 
			SUBJECTNAME as b,
			round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*1+1)-min(r))+min(STATYHOUR)),2)as lowerQuartile
		from(	select 
				*
				from (	select *,
							row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
							count(*) over(partition by SUBJECTNAME) as n
						from t1
					) as a1
				where r >=((n-1)/4*1+1-0.75) and r<=((n-1)/4*1+1+0.75) 
		)as a2
	group by SUBJECTNAME),
t3 as (	select 
			SUBJECTNAME as b,
			round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*2+1)-min(r))+min(STATYHOUR)),2)as median
		from(	select 
				*
				from (	select *,
							row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
							count(*) over(partition by SUBJECTNAME) as n
						from t1
					) as a1
				where (r >=((n-1)/4*2+1-0.75) and r<=((n-1)/4*2+1+0.75))
		)as a2
	group by SUBJECTNAME),
t4 as (	select 
			SUBJECTNAME as b,
			round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*3+1)-min(r))+min(STATYHOUR)),2)as upperQuartitle
		from(	select 
				*
				from (	select *,
							row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
							count(*) over(partition by SUBJECTNAME) as n
						from t1
					) as a1
				where (r >=((n-1)/4*3+1-0.75) and r<=((n-1)/4*3+1+0.75))
		)as a2
	group by SUBJECTNAME)
select t2.b as SUBJECTNAME,lowerQuartile,median,upperQuartitle
from t2,t3,t4 where t2.b=t3.b and t3.b=t4.b;


/*
c	29.8	50.36	107.87
c++	47.72	73.86	112.25
db	29.18	60.29	137.41
gauss	13.55	29.61	92.71
java	58.96	107.58	127.41
python	59.52	71.44	112.83
*/

SQL执行成功啦，可是，返回的结果是否正确？
通过结果 推算公式 编写sql以后继续验证该结果是否正确；
这种情况下第一想到的是excel表格，突然想起来，我是一名测试，那么，是否可以尝试python的库呢？

# 创建一个数组，查是否可以找到对应的分位数`
# pip install numpy 安装库
import numpy as np
number = [1,2,3,4,5]
z = np.percentile(number,(25,50,75), interpolation='midpoint')
print(z)
print(type(z))


>> [2. 3. 4.]
>> <class 'numpy.ndarray'>

python实现校验：

import numpy as np
import pymysql

calculated_value={} #存放数据库查询的分位数
verify_calculated_value={}  #存放数据库查询的分位数
class MySqlS():
    """创建一个类，用来校验数据库获取的上四分数，中四分数，下四分数是否一致"""
    def __init__(self):
        self.dbs = pymysql.connect(host='localhost', user='root', password='root', db='demo', port=3306)
        self.db = self.dbs.cursor()
    def __del__(self):
        self.db.close()
        self.dbs.close()
    def query_table_data(self):
        """查询要验证的sql数据，得出：科目名称（SUBJECTNAME）：{科目名称（SUBJECTNAME）,上四分数（lowerQuartile）,中位数（median）,下四分数（upperQuartitle）}}"""
        sql = """with 
                t1 as(	select * from SUBJECT
                    where CLASSID = 9
                    ),
                t2 as (	select 
                            SUBJECTNAME as b,
                            round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*1+1)-min(r))+min(STATYHOUR)),2)as lowerQuartile
                        from(	select 
                                *
                                from (	select *,
                                            row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
                                            count(*) over(partition by SUBJECTNAME) as n
                                        from t1
                                    ) as a1
                                where r >=((n-1)/4*1+1-0.75) and r<=((n-1)/4*1+1+0.75) 
                        )as t2
                    group by SUBJECTNAME),
                t3 as (	select 
                            SUBJECTNAME as b,
                            round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*2+1)-min(r))+min(STATYHOUR)),2)as median
                        from(	select 
                                *
                                from (	select *,
                                            row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
                                            count(*) over(partition by SUBJECTNAME) as n
                                        from t1
                                    ) as a1
                                where (r >=((n-1)/4*2+1-0.75) and r<=((n-1)/4*2+1+0.75))
                        )as t2
                    group by SUBJECTNAME),
                t4 as (	select 
                            SUBJECTNAME as b,
                            round(((max(STATYHOUR)-min(STATYHOUR))*(((max(n)-1)/4*3+1)-min(r))+min(STATYHOUR)),2)as upperQuartitle
                        from(	select 
                                *
                                from (	select *,
                                            row_number() over(partition by SUBJECTNAME order by STATYHOUR) as r,
                                            count(*) over(partition by SUBJECTNAME) as n
                                        from t1
                                    ) as a1
                                where (r >=((n-1)/4*3+1-0.75) and r<=((n-1)/4*3+1+0.75))
                        )as t2
                    group by SUBJECTNAME)
                select t2.b as SUBJECTNAME,lowerQuartile,median,upperQuartitle
                from t2,t3,t4 where t2.b=t3.b and t3.b=t4.b;
                """
        self.db.execute(sql)
        temp_val_1 = self.db.fetchall()
        for i in temp_val_1:
            calculated_value_temp = {i[0]:{ "SUBJECTNAME":i[0],"lowerQuartile":i[1],"median":i[2],"upperQuartitle":i[3]}}
            calculated_value.update(calculated_value_temp)
    def query_sql_data(self):
        """ 查询验证的数值，得出 {科目名称（SUBJECTNAME}：{科目名称（SUBJECTNAME）,上四分数（lowerQuartile）,中位数（median）,下四分数（upperQuartitle）}}"""
        for SUBJECTNAME in calculated_value.keys():
            sql = "select STATYHOUR from SUBJECT where CLASSID = 9 and SUBJECTNAME = '{0}'order by STATYHOUR asc;".format(SUBJECTNAME)
            self.db.execute(sql)
            temp_val_1 = self.db.fetchall()
            SUBJECTNAMES = []
            for i in temp_val_1:
                SUBJECTNAMES +=list(i)
            z = np.percentile(SUBJECTNAMES, (25, 50, 75), interpolation='midpoint')
            lowerQuartile = round(z[0],2)
            median = round(z[1],2)
            upperQuartitle = round(z[2],2)
            verify_calculated_value_temps =  {SUBJECTNAME :{"SUBJECTNAME":SUBJECTNAME,"lowerQuartile":lowerQuartile,"median":median,"upperQuartitle":upperQuartitle}}
            verify_calculated_value.update(verify_calculated_value_temps)
    def verify_sql_data(self):
        """校验数据"""
        for SUBJECTNAME in calculated_value.keys():
            try:
                assert calculated_value.get(SUBJECTNAME).get('lowerQuartile')==verify_calculated_value.get(SUBJECTNAME).get('lowerQuartile'),\
                    '{0}=={1}报错\t{2}'.format(calculated_value.get(SUBJECTNAME).get('lowerQuartile'),verify_calculated_value.get(SUBJECTNAME).get('lowerQuartile'),(calculated_value.get(SUBJECTNAME),verify_calculated_value.get(SUBJECTNAME)))
                assert calculated_value.get(SUBJECTNAME).get('median')==verify_calculated_value.get(SUBJECTNAME).get('median'),\
                    '{0}=={1}报错\t{2}'.format(calculated_value.get(SUBJECTNAME).get('median'),verify_calculated_value.get(SUBJECTNAME).get('median'),(calculated_value.get(SUBJECTNAME),verify_calculated_value.get(SUBJECTNAME)))
                assert calculated_value.get(SUBJECTNAME).get('upperQuartitle')==verify_calculated_value.get(SUBJECTNAME).get('upperQuartitle'),\
                    '{0}=={1}报错\t{2}'.format(calculated_value.get(SUBJECTNAME).get('upperQuartitle'),verify_calculated_value.get(SUBJECTNAME).get('upperQuartitle'),(calculated_value.get(SUBJECTNAME),verify_calculated_value.get(SUBJECTNAME)))
            except AssertionError as err:
                print(err)
if __name__ == '__main__':
    MySqlS().query_table_data()
    MySqlS().query_sql_data()
    MySqlS().verify_sql_data()


>> 47.72==44.49报错	({'SUBJECTNAME': 'c++', 'lowerQuartile': 47.72, 'median': 73.86, 'upperQuartitle': 112.25}, {'SUBJECTNAME': 'c++', 'lowerQuartile': 44.49, 'median': 73.86, 'upperQuartitle': 112.65})
>> 13.55==14.71报错	({'SUBJECTNAME': 'gauss', 'lowerQuartile': 13.55, 'median': 29.61, 'upperQuartitle': 92.71}, {'SUBJECTNAME': 'gauss', 'lowerQuartile': 14.71, 'median': 29.62, 'upperQuartitle': 81.44})
>> 59.52==59.53报错	({'SUBJECTNAME': 'python', 'lowerQuartile': 59.52, 'median': 71.44, 'upperQuartitle': 112.83}, {'SUBJECTNAME': 'python', 'lowerQuartile': 59.53, 'median': 71.44, 'upperQuartitle': 112.83})

ok，执行，然后，断言出错了：

执行sql，分别找出这几条数据：

select STATYHOUR from SUBJECT where CLASSID = 9 and SUBJECTNAME= 'c++' order by STATYHOUR asc;
select STATYHOUR from SUBJECT where CLASSID = 9 and SUBJECTNAME= 'gauss' order by STATYHOUR asc;
select STATYHOUR from SUBJECT where CLASSID = 9 and SUBJECTNAME= 'python' order by STATYHOUR asc;

数据粘贴到excel：

得到的中位数和我执行查询到的中位数是 一致的，把数据粘贴到python里，查询中

import numpy as np
c = [21.86,38.03,50.95,72.3,75.42,111.86,113.43,147.58,21.86,38.03,50.95,72.3,75.42,111.86,113.43,147.58]
gauss=[1.59,3.52,12.4,17.01,20.29,38.94,58.88,103.99,144.65,144.93]
python = [16.74,19.82,59.21,59.84,66.57,71.44,87.05,111.84,113.82,122.19,123.31]
c_lowerQuartile = np.percentile(c,(25), interpolation='midpoint')
gauss_lowerQuartile =np.percentile(gauss,(25), interpolation='midpoint')
python_lowerQuartile = np.percentile(python,(25), interpolation='midpoint')

print(c_lowerQuartile)
print(gauss_lowerQuartile)
print(python_lowerQuartile)


>> 44.49
>> 14.705000000000002
>> 59.525000000000006

现在得到的问题是，得到的上四分数，
excel算出来的和python（numpy ）算出来的数值不一致；

暂时推测：
excel上四分 = （模拟数据（最大值）-模拟数（最小值））×（R-最小行号）+模拟数最小值

python上四分 = （模拟数据（最大值）+模拟数（最小值））*（R-最小行号）
两个公式下得到的结果不一致，
细推，我的数据不止这三组，其他的数据为什么断言就成功了，找出成功的数据，试试规则；

在计算上次执行成功的数据的时候，遇到了一个新问题，第二次执行断言测试，班级java也出现了断言失败；错误数由上次的三个变成了4个。

>> 47.72==44.49报错	({'SUBJECTNAME': 'c++', 'lowerQuartile': 47.72, 'median': 73.86, 'upperQuartitle': 112.25}, {'SUBJECTNAME': 'c++', 'lowerQuartile': 44.49, 'median': 73.86, 'upperQuartitle': 112.65})
>> 13.55==14.71报错	({'SUBJECTNAME': 'gauss', 'lowerQuartile': 13.55, 'median': 29.61, 'upperQuartitle': 92.71}, {'SUBJECTNAME': 'gauss', 'lowerQuartile': 14.71, 'median': 29.62, 'upperQuartitle': 81.44})
>> 58.96==58.22报错	({'SUBJECTNAME': 'java', 'lowerQuartile': 58.96, 'median': 107.58, 'upperQuartitle': 127.41}, {'SUBJECTNAME': 'java', 'lowerQuartile': 58.22, 'median': 107.58, 'upperQuartitle': 128.8})
>> 59.52==59.53报错	({'SUBJECTNAME': 'python', 'lowerQuartile': 59.52, 'median': 71.44, 'upperQuartitle': 112.83}, {'SUBJECTNAME': 'python', 'lowerQuartile': 59.53, 'median': 71.44, 'upperQuartitle': 112.83})

简单输入

z = [1,2,3,4,5,6]
x= np.percentile(z,(25), interpolation='midpoint')
print(x)


>> 2.5
进程已结束，退出代码为 0

这里实际的上四分口算应该是2.25
这种情况下暂时定义excel的结果是正确的，本人编写的sql找到的结果是正确的。
后续再查找numpy库的逻辑，看看这个区别在哪里