玩转MaxCompute SQL! 30分钟搞定数据分析挖掘

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 >= '2018-01' AND MONTH <='2022-12'
GROUP BY dws.repo_id,
 repos.name
ORDER BY stars DESC LIMIT 10;

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;

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
;

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;

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;

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
;

SET odps.namespace.schema=true; 
SELECT
 language,
 count(*) total
FROM
 bigdata_public_dataset.github_events.dwd_github_events_odps
WHERE
 ds>=date_add(getdate(), -365)
 AND language IS NOT NULL
GROUP BY
 language
ORDER BY
 total DESC
LIMIT 10;

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;

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
;