背景

由于业务灵活的查询需求，会演变成各种各样的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 re
import os
import csv
import operator
def 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
)r
on 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
) r
LEFT JOIN Student
ON Student.SId = r.SId;
select * from
    (select * from sc where sc.CId = '01') as t1,
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;
select * from
(select * from sc where sc.CId = '01') as t1
left join
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;
select * from
(select * from sc where sc.CId = '02') as t2
right join
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;
select * from sc
where sc.SId not in (
    select SId from sc
    where sc.CId = '01'
)
AND sc.CId= '02';
select s.sid, s.sname,r.coursenumber,r.scoresum
from (
    (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 student
where student.sid in (
    select sc.sid from sc
    where sc.cid in(
        select sc.cid from sc
        where sc.sid = '01'
    )
);
select
sc.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 sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC
set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc)q;