sql - 从分组查询中删除连接操作
问题描述
我有一个看起来像的表:
usr_id query_ts
12345 2019/05/13 02:06
123444 2019/05/15 04:06
123444 2019/05/16 05:06
12345 2019/05/16 02:06
12345 2019/05/15 02:06
它包含运行查询时的用户 ID。表中的每个条目代表在给定时间戳运行 1 个查询的 ID。
我正在尝试制作这个:
usr_id day_1 day_2 … day_30
12345 31 13 15
123444 23 41 14
我想显示每个 ID 在过去 30 天内每天运行的查询数,如果当天没有运行查询,它将为 0。
这是我提出的查询的一部分,
SELECT
t1.usr_id,
case when t1.count_day_1 is null then 0 else t1.count_day_1 end as day_1,
case when t2.count_day_2 is null then 0 else t2.count_day_2 end as day_2
FROM
(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_1,
COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_1
FROM db.table
WHERE
DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 1
AND
from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
AND from_unixtime(unix_timestamp())
GROUP BY usr_id, day_1) t1
LEFT JOIN
(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_2,
COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_2
FROM db.table
WHERE
DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 2
AND
from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
AND from_unixtime(unix_timestamp())
GROUP BY usr_id, day_2) t2
ON (t1.usr_id = t2.usr_id)
ORDER BY t1.usr_id;
这很好用,它显示了前 2 天每天运行的查询数,并将 NULL 替换为 0。
问题是要让它工作 30 天,我必须使用 30 个 LEFT JOIN,这会在集群上拉出约 400GB+ 的内存。
有没有更简单的方法来做到这一点?
解决方案
尝试在没有连接的情况下执行此操作,并在 WHERE 中使用 current_date 或current_timestamp常量,而不是 unix_timestamp(),此函数不是确定性的,其值对于查询执行的范围也不是固定的,因此会阻止查询的适当优化 - 这有自 2.0 起已弃用,取而代之的是 CURRENT_TIMESTAMP 常量:
select usr_id,
nvl(count(case when from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "dd") = 1 then 1 end),0) as day_1,
nvl(count(case when from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "dd") = 2 then 1 end),0) as day_2
...
from db.table
WHERE
from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
BETWEEN date_sub(current_date, 30) AND current_date)
group by usr_id
推荐阅读
- r - 在 R Boxplot (ggpubr) 中调整字体大小和小数位
- javascript - 仅在 Firefox 上:finally 不是函数 | 被包覆盖的承诺
- sql - MS-Script 查询无法工作或运行
- python - 如何将值添加到python中的嵌套列表
- c - 将 '\0' 放在字符串中间以提前终止它是行不通的
- powerbi - 获取日期之间的列搜索值
- java - 发生未知错误请检查服务器响应的 HTTP 结果代码和内部异常
- haproxy - 使用具有多个 IP 地址/网络的环境变量配置 HAProxy ACL
- r - 导入 R 后 CSV 文件中出现奇怪字符 (†)
- asp.net - 如何在 ascx 文件的不同 div 中显示相同的用户控件以提高响应能力?