sonarqube 代码审查统计分值SQL

场景

        使用SonarQube进行代码质量检查是，想导出检查结果编译记录和展现，除了一些导出插件外，可以通过数据库查询的方式根据需要查询想要的结果。

方法说明

相关数据表说明
projects：项目字典表，uuid-项目主键，name项目名，
project_branches：项目代码分支，project_uuid项目主键，updated_at项目分支最后检查时间
metrics：检查指标，uuid指标主键，name指标名
analysis_properties：检查记录，uuid，analysis_uuid检查主键，text_value-检查场景（scm全量代码库，ci-增量代码），created_at检查时间
project_measures：检查结果，metric_uuid指标主键，analysis_uuid检查主键，component_uuid项目主键
分项目统计
最近的检查结果
select ps.name,ms.name,pm.value,to_char(to_timestamp(pb.updated_at/1000),'YYYY-MM-DD HH24:MI:SS') updated_at
from project_branches pb,project_measures pm, analysis_properties ap ,projects ps,metrics ms
where   ap.analysis_uuid = pm.analysis_uuid and ap.text_value='git' and abs(ap.created_at-pb.updated_at)<60000
and  ps.uuid = pb.project_uuid
and  pb.project_uuid = pm.component_uuid
and ms.uuid=pm.metric_uuid
and ms.name in('bugs','vulnerabilities','code_smells','duplicated_lines_density','ncloc')
order by name
分团队项目汇总
可以根据项目名前几位进行分组时
select substr(ps.name, 0,10) team,ms.name,ROUND(sum(pm.value),2)
from project_branches pb,project_measures pm, analysis_properties ap ,projects ps,metrics ms
where   ap.analysis_uuid = pm.analysis_uuid and ap.text_value='git' and abs(ap.created_at-pb.updated_at)<60000
and  ps.uuid = pb.project_uuid
and  pb.project_uuid = pm.component_uuid
and ms.uuid=pm.metric_uuid
and ms.name in('bugs','vulnerabilities','code_smells','duplicated_lines_density','ncloc')
group by substr(ps.name, 0,10),ms.name
ORDER BY team
————————————————
 优化统计后：

select project_name  "工程名称", bugs "bug数量", vulnerabilities "漏洞数", code_smells "待优化", bugs+vulnerabilities+code_smells "缺陷总数",duplicated_lines_density||'%' "重复行", ncloc "代码行数",
round(100-(bugs*30+vulnerabilities*20+code_smells*50)/ncloc*1000/100,2) "质量分值" --百分比可灵活调整
from 
(
select project_name,
sum(case mesure_name when 'bugs' then round(value,0) else 0 end) as bugs,
sum(case mesure_name when 'vulnerabilities' then round(value,0) else 0 end) as vulnerabilities,
sum(case mesure_name when 'code_smells' then round(value,0) else 0 end) as code_smells,
sum(case mesure_name when 'duplicated_lines_density' then round(value,2) else 0 end) as duplicated_lines_density,
sum(case mesure_name when 'ncloc' then round(value,0) else 0 end) as ncloc
from (
select distinct ps.name project_name,ms.name mesure_name,pm.value,to_char(to_timestamp(pb.updated_at/1000),'YYYY-MM-DD HH24:MI:SS') updated_at
from project_branches pb,project_measures pm, analysis_properties ap ,projects ps,metrics ms
where   ap.analysis_uuid = pm.analysis_uuid 
--and ap.text_value='git'
 and abs(ap.created_at-pb.updated_at)<60000
and  ps.uuid = pb.project_uuid
and  pb.project_uuid = pm.component_uuid
and ms.uuid=pm.metric_uuid
and ms.name in('bugs','vulnerabilities','code_smells','duplicated_lines_density','ncloc')
) f
group by project_name
)
g