mysql - 近1000条记录对比查询

Question

我希望根据用户最近 1000 次操作来比较数据库中的用户。查询的基本结构如下:

SELECT p.id, p2.id, ROUND(AVG(1000 + ABS(p2.ss - p.ss)/6.1 * -50)) AS sim_score
FROM 
(SELECT * FROM p WHERE id = 519000 ORDER BY p_date DESC LIMIT 0,1000) p, 
(SELECT * FROM p WHERE id = 279000 ORDER BY p_date DESC LIMIT 0,1000) p2 

在本例中，我指定了两个用户(519000 和 279000)。这将返回一条记录，其中包含每个 ID 和相似度分数。对于数据库中每个用户比较 (u^2-u) 返回一条记录的选项是什么，目的是将该结果转储到另一个表中？我可以看到在夜间批处理作业中使用嵌套游标，但我希望有更有效的方法。

Answer 1

您可以使用排名变量创建每个用户最近 1,000 个操作的表格，然后使用分组依据来比较每个用户。请注意底部的 where 条件，以确保不会重复比较。

关于效率的说明:原始问题中的查询执行大约需要 1 秒。添加下表之一时，执行时间需要 30 分钟。第二个表可能会将查询时间延长到大约 450 分钟(我尚未确认)。因此，虽然这个答案有效，但我仍在寻找更快的方法。

SELECT p.id, p2.id, ROUND(AVG(1000 + ABS(p2.ss - p.ss)/6.1 * -50)) AS sim_score FROM 

(SELECT * FROM (
SELECT p.id, 
    (CASE p.id
    WHEN @curPit 
    THEN @curRow := @curRow + 1 
    ELSE @curRow := 1 AND @curPit := p.id END) AS rank
FROM pitches p, (SELECT @curRow := 0, @curPit := '') r 
ORDER BY  p.id, p.p_date DESC) n WHERE rank <= 1000) p, 

(SELECT * FROM (
SELECT p.id, 
    (CASE p.id
    WHEN @curPit 
    THEN @curRow := @curRow + 1 
    ELSE @curRow := 1 AND @curPit := p.id END) AS rank
FROM pitches p, (SELECT @curRow := 0, @curPit := '') r 
ORDER BY  p.id, p.p_date DESC) n WHERE rank <= 1000) p2

WHERE p.id < p2.id
GROUP BY p.id, p2.id