sonar版本Version 5.3
sonar 取数据口径主要的表
projects：项目表
metrics：指标表 比如id为3的是代码行数 id为20是复杂度
project_measures ： 项目与指标表的关系表 一个项目具体指标的值存储在此表
snapshots： 快照表，每次执行一次，都会在快照表插入一条记录
代码语言：javascript复制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
