云原生大数据计算服务 MaxCompute：Github公开事件数据

Query样例：

SET odps.namespace.schema = TRUE;
SELECT dws.repo_id AS repo_id,
 repos.name AS repo_name,
 SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
WHERE MONTH >= '2015-01' AND MONTH <='2022-12'
GROUP BY dws.repo_id,
 repos.name
ORDER BY stars DESC LIMIT 10;

分析结果：过去5年开源数据库项目中elasticsearch最受欢迎，其次是redis和prometheus。

Query样例：

SET odps.namespace.schema = TRUE;
SET odps.sql.validate.orderby.limit=FALSE;
 WITH tmp as
 (SELECT dws.repo_id AS repo_id, repos.name AS repo_name, SUM(dws.stars) AS stars, SUBSTR(MONTH,1,4) AS YEAR, row_number() over (partition BY SUBSTR(MONTH,1,4)
 ORDER BY SUM(dws.stars) DESC) AS ranknum
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
 JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
 WHERE MONTH>='2018-01'
 GROUP BY dws.repo_id, repos.name,SUBSTR(MONTH,1,4))
SELECT repo_id,
 repo_name,
 stars,
 ranknum,
 YEAR
FROM tmp
WHERE YEAR<=2022
 AND ranknum<=10
ORDER BY YEAR ASC,ranknum ASC;

分析结果：在过去5年，各开源数据库在开发者心中Top10的地位此起彼伏，其中clickhouse是排名上升最快的项目，从2018年第十名迅速升至2021年的第一名，于2022年被稳步上升的redis超越。反观taosdata/TDengine在2019年排名第一，短短一年时间下降至第九名。

Query样例：

set odps.namespace.schema = true;
set odps.sql.validate.orderby.limit=false;
WITH top_10_repos AS (
 SELECT
 dws.repo_id as repo_id, 
 repos.name as repo_name, 
 SUM(dws.stars) as stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
 join bigdata_public_dataset.github_events.db_repos repos on dws.repo_id = repos.id
 where month >= '2018-01' AND MONTH <='2022-12'
 group by dws.repo_id, repos.name
 ORDER BY stars DESC
 LIMIT 10
),
tmp AS (
 SELECT
 month,
 repo_id,
 stars,
 SUM(stars) OVER (partition by repo_id ORDER BY month ASC ) AS total_stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month
 where month >= '2015-01' and stars is not null
 and repo_id in (select repo_id from top_10_repos)
 group by repo_id,month,stars
 ORDER BY month ASC,repo_id)
 
SELECT
 tmp.month as month,
 top_10_repos.repo_name as repo_name,
 tmp.total_stars as total_stars

 from tmp
 join top_10_repos ON top_10_repos.repo_id = tmp.repo_id
 group by month,repo_name,total_stars
 ORDER BY month ASC,repo_name
;

分析结果：Top10开源数据库项目中elasticsearch一直是最受欢迎的开源数据库，clickhouse自2021年增速较快。

Query样例：

set odps.namespace.schema = true;
SELECT
 repos.name AS repo_name,
 sum（dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dws.repo_id
WHERE month >= '2023-01' AND month <= '2023-06'
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 10;

分析结果：clickhouse数据库在2023年上半年最受欢迎，其次是prometheus和redis。

Query样例：

set odps.namespace.schema = true;
SELECT
 repos.name AS repo_name,
 COUNT(dwd.id) AS num
FROM bigdata_public_dataset.github_events.dwd_github_events_odps dwd
 JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dwd.repo_id
WHERE type = 'PullRequestEvent'
 AND ds>='2023-01-01' and ds<='2023-06-30'
 AND action = 'opened'
GROUP BY repos.name
ORDER BY num DESC
LIMIT 10;

分析结果：在2023上半年，StarRocks保持最活跃的维护与更新（定义开启PullRequest为项目活跃的表现）。

Query样例：

SET odps.namespace.schema=true; 
WITH a AS 
(
 SELECT repo_id
 ,repo_name
 ,actor_id
 ,actor_login
 ,COUNT(*) AS contribution
 ,ds
 FROM bigdata_public_dataset.github_events.dwd_github_events_odps
 WHERE ds >='2021-01-01' and ds<='2021-12-31'
 AND (
 (
 type = 'PullRequestEvent'
 AND action = 'opened'
 )
 OR (
 type = 'IssuesEvent'
 AND action = 'opened'
 )
 OR (
 type = 'IssueCommentEvent'
 AND action = 'created'
 )
 OR (
 type = 'PullRequestReviewEvent'
 AND action = 'created'
 )
 OR (
 type = 'PullRequestReviewCommentEvent'
 AND action = 'created'
 )
 OR (
 type = 'PushEvent'
 AND action IS NULL
 )
 )
 GROUP BY repo_id
 ,repo_name
 ,actor_id
 ,actor_login
 ,ds
)
SELECT repo_name
 ,actor_login
 ,SUM(contribution) AS contribution
FROM a
WHERE repo_name = 'StarRocks/starrocks'
AND actor_login NOT LIKE '%[bot]'
AND actor_login NOT LIKE 'cockroach%'

GROUP BY repo_name
 ,actor_login
ORDER BY contribution DESC
LIMIT 10
;

分析结果：在2023年上半年，最活跃的开源数据库项目中主要的个人贡献者是kangkaisen。

Query样例：

SET odps.namespace.schema = TRUE;

SELECT dws.repo_id AS repo_id,
 repos.name AS repo_name,
 SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.programming_language_repos repos ON dws.repo_id = repos.id
WHERE MONTH >= '2015-01'
GROUP BY dws.repo_id,
 repos.name
ORDER BY stars DESC LIMIT 10;

分析结果：过去五年最受欢迎的编程语言是Go，收获了81,642颗星，其次是TypeScript和Node等语言。

Query样例：

set odps.namespace.schema = true;
set odps.sql.validate.orderby.limit=false;
WITH top_10_repos AS (
 SELECT
 dws.repo_id as repo_id, 
 repos.name as repo_name, 
 SUM(dws.stars) as stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_by_month dws
 join bigdata_public_dataset.github_events.programming_language_repos repos on dws.repo_id = repos.id
 where month >= '2018-01' and month <='2022-12'
 group by dws.repo_id, repos.name
 ORDER BY stars DESC
 LIMIT 10
),
tmp AS (
 SELECT
 month,
 repo_id,
 stars,
 SUM(stars) OVER (partition by repo_id ORDER BY month ASC ) AS total_stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_by_month
 where month >= '2015-01' and stars is not null
 and repo_id in (select repo_id from top_10_repos)
 group by repo_id,month,stars
 ORDER BY month ASC,repo_id)
 
SELECT
 tmp.month as month,
 top_10_repos.repo_name as repo_name,
 tmp.total_stars as total_stars

 from tmp
 join top_10_repos ON top_10_repos.repo_id = tmp.repo_id
 group by month,repo_name,total_stars
 ORDER BY month ASC,repo_name
;

分析结果：在过去5年，Go是受欢迎程度增长最快的编程语言。