sql/hiveql:有没有一种方法可以在没有groupby的情况下计算每行的join< table>匹配数?

p3rjfoxz  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(225)

我想对两个表进行左(外)联接,并在结果表中有一列指示右表中的记录满足左表的联接条件的次数。
考虑下表:

Students          Absences
name    ID        ID   date  
Kyle    1         1    7/04
Chris   2         2    7/04
Sam     3         2    7/20
Maura   4         1    8/01
                  3    8/02
                  1    8/02

我想创建一个新表来显示一个学生在缺勤表中出现的次数。生成的表如下所示:

name     ID   absences
Kyle     1    3
Chris    2    2
Sam      3    1
Maura    4    0

我知道解决方案可以是:

SELECT Students.name, Student.ID, t1.ct as absences
FROM Students
LEFT JOIN (SELECT ID, count(*) as ct
           FROM Absences
           GROUP BY ID) t1
ON Students.ID = t1.ID

…但我希望在解决方案中避免使用group by,因为我要处理数百万行,group by会降低效率。我搜索了一个sql/hiveql操作符,它返回特定行满足连接条件的次数,但找不到任何结果。
我考虑的另一个解决方案是每次满足连接时都有一个值增量,但我想不出一种方法来实现这一点。
任何不涉及聚合函数的有用线程或解决方案都将是惊人的。谢谢。

7cwmlq89

7cwmlq891#

使用analytic count()计算每个student.id的联合缺勤次数:

SELECT s.name, s.ID, count(a.id) over(partition by s.ID) as absences
FROM Students s LEFT JOIN 
     Absences a
     ON s.ID = a.ID
;

如果学生和缺勤之间有1:m的关系,那么在join之后您将得到重复的行。在这种情况下,groupby是正确的解决方案,最好在子查询中这样做,以便在join之前按student\u id聚合缺勤情况,这样join将收到较少的行,如问题示例中所示。

ffscu2ro

ffscu2ro2#

不使用子查询通常更简单:

SELECT s.name, s.ID, count(a.id) as absences
FROM Students LEFT JOIN 
     Absences a
     ON s.ID = a.ID
GROUP BY s.name, s.ID;

相关问题