我需要从Github中采样大量的java项目。我正在使用谷歌大查询来查询GitHub档案。我目前能够制作一个长列表,其中包含顶级java repos的每个发布事件。
然而,我想合并结果,将具有匹配repo名称的结果放在一行中,然后为每个事件创建单独的日期列,最好是逐月创建,并创建一个指向发布URL的链接,我将从JSON负载中提取该URL(一个月内可能有多个发布事件(。我在下面给出了一个模型
除了基础知识之外,我对SQL几乎没有什么经验,我想知道我想做的是简单的还是复杂的。我可以用java完成这一切,但我希望在查询阶段使用SQL来节省时间。
电流输出
repo.name created at link
repoA 2018-12-06 02:04:27 UTC url
repoA 2018-02-07 02:33:57 UTC url
repoA 2018-02-18 00:55:15 UTC url
repoB 2018-03-21 19:14:02 UTC url
repoB 2018-04-11 02:07:04 UTC url
repoC 2018-07-02 14:58:12 UTC url
我想要什么
Jan Feb Mar April May
REPO A URL URL
REPO B URL
REPO C URL URL , URL
我当前的查询:
*SELECT repo.name,created_at FROM githubarchive.year.2018 WHERE type="ReleaseEvent"AND repo.name IN(很长的repo列表(ORDER BY repo.name*;
我将添加一个JSON_EXTRACT来获取来自有效负载的URL
从中选择repo.name,created_at,JSON_EXTRACT(有效负载,'$.zipball_url'(
架构:
type STRING NULLABLE https://developer.github.com/v3/activity/events/types/
payload STRING NULLABLE Event payload in JSON format
repo. name STRING NULLABLE Repository name
created_at TIMESTAMP NULLABLE Timestamp of associated event
我认为您只需要条件聚合:
SELECT repo.name, created_at,
MAX(CASE WHEN EXTRACT(month FROM created_at) = 1 THEN link END) as jan,
MAX(CASE WHEN EXTRACT(month FROM created_at) = 2 THEN link END) as feb,
MAX(CASE WHEN EXTRACT(month FROM created_at) = 3 THEN link END) as mar
FROM githubarchive.year.2018
WHERE type = 'ReleaseEvent' AND
repo.name IN ( very long list of repos )
GROUP BY BY repo.name