Geek asks and answers

SQL: finding differences between rows

I want to count how many times each user has rows within the '5' of eachother.

For example, Don-501 and Don-504 should be counted, and Don-501 and Don-1600 should not be counted.

Start:

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

Finish:

Name            difference_5
__________      _____________
Don             1
James           0
Sarah           2
Becca           0
+5
source share
4 answers

Use the ABS () function in conjunction with the self-join in the subquery:

So something like:

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

edited according to comment by Andreas.

+2
source

Assuming each pair is name→ valueunique, this will give you the number of times the value is within 5 per name :

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

As you have noticed, we must also exclude pairs that are equal.

, 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 Demo .

+1

Since the OP also wants a zero count, we need an independent left join. Additional logic is necessary if one person has two exactly the same values, they should also be taken into account only once.

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
        ;

RESULT:

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

NOTE: sorry for the CTE, I did not see the mysql tag, I just liked the problem; -)

0
source 
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
0
source

More articles:


All Articles