更好的sql在1小时滑动窗口内链接记录

6g8kf2rb  于 2021-06-09  发布在  Hbase
关注(0)|答案(1)|浏览(403)

我尝试用(user1,user2,count)创建一个新表,表示两个用户在1小时间隔内共享同一列值的时间。

WITH d1 AS (SELECT * FROM user_access_tab 
WHERE last_access >= 1544630400 AND last_access <= 1545601214)
SELECT d1.userid, d2.userid, COUNT(*) as count
FROM d1
INNER JOIN d1 AS d2
ON d1.item = d2.item AND d1.userid != d2.userid
WHERE d1.last_access < d2.last_access  AND 
      (d2.last_access - d1.last_access) <= 3600
GROUP BY d1.userid, d2.userid

但是,即使只有1小时的时间间隔限制,这样的查询也非常慢。我需要查询大约6个月的数据,这些数据累积到数十亿行记录。如何改进sql?
“用户访问”选项卡如下所示

新表如下所示。只要两个用户ID的最后一次访问时间在1小时窗口内,它们就被链接起来,并且计数器增加1。

kninwzqo

kninwzqo1#

imho,你的查询的问题是你连接了太多的记录。
以下面我插入cte的最小示例为例:

WITH user_access_tab(item, userid, last_access) AS (
    SELECT UNNEST(ARRAY['A', 'A', 'A', 'A', 'A', 'A']), 
           UNNEST(ARRAY[11383575,11383575,52539489,52539489,24830131,24830131]),
           UNNEST(ARRAY[1545645324,1545645325,1545647895,1545647896,1545646895,1545646896])
    /*UNION ALL
    SELECT UNNEST(ARRAY['A', 'A', 'A', 'A', 'A', 'A']), 
           UNNEST(ARRAY[11383575,11383575,52539489,52539489,24830131,24830131]),
           UNNEST(ARRAY[1545645326,1545645327,1545647897,1545647898,1545646897,1545646898])*/
),
d1 AS (SELECT * FROM user_access_tab 
WHERE last_access >= 1544630400 AND last_access <= 1545661214
)                       
SELECT d1.userid, d2.userid, COUNT(*) as count
FROM d1
INNER JOIN d1 AS d2
ON d1.item = d2.item AND d1.userid != d2.userid
WHERE d1.last_access < d2.last_access  AND 
      (d2.last_access - d1.last_access) <= 3600
GROUP BY d1.userid, d2.userid

cte有6条记录,查询返回3条记录,每条记录的计数等于4。
现在取消注解cte的后半部分,得到的是3x16。这比cte中的记录数量还要多,而且随着用户和事件的增多,情况只会变得更糟。
我建议你在你的生活的一方面做些限制性的事情 JOIN . 示例如下:

WITH d1 AS (SELECT * FROM user_access_tab 
WHERE last_access >= 1544630400 AND last_access <= 1545661214),
d2 AS (
SELECT *
FROM d1 d
WHERE NOT EXISTS (SELECT FROM d1 WHERE item = d.item AND userid = d.userid AND d.last_access BETWEEN last_access+1 AND d.last_access + 3600))
SELECT d2.item, d2.userid, d1.userid, COUNT(*)
FROM d2
LEFT OUTER JOIN d1 ON d2.item = d1.item AND d2.userid = d1.userid AND d1.last_access BETWEEN d2.last_access and d2.last_access + 3600
GROUP BY d2.item, d2.userid, d1.userid

显然,这将改变 COUNT(*) 专栏(在更快的基础上),但因为它似乎没有太多的意义之前,我想说这是最好的。

相关问题