sonar 表结构以及取数语句

sonar版本Version 5.3

sonar 取数据口径主要的表  

projects：项目表

metrics：指标表  比如id为3的是代码行数 id为20是复杂度 

project_measures ： 项目与指标表的关系表 一个项目具体指标的值存储在此表

snapshots： 快照表，每次执行一次，都会在快照表插入一条记录

 

SELECT sum(case when projdesc.metric_id = 3 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 3 or null) as '代码总行数',
 sum(case when projdesc.metric_id = 20 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 20 or null) as '代码复杂度',
 sum(case when projdesc.metric_id = 90 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 90 or null) as '代码重复度%',
 sum(case when projdesc.metric_id = 112 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 112 or null) as '技术债务占比%',
 sum(case when projdesc.metric_id = 92 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 92 or null) as '遗留违规总数（单位：个）',
 sum(case when projdesc.metric_id = 93 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 93 or null) as '遗留违规数：阻断',
 sum(case when projdesc.metric_id = 94 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 94 or null) as '遗留违规数：严重',
 sum(case when projdesc.metric_id = 95 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 95 or null) as '遗留违规数：主要',
 sum(case when projdesc.metric_id = 29 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 29 or null) as '单元测试用例数',
 sum(case when projdesc.metric_id = 34 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 34 or null) as '单元测试成功率',
 sum(case when projdesc.metric_id = 31 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 31 or null) as '单元测试BUG数',
 sum(case when projdesc.metric_id = 36 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 36 or null) as '单元测试覆盖率',
 sum(case when projdesc.metric_id = 42 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 42 or null) as '单元测试代码覆盖率',
 sum(case when projdesc.metric_id = 49 then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 49 or null) as '单元测试分支覆盖率',
 sum(case when projdesc.metric_id = 108 and projdesc.rule_id is not null then projdesc.value else 0 end)/ COUNT(projdesc.metric_id = 108 and projdesc.rule_id is not null or null)/8 as '技术债务总量（单位：D）', 
 COUNT(1) as 'sonar执行次数',
 avg((snap.build_date- snap.created_at)/1000) as 'Sonar分析时长'
FROM snapshots snap
LEFT JOIN project_measures projdesc ON projdesc.snapshot_id=snap.id
-- 3代码行数 20复杂度 90代码重复度 112技术债务占比 92遗留违规总数 93阻断 94严重 95主要 
-- 29单元测试数 34单元测试成功率 31单元测试错误数 36覆盖率 42代码覆盖率 49分支覆盖  108技术债务总量
WHERE snap.project_id =81531 AND projdesc.metric_id IN(3,20,90,112,92,93,94,95,29,34,31,36,42,49,108) 
 -- and FROM_UNIXTIME(snap.created_at/1000,'%Y-%m-%d') >=  '2018-12-1'
AND snap.created_at <= UNIX_TIMESTAMP(LAST_DAY(CURDATE()))*1000 -- 月底
AND snap.created_at >= UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY))*1000 -- 本月第一天
GROUP BY snap.project_id