项目画像

开源项目画像

从以下五个维度生成当前项目画像：

社区影响力：从项目点赞、关注、fork角度生成评分 ；

项目成熟度：从项目疑修、合并请求、提交总数量生成评分 ；

项目健康度：从项目疑修、合并请求、许可证完成度生成评分 ；

团队影响度：从项目团队稳定性、新人增长率生成评分 ；

开发活跃度：从代码提交、疑修及合并请求等开发活动频率生成评分；

注意：

导入项目不显示项目画像 –> 在统计commits_log时，导入的项目不会存储数据；projects表中project_type=1表示导入项目，project_type=2表示导入的同步镜像项目，project_type=0表示普通项目，包括个人和组织项目；

项目数是实时更新的，社区数是存储在redis里面的，每日凌晨更新1次；

开启项目画像

后台配置：管理>网站配置>系统配置>全局变量配置> open_portrait_projects>配置project_id

后台配置了项目拥有项目画像功能后，项目接口（/api/${xx}/${xx} /simple.json）会返回open_portrait字段，true表示项目开启了项目画像功能。

image_2.png image_2.png

社区影响力

字段 说明
社区影响力 30*[点赞数/社区最高点赞数]+30*[关注数/社区最高关注数]+40*[fork数/社区最高fork数]
点赞数 SELECT praises_count FROM projects WHERE id=?;
社区最高点赞 社区最高点赞数指的是平台点赞数最高的项目的点赞数：
SELECT praises_count FROM projects WHERE praises_count = (SELECT MAX(praises_count) FROM projects);
关注数 SELECT watchers_countFROM projects WHERE id=?;
社区最高关注数 社区最高关注数指的是平台关注数最高的项目的关注数：SELECT watchers_count FROM projects
WHERE watchers_count = (SELECT MAX(watchers_count) FROM projects);
社区最高fork数 社区最高fork数指的是平台fork数最高的项目的fork数： SELECT forked_count FROM projectsWHERE forked_count = (SELECT MAX(forked_count) FROM projects);
fork数 SELECT forked_count FROM projects WHERE id=?;

项目成熟度

项目成熟度

  • 30*[pr总数/社区最高pr总数]+30*[issue总数/社区最高issue总数]+40*[commit总数/社区最高commit总数]

pr总数

select count(*) from pull_requests WHERE project_id =？;

社区最高pr总数

SELECT p.id, p.name, COUNT(pr.id) AS pr_count
FROM projects p
LEFT JOIN pull_requests pr ON p.id = pr.project_id
GROUP BY p.id, p.name
ORDER BY pr_count DESC
LIMIT 1; |
| issue总数 | select count(*) from issues WHERE
issue_classify='issue'
AND  project_id =？; |

社区最高issue总数

SELECT p.id, p.name, COUNT(i.id) AS issue_count
FROM projects p
LEFT JOIN issues i ON p.id = i.project_id
WHERE project_id != 0
AND issue_classify='issue'
GROUP BY p.id, p.name
ORDER BY issue_count DESC
LIMIT 1; |
| commit总数 | select count(*) from commit_logs
where project_id=？; |
| 社区最高commit总数 | SELECT p.id, p.name, COUNT(commits.id) AS commits_count
FROM projects p
LEFT JOIN commit_logs commits ON p.id = commits.project_id
GROUP BY p.id, p.name
ORDER BY commits_count DESC
LIMIT 1;

项目健康度

注意：

  • 这里统计的数都是当前项目内的数。
  • Issue已解决数量-10 小于0 认为是0；
  • Issue总数-10 小于0认为是0；
  • Pr已解决数量-5小于0认为是0；
  • Pr总数-5小于0认为是0；

项目健康度

  • 40*（issue已解决数量-10）/（issue总数-10）+40*（pr已解决数量-5）/（pr总数-5）+20*许可证参数（有为1，没有为0）

issue总数

select count(*) from issues WHERE
issue_classify='issue'
AND  project_id =？;

issue已解决数量（3已解决/5关闭）

 select count(*) from issues where
status_id in (3,5)
and issue_classify='issue'
and project_id=？

pr总数

select count(*) from pull_requests where project_id=？;

pr已解决数量（1合并）

select count(*) from pull_requests where status=1 and project_id=？;

许可证参数

select id, license_id from projects where id=？;

团队影响度

团队影响度

40*[当前仓库人数/社区最多仓库人数]+60*[近一个月成员新增数/近一个月社区成员新增最大值]

社区最多仓库人数

SELECT p.id, p.name, COUNT(member.id) AS member_count
FROM projects p
LEFT JOIN members member ON p.id = member.project_id
GROUP BY p.id, p.name
ORDER BY member_count DESC
LIMIT 1;

当前仓库人数

select count(*) from members where project_id=？;

近一个月成员新增数

SELECT project_id, COUNT(*) AS member_count
FROM members
WHERE created_on >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
and project_id=？;

近一个月社区成员新增最大值

SELECT project_id, COUNT(*) AS new_member_count
FROM members
WHERE created_on >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY project_id
ORDER BY new_member_count DESC
LIMIT 1;

开发活跃度

开发活跃度

  • 20+20*[近一月pr数/社区近一月pr数）]+20*[近一月issue数/社区近一月issue数]+40*[近一月commit数/社区近一月commit数]

近一月pr数

SELECT project_id, COUNT(*) AS new_pr_count
FROM pull_requests
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
and project_id=？;

社区近一月pr数

SELECT project_id, COUNT(*) AS new_pr_count
FROM pull_requests
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY project_id
ORDER BY new_pr_count DESC
LIMIT 1;

近一月issue数

SELECT project_id, COUNT(*) AS new_issue_count
FROM issues
WHERE created_on >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
and issue_classify='issue' and project_id=？;

社区近一月issue数

SELECT project_id, COUNT(*) AS new_issue_count
FROM issues
WHERE created_on >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
AND issue_classify='issue'
GROUP BY project_id
ORDER BY new_issue_count DESC
LIMIT 1;

近一月commit数

SELECT project_id, COUNT(*) AS new_commits_count
FROM commit_logs
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
and project_id=？

社区近一月commit数

SELECT project_id, COUNT(*) AS new_commits_count
FROM commit_logs
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY project_id
ORDER BY new_commits_count DESC
LIMIT 1;