SQL：查找行之间的差异

Question

我希望能计算每个用户的记录中，有多少次行之间距离小于或等于'5'。

例如：唐（Don）- 501 和唐（Don）- 504应被计算，而唐（Don）- 501和唐（Don）- 1600则不应被计算。

开始：

Name        value
_________   ______________
Don         1235
Don         6012
Don         6014
Don         6300
James       9000
James       9502
James       9600
Sarah       1110
Sarah       1111
Sarah       1112
Sarah       1500
Becca       0500
Becca       0508
Becca       0709

完成：

Name            difference_5
__________      _____________
Don             1
James           0
Sarah           2
Becca           0

- Don P

---

也许是我的眼花了，但你的数据似乎与文字描述不符......应该计算Don-501和Don-504，但我没有看到这些数值。 - Taryn

你能解释一下为什么Sarah的计数不是3吗？从1110到1111是1，从1111到1112是2，从1110到1112是3，对吗？或者你做法不是这样的？ - mikeY

Answer 1

使用ABS()函数，并结合子查询中的自连接：

因此，类似以下内容：

SELECT name, COUNT(*) / 2 AS difference_5
FROM (
  SELECT a.name name, ABS(a.value - b.value) 
  FROM  tbl a JOIN tbl b USING(name)
  WHERE ABS(a.value - b.value) BETWEEN 1 AND 5
) AS t GROUP BY name

根据Andreas的评论进行了编辑。

- Jeremy Smyth

---

我认为这将包括a和b的所有排列组合，也就是说，对于"Don 6012"和"Don 6014"，会有两行差为2。这些需要以某种方式进行过滤; 也许除以二？ - Andreas

MySQL 5.5 不支持 WHERE 子句中的 difference。 - Holger Brandt

我的错。通过添加反引号进行修复。 - Jeremy Smyth

@JeremySmyth，添加反引号是没有用的。你不能在WHERE子句中从SELECT子句中引用别名。你只需要将ABS(a.value - b.value)移到WHERE子句中，并完全从SELECT子句中删除它。 - Holger Brandt

糟糕，又出错了！我需要一个更好的SQL解析器在我的脑海中。 - Jeremy Smyth

Answer 2

假设每个name -> value对都是唯一的，这将为您获取值在5个每个名称内的计数：

SELECT    a.name, 
          COUNT(b.name) / 2 AS difference_5
FROM      tbl a
LEFT JOIN tbl b ON a.name = b.name AND 
                   a.value <> b.value AND
                   ABS(a.value - b.value) <= 5
GROUP BY  a.name

正如您所注意到的那样，我们还必须排除与自身相等的对。

但是，如果您想计算每个名称的值在表中任何值的范围内出现的次数（范围为5），您可以使用以下方法：

SELECT    a.name,
          COUNT(b.name) / 2 AS difference_5
FROM      tbl a
LEFT JOIN tbl b ON NOT (a.name = b.name AND a.value = b.value) AND
                   ABS(a.value - b.value) <= 5
GROUP BY  a.name

---

请查看SQLFiddle演示，了解两种解决方案。

- Zane Bien

Answer 3

因为 OP 还想要零计数，所以我们需要一个自我左连接。如果一个人有两个完全相同的值，那么额外的逻辑是必要的，这些值也只应该被计算一次。

WITH cnts AS (
        WITH pair AS (
                SELECT t1.zname,t1.zvalue
                FROM ztable t1
                JOIN ztable t2
                ON t1.zname = t2.zname
                WHERE ( t1.zvalue < t2.zvalue
                        AND t1.zvalue >= t2.zvalue - 5 )
                OR (t1.zvalue = t2.zvalue AND t1.ctid < t2.ctid)
                )
        SELECT DISTINCT zname
        , COUNT(*) AS znumber
        FROM pair
        GROUP BY zname
        )
, names AS (
        SELECT distinct zname  AS zname
        FROM ztable
        GROUP BY zname
        )
SELECT n.zname
        , COALESCE(c.znumber,0) AS znumber
FROM names n
LEFT JOIN cnts c ON n.zname = c.zname
        ;

结果：

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 14
 zname | znumber 
-------+---------
 Sarah |       3
 Don   |       1
 Becca |       0
 James |       0
(4 rows)

注意：抱歉使用了CTE，我没有看到mysql标签，只是喜欢这个问题;-)

- wildplasser

---

MySql支持CTE吗？我认为它没有这个功能。 - Taryn

我刚刚注意到了这一点。不过我仍然喜欢这个解决方案...(而且问题更或多或少是普遍存在的) - wildplasser

使用窗口函数（例如lag()）会更加容易。 - user330315

是的。如果您需要检测和计算“簇”（多个距离<5的值，例如{1,5,9,11}），问题会变得更好。我甚至会使用递归。顺便说一句：这是一个间隙和岛屿问题。应该重新标记。（也许删除mysql标记？;-）看起来像是家庭作业 - wildplasser

谢谢你的帮助，这是实际工作，不是家庭作业 :P 从未听说过间隙和岛屿，也许如果我学了计算机科学，这将是微不足道的 :) - Don P

Answer 4

SELECT
    A.Name,
    SUM(CASE WHEN (A.Value < B.Value) AND (A.Value >= B.Value - 5) THEN 1 ELSE 0 END) Difference_5
FROM
    tbl A INNER JOIN
    tbl B USING(Name)
GROUP BY
    A.Name

- Jazzy J