查找不同行之间列的最大差异

Question

给定以下数据：

课程编号	教师编号	最低分数	最高分数
1	1	7	8
2	2	5	6
3	2	5	7
4	3	5	8
5	3	6	7
6	4	5	6
7	4	4	6
8	4	5	7

使用Postgresql，我需要编写一个查询来：

选择所有教授过2门不同的课程X和Y的老师，其中课程X的最低成绩与课程Y的最高成绩之间的差异大于2。

因此，只有4号教师应该被选中。

我尝试了以下内容：

SELECT Teacher_ID, MAX(Maximum_grade), MIN(Minimum_grade)
FROM dataset
GROUP BY Teacher_ID
HAVING count(Teacher_ID) > 1 AND (MAX(Maximum_grade) - Min(Minimum_grade)) > 2;

这选择了教师3和4，这不应该发生。

我猜这是因为我写的代码将课程4的最低成绩与同一课程的最高成绩进行了比较，这是不应该发生的。

我不知道如何改进我的代码以获得所需的结果，任何帮助都将不胜感激。

- Patrick

---

老师4不符合最后一个条件，为什么应该被选中？ - RomanPerekhrest

嗨 @Patrick，你需要用 Python 的解决方案吗？ - R. Baraiya

@RomanPerekhrest 对于第四位老师，课程7的最低分和课程8的最高分相差3分，因此应该选择它。 - Patrick

你想要多少关于这个问题的答案用SQL来回答，多少用Python来回答？如果这个问题完全可以用SQL来回答，那可以吗？如果可以用Python来对你最初的查询进行后处理来回答这个问题，那可以吗？ - JonSG

你的查询失败的原因是，它检查的是 max_grade(X)-min_grade(X) > 2 而不是根据你的要求检查 max_grade(X)-min_grade(Y) > 2。 - vicki

@JonSG 我刚刚再次查看了任务要求。它需要完全使用SQL完成。对于我之前的评论造成的任何困惑，我感到抱歉。 - Patrick

Answer 1

我会通过两次从您的表中选择来解决这个问题，第一次着眼于查找最低成绩，第二次则是查找最高成绩。当我们将它们合并在一起时，我们希望为共享其ID的教师进行连接，但要确保课程具有不同的ID：

SELECT
    a.Teacher_ID,
    MAX(b.Maximum_grade - a.Minimum_grade) AS diff
FROM
    dataset as a JOIN
    dataset as b ON
    (
       a.Teacher_ID = b.Teacher_ID AND
       a.Course_ID != b.Course_ID
    )
GROUP BY
    a.Teacher_ID
HAVING
    MAX(b.Maximum_grade - a.Minimum_grade) > 2

你可以在这里尝试，但我不知道这些代码片段会被保存多久：http://sqlfiddle.com/#!9/1c4a72/1

- JonSG

---

这个解决方案看起来比我的更干净。我没有想到要做一个自连接。 - vicki

Answer 2

这个查询仍然可以优化或转换成子查询。这是我想到的方案。它由两部分组成，首先我们需要满足两个条件：

1. 所有授课了两门不同的课程 X 和 Y 的教师
2. X 课程的最低分与 Y 课程的最高分之间的差异大于 2。

select teacher_id, max(max_g) as max_g, min(min_g) as min_g from dataset 
group by teacher_id 
having count(distinct course_id) >= 2 and (max(max_g)- min(min_g)) > 2

以上查询根据上述条件检索记录，但存在一些矛盾。矛盾在于有时会在同一门课程内计算差异，即

课程X的最高成绩-课程X的最低成绩

为了纠正这个问题，我只选择那些最高分和最低分不来自同一行的记录（因此这些课程也不同）。

with diff_gt_two as
(   
   select 
        teacher_id, 
        max(max_g) as max_g, min(min_g) as min_g 
   from dataset     
   group by teacher_id 
   having count(distinct course_id) >= 2 and 
   (max(max_g)- min(min_g)) > 2
)
select dataset.teacher_id from dataset,diff_gt_two
where 
     dataset.teacher_id = diff_gt_two.teacher_id 
     and 
     ( dataset.max_g = diff_gt_two.max_g or     dataset.min_g = 
       diff_gt_two.min_g) 
group by dataset.teacher_id
having count(*) > 1

编辑： @JonSG已将CTE转换为子查询，fiddle链接：http://sqlfiddle.com/#!9/1c4a72/9。 感谢JonSG

- vicki

---

列 dataset.max_g 是从哪里来的？ - JonSG

好的，问题中提到的原始数据表是我命名为dataset的那个。 - vicki

是的，那个表格没有名为 max_g 的列 :-) - JonSG

哦，我随便把maximum_grade缩短成max_g，minimum_grade缩短成min_g了。 - vicki

注意：如果纠正缩短的表名，该解决方案似乎也有效：http://sqlfiddle.com/#!9/1c4a72/9 - JonSG

Answer 3

你可以将数据集与自身交叉连接，以相同的教师和不同的课程为交点，并从此笛卡尔积中获取每个教师的最大差异。

SELECT A.Teacher_ID, max(A.Maximum_grade-B.Minimum_grade) as Diff
FROM dataset A,dataset B 
WHERE A.Teacher_ID = B.teacher_ID and A.Course_ID <> B.Course_ID
GROUP BY A.Teacher_ID
HAVING Diff > 2;

由于连接条件要求不同的课程，只有一个课程的教师将不会出现，而同一门课程中成绩的差异也将被排除。

- Alain T.

---

这不是和我的回答本质上一样吗？ - JonSG

是的！除了使用的语法不同，它是一样的（我在发布时没有注意到）。 - Alain T.

Answer 4

SELECT *
FROM (
    SELECT Teacher_ID , max(diff) as DIFF, COUNT(Course_ID) as NUMBER_OF_COURSE
    FROM (
        SELECT *, (Maximum_grade  - Minimum_grade) AS diff
        FROM dataset
    )
    GROUP BY Teacher_ID
)
WHERE DIFF > 2

结果 = 授课教师ID 3，难度系数3，课程数量2

- Burak Altınışık

---

这个解决方案不会显示每门课程的最高和最低成绩之间的最大差异吗？如果是这样，那么它会选择教师3而不是教师4，对吗？ - Patrick

根据我理解的问题和评论，我已经编辑了代码。感谢您的贡献。 - Burak Altınışık

这确实会产生你所说的结果，但那不是OP所要求的结果。他们想要的不是第三个老师，而是第四个老师作为结果。 - JonSG

Answer 5

使用Python

DF：

import pandas as pd
df = pd.DataFrame({'CID': [1,2,3,4,5,6,7,8,8],
                   'TID': [1,2,2,3,3,4,4,4,4],
                   'Min': [7,5,5,5,6,5,4,5,6],
                   'Max': [8,6,7,8,7,6,6,7,7]})

代码：

ans = []   #Creating new list where we will store our result

#Creating new list with the teachers who attend two or more thn two courses
TID = [k for k, v in df.groupby(['TID'])['CID'].count().to_dict().items() if v>=2]



for T in TID:    #Loop Over Teachers

    #Lets just select the Min value row
    d = df.loc[df.loc[df['TID']==T][['Min']].idxmin().values[0]]

    #Lets add Max to row, while filtering just not select the Min CID here
    d['Max'] = max(df.loc[(df['TID']==T) & (df['CID']!=d['CID'])]['Max'].tolist())


    if(d['Max'] - d['Min']> 2):
         ans.append({T:  d['Max'] - d['Min']})
    
ans

输出：

[{4: 3}]  ### CID : DIff

- R. Baraiya