时间戳

首页 > 解决方案 > 合并为具有多列日期的单行

sql - 合并为具有多列日期的单行

问题描述

我需要从 Github 采样大量的 java 项目。我正在使用 Google Big Query 来查询 GitHub 存档。我目前能够制作一个长列表，其中包含顶级 Java 存储库的每个发布事件。

但是，我想合并结果，将具有匹配 repo 名称的结果放在一行中，然后为每个事件分隔日期列，最好是逐月创建，并带有指向我将提取的版本 URL 的链接来自 JSON 有效负载（一个月内可能有多个发布事件）。我在下面给出了一个模型

除了基础知识，我对 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 (非常长的 repos 列表) ORDER BY repo.name* ;

我将添加一个 JSON_EXTRACT 以获取有效负载从有效负载中获取 URL

选择 repo.name, created_at, JSON_EXTRACT(payload,'$.zipball_url') FROM

架构：

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

标签： sqlgoogle-bigquery

解决方案

我想你只想要条件聚合：

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

推荐阅读