CODEWITHSUNDEEP
mysqlsql
IrfanRaza
IrfanRaza

Reputation: 3058

SQL query to get user dis-agreements

I have some analytical data for different cases. Each case is associated with one or more photos. Each photo is analyzed by two users. The stored data looks like Source data

What I want is to have SQL query to generate dis-agreement result as shown below

EITHER dis-agreement list-1

OR dis-agreement list-2

Can somebody help me out to achieve this.

Thanks for sharing your valuable time.

Here is sample data to test with 

Case Id,Photo Id,FeatureCheck,Result,CheckedBy 
17116,173442,severity,none,24 
17116,173442,severity,low,25 
17116,175062,severity,none,26 
17116,175062,severity,none,27 
17116,175427,severity,medium,24 
17116,175427,severity,high,28 
17116,175748,severity,low,22 
17116,175748,severity,none,30 
17116,176031,severity,low,24 
17116,176031,severity,low,29 
17277,175309,severity,none,24 
17277,175309,severity,none,25 
17277,175649,severity,none,24 
17277,175649,severity,none,25

Upvotes: 0

Views: 103

Answers (4)

collapsar
collapsar

Reputation: 17238

Approach

Key to the suggested solution is that you always have exactly 2 reviews to each combination of case, photo and feature.

Grouping by said combination you'll have groups with size of 2. Thus you get all users and results within a group by applying MIN/MAX to result/ checkedby.

What you need to check now is whether the minimum value of the user id and the minimum value of the result belong to the same record within a group. If they do, you're finished, otherwise you'd have to swap either user id or result. Equivalently to swapping you'd have to apply MAX instead of MIN and vice versa for the result field.

The check can be performed by computing the ranks within a group according to the user id and the review result and check whether they match in a given record or not. Note that the result of the matching predicate is constant in any given group. Thus we can add this predicate to the grouping criteria.

Query 1 (requires Mysql 8)

    SELECT caseid
         , photoid
         , featurecheck
         , MIN(checkedby)   user1
         , CASE reversal WHEN 0 THEN MIN(result) ELSE MAX(result) END review1
         , MAX(checkedby)   user2
         , CASE reversal WHEN 0 THEN MAX(result) ELSE MIN(result) END review2
      FROM (
                SELECT t.*
                     , CASE WHEN RANK() OVER (PARTITION BY caseid, photoid, featurecheck ORDER BY result) = RANK() OVER (PARTITION BY caseid, photoid, featurecheck ORDER BY checkedby)
                            THEN 0
                            ELSE 1
                       END  reversal
                  FROM test t
           ) tbase
  GROUP BY caseid
         , photoid
         , featurecheck
         , reversal
    HAVING MIN(result) <> MAX(result)
         ;

reversal holds the prediacte mentioned in the Approach section, the having clause selects those cases where the reviews do not match.

Here is a db fiddle.

Query 2

The use of the RANK function missing from earlier mysql versions can be simulated.

    SELECT caseid
         , photoid
         , featurecheck
         , MIN(checkedby)   user1
         , CASE MIN(reversal) WHEN 0 THEN MIN(result) ELSE MAX(result) END review1
         , MAX(checkedby)   user2
         , CASE MIN(reversal) WHEN 0 THEN MAX(result) ELSE MIN(result) END review2
      FROM (
                SELECT t1.*
                     , CASE
                        WHEN
                               (t1.cmin =  t1.checkedby AND t1.rmin =  t1.result)
                            OR (t1.cmin <> t1.checkedby AND t1.rmin <> t1.result)
                        THEN 0
                        ELSE 1
                       END  reversal
                  FROM (
                            SELECT t.*
                                 , (SELECT MIN(checkedby) FROM test ts WHERE ts.caseid = t.caseid AND ts.photoid = t.photoid AND ts.featurecheck = t.featurecheck GROUP BY ts.caseid, ts.photoid, ts.featurecheck) cmin
                                 , (SELECT MIN(result)    FROM test ts WHERE ts.caseid = t.caseid AND ts.photoid = t.photoid AND ts.featurecheck = t.featurecheck GROUP BY ts.caseid, ts.photoid, ts.featurecheck) rmin
                              FROM test t
                       ) t1
           ) t2
  GROUP BY caseid
         , photoid
         , featurecheck
         , reversal
    HAVING MIN(result) <> MAX(result)
         ;

Here is a db fiddle.

Upvotes: 1

Mangesh Auti
Mangesh Auti

Reputation: 1153

You can use INNER JOIN

SELECT
 t1.`Case Id`,  t1.`Photo Id`,t1.FeatureCheck,t1.CheckedBy,t1.Result,t2.CheckedBy,t2.Result
FROM test t1
 JOIN test t2
ON (t1.`Case Id`=t2.`Case Id` 
    AND  t1.`Photo Id`= t2.`Photo Id` 
    AND t1.CheckedBy!=t2.CheckedBy 
    AND t1.CheckedBy<t2.CheckedBy)
WHERE t1.Result!=t2.Result;

DEMO

Upvotes: 1

Wim Hermans
Wim Hermans

Reputation: 2116

I would use a subquery to get the case_id & photo_id for which we have different results:

SELECT
    p.case_id,
    p.photo_id,
    p.feature_check,
    p.result,
    p.checked_by
FROM photos p
INNER JOIN (
    SELECT
        case_id,
        photo_id,
        count(distinct result)
    FROM
        photos
    GROUP BY
        case_id,
        photo_id
    HAVING
        count(distinct result) > 1
) sub ON p.case_id = sub.case_id AND p.photo_id = sub.photo_id
;

Upvotes: 0

HereGoes
HereGoes

Reputation: 1320

The HAVING clause can give you the results you show.

SELECT  caseid, photo_id , feature_check, agreedupon
FROM table1
GROUP BY caseid,  photo_id, feature_check, agreedupon
HAVING count(agreedupon) = 1

Upvotes: 0

Related Questions