基于python分析业务SQL常用查询字段

作者:jcmp      发布时间:2021-04-22      浏览量:0
一、背景由于业务灵活的查询需求,会演变成

一、背景

由于业务灵活的查询需求,会演变成各种各样的SQL语句,但是对于目前的SQL语句并没有做到分文别类的归纳整理。不能很好的把握业务脉络,理解业务需求。导致cube频繁修改,加大cube开发人员的工作量。从而导致了cube使用的体验欠佳。基于以上的业务背景,所以想收集到某段时间业务查询某个cube的所有SQL,进行分析整理,统计排序,直观且具体的了解到业务常用字段。

二、工具

python+excel+influxdb

三、实现逻辑

收集某段时间查询某个cube的所有SQL

目前比较快速的方法就是通过KM自带的influxdb实现数据的导出,可以规定导出某个cube的某段时间的查询SQL.保存到一个文件里。不限文件格式,获取命令 influxdb -host ip -port port -database database -execute “SQL content” -format ‘format’

四、使用脚本读取

解析规则:只需要截取出select from之间的字段,当然这个解析规则同样适用于带子查询的SQL, 为什么没有 where 和group by的字段?因为在绝大数情况下,过滤和分组字段都包含到select 和from之间。不解析别名,因为不同的查询会对应不同的别名。直接定位到表名+列名,这样对cube优化会起到一个参考指,指导哪些是常用维度,在下一个cube版本迭代开发,或者修改时,对于聚合组中的联合维度和rowkey设计的选择给到数据支持。 解些逻辑如下:

import reimport osimport csvimport operatordef sqlparse(): filePath = 'c://new//' filenames = os.listdir(filePath) for name in filenames: f = open(filePath+name, 'r',encoding='UTF-8') fw=open(filePath+name+".csv",'w',encoding='UTF-8',newline='') csv_writer = csv.writer(fw) buff = f.read() buff = buff.replace('\n','').replace('\t','').replace('"','').replace("'",'') pat = re.compile(r"\w+\.+\w+\w") result = pat.findall(buff) for item in result: list=item.split(',') for col in list: context=col.split(' AS ')[0].replace(' ','') csv_writer.writerow([context]) fw.close() f.close() f = open(filePath+name+".csv", 'r',encoding='UTF-8') print(f) count_dict = {} for line in f.readlines(): print(line) line = line.strip() count = count_dict.setdefault(line, 0) count += 1 count_dict[line] = count sorted_count_dict = sorted(count_dict.items(), key=operator.itemgetter(1), reverse=True) fw = open(filePath+name+".csv", 'w', encoding='UTF-8', newline='') csv_writer = csv.writer(fw) csv_writer.writerow(["name", "count"]) for item in sorted_count_dict: csv_writer.writerow([item[0], item[1]]) fw.close()if __name__ == '__main__': sqlparse()。

测试SQL

select * from Student RIGHT JOIN ( select t1.SId, class1, class2 from (select SId, score as class1 from sc where sc.CId = '01')as t1, (select SId, score as class2 from sc where sc.CId = '02')as t2 where t1.SId = t2.SId AND t1.class1 > t2.class2)ron Student.SId = r.SId;select * from ( select t1.SId, class1, class2 from (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, (SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2 where t1.SId = t2.SId and t1.class1 > t2.class2) rLEFT JOIN StudentON Student.SId = r.SId;select * from (select * from sc where sc.CId = '01') as t1, (select * from sc where sc.CId = '02') as t2where t1.SId = t2.SId;select * from(select * from sc where sc.CId = '01') as t1left join(select * from sc where sc.CId = '02') as t2on t1.SId = t2.SId;select * from(select * from sc where sc.CId = '02') as t2right join(select * from sc where sc.CId = '01') as t1on t1.SId = t2.SId;select * from scwhere sc.SId not in ( select SId from sc where sc.CId = '01')AND sc.CId= '02';select s.sid, s.sname,r.coursenumber,r.scoresumfrom ( (select student.sid,student.sname from student )s left join (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid )r on s.sid = r.sid);select * from studentwhere student.sid in ( select sc.sid from sc where sc.cid in( select sc.cid from sc where sc.sid = '01' ));selectsc.CId ,max(sc.score) as 最高分,min(sc.score) as 最低分,AVG(sc.as) as 平均分,count(*) as 选修人数,sum(case when sc.score>=60 then 1 else 0 end )/count(*) as 及格率,sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*) as 中等率,sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*) as 优良率,sum(case when sc.score>=90 then 1 else 0 end )/count(*) as 优秀率from scGROUP BY sc.CIdORDER BY count(*)DESC, sc.CId ASCset @crank=0;select q.sid, total, @crank := @crank +1 as rank from(select sc.sid, sum(sc.score) as total from scgroup by sc.sidorder by total desc)q;

五、说明

SQL必须符合SQL语法标准格式 分析结果 1.会生成一个csv文件 如下。

2.以上是按照倒叙排列,如果想直观看出,可以借助excel的绘图功能美化。 这里由于图表格式限制,并没有完全列出来。

六、从结果可以分析出什么

-‘*’多达13个,由此可以知这是一个明细查询次数较多的场景 -sid次之,但是从两个表出,可以将出字段的表统一使用。 -class1和class2数量一致,含义相同,可以设置为联合维度组。 以上就是针对测试SQL做了个常用查询字段分析。