用 BigQuery 查询 GitHub 上的 'Repositories contributed to'
01 Mar 2017在 Google BigQuery 上查,不考虑免费额度,大约每次 7 美元
WITH user_name AS (SELECT 'crvv' UNION ALL SELECT 'CRVV'),
data AS (
SELECT
type,
repo.name AS repo_name,
regexp_extract(repo.name, '(.*)/.*') AS repo_owner,
coalesce(
JSON_EXTRACT_SCALAR(payload, '$.pull_request.html_url'),
JSON_EXTRACT_SCALAR(payload, '$.issue.html_url')
) AS url,
coalesce(
JSON_EXTRACT_SCALAR(payload, '$.pull_request.created_at'),
JSON_EXTRACT_SCALAR(payload, '$.issue.created_at')
) AS time
FROM
`githubarchive.month.201*`
WHERE
_TABLE_SUFFIX > '50'
AND type IN ('IssuesEvent', 'PullRequestEvent')
AND actor.login IN (SELECT * FROM user_name)
AND JSON_EXTRACT_SCALAR(payload, '$.action') = 'opened'
)
SELECT * FROM data
WHERE repo_owner NOT IN (SELECT * FROM user_name)
ORDER BY time