多个完全外部连接(来自同一表中的数据)
多个完全外部连接(来自同一表中的数据)
提问于 2018-05-08 13:25:48
我有一些日志数据，如下所示
日志
+---------------------+---------+---------+------+
| date | api_key | version | data |
+---------------------+---------+---------+------+
| 2018-05-08 01:00:00 | AAA | v1 | data |
| 2018-05-08 02:00:00 | AAA | v2 | data |
| 2018-05-06 03:00:00 | AAA | v2 | data |
| 2018-05-06 04:00:00 | BBB | v1 | data |
+---------------------+---------+---------+------+date是API调用的日期。api_key是使用的API键。version是使用的API的版本(我们有两个)
这些数据目前生活在RDBMS中，我需要将数据转移到雅典娜。
聚合
有一些遗留代码定期运行以聚合日志。然后将此聚合数据显示在仪表板上。从本质上讲，它将平面日志数据(上面)转换为API键聚合的数据，提供基于版本的调用计数，并在不同的时间窗口(所有时间、1天内、7天内等)进行计数。
例如，下面的聚合表显示聚合的原始数据。
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| api_key | hits | hits_v1 | hits_v2 | hits_1 | hits_1_v1 | hits_1_v2 | hits_7 | hits_7_v1 | hits_7_v2 |
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| AAA | 3 | 1 | 2 | 2 | 1 | 1 | 3 | 1 | 2 |
| BBB | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 |
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+这些栏的意思是：
hits给定的api-key的所有时间调用的次数hits_v1给定的api-key对v1的所有时间调用数hits_v2给定的api-key对v2的所有时间调用数hits_1指定的api-key在最后一天的呼叫数hits_1_v1由给定的api-key在最后一天调用的v1数hits_1_v2由给定的api-key在最后一天调用的v2数hits_7指定的api-key在过去7天内的呼叫次数hits_7_v1过去7天内由给定的api-key发出的v1调用数hits_7_v2过去7天内由给定的api-key发出的v2调用数
SQL
下面是用于创建此聚合表的SQL。
SELECT coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key) api_key,
coalesce(hits_v1.hits_v1,0) + coalesce(hits_v2.hits_v2,0) hits,
coalesce(hits_v1.hits_v1,0) hits_v1,
coalesce(hits_v2.hits_v2,0) hits_v2,
coalesce(hits_1_v1.hits_1_v1,0) + coalesce(hits_1_v2.hits_1_v2,0) hits_1,
coalesce(hits_1_v1.hits_1_v1,0) hits_1_v1,
coalesce(hits_1_v2.hits_1_v2,0) hits_1_v2,
coalesce(hits_7_v1.hits_7_v1,0) + coalesce(hits_7_v2.hits_7_v2,0) hits_7,
coalesce(hits_7_v1.hits_7_v1,0) hits_7_v1,
coalesce(hits_7_v2.hits_7_v2,0) hits_7_v2
FROM
(
(select api_key,count(*) as hits_v1 from logs where (version='v1' or version='') group by api_key) hits_v1
FULL OUTER JOIN
(select api_key,count(*) as hits_v2 from logs where version='v2' group by api_key) hits_v2 on hits_v2.api_key = hits_v1.api_key
FULL OUTER JOIN
(select api_key,count(*) as hits_1_v1 from logs where (version='v1' or version='') and (date > localtimestamp - interval '1' day) group by api_key) hits_1_v1 on hits_1_v1.api_key = coalesce(hits_v1.api_key, hits_v2.api_key)
FULL OUTER JOIN
(select api_key,count(*) as hits_1_v2 from logs where version='v2' and (date > localtimestamp - interval '1' day) group by api_key) hits_1_v2 on hits_1_v2.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key)
FULL OUTER JOIN
(select api_key,count(*) as hits_7_v1 from logs where (version='v1' or version='') and (date > localtimestamp - interval '7' day) group by api_key) hits_7_v1 on hits_7_v1.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key)
FULL OUTER JOIN
(select api_key,count(*) as hits_7_v2 from logs where version='v2' and (date > localtimestamp - interval '7' day) group by api_key) hits_7_v2 on hits_7_v2.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key, hits_7_v1.api_key)
)
order by api_key asc正如你所看到的，这是相当重复的，冗长的。更糟糕的是，我需要添加的列越多(14天、30天、60天等)，我每次都需要添加到on子句中，以包含所有以前的联接。
这是可行的，但我相信一定有一种更干净的方法来做到这一点。有人能帮忙吗？
PS。是的，我确实需要保留这个聚合表--它可能不是很好，但是很多其他代码都依赖于它是这样的，所以它不会改变。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-05-08 13:59:31
使用条件聚合：
select api_key,
sum(case when version = 'v1' or version = '' then 1 else 0 end) AS hits_v1,
sum(case when version = 'v2' then 1 else 0 end) AS hits_v2,
sum(case when (version = 'v1' or version = '') and (date > localtimestamp - interval '1' day) then 1 else 0 end) as hits_v1_1,
. . .
from logs l
group by api_key;Stack Overflow用户
发布于 2018-05-08 15:11:43
您可以使用此查询而不是子查询。
如果你想得到hits
hits给定的api-key的所有时间调用的次数
您可以选择count(1)来获取所有数据。
SELECT api_key,
count(1) hits,
SUM(CASE WHEN (version='v1' or version='') THEN 1 ELSE 0 END ) hits_v1,
SUM(CASE WHEN (version = 'v2' or version='') THEN 1 ELSE 0 END ) hits_v2,
SUM(CASE WHEN (date > localtimestamp - interval '1' day) THEN 1 ELSE 0 END) hits_1,
SUM(CASE WHEN (date > localtimestamp - interval '1' day) and (version='v1' or version='') THEN 1 ELSE 0 END) hits_1_v1,
SUM(CASE WHEN (date > localtimestamp - interval '1' day) and (version='v2' or version='') THEN 1 ELSE 0 END) hits_1_v2,
SUM(CASE WHEN (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7,
SUM(CASE WHEN (version='v1' or version='') and (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7_v1,
SUM(CASE WHEN (version='v2' or version='') and (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7_v2
FROM logs
group by api_key方形小提琴：http://sqlfiddle.com/#!9/be990/5
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50234762
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号