从单个查询获取聚合结果

Question

在下面的示例中，我对同一个表查询了2次。第二个查询是left join内部的嵌套查询，但查询的是同一个表。唯一的区别是增加了聚合函数count，其结果由外部查询使用。有更好的方法来解决这个问题吗?

select sm.student_id, sm.marks, smarks.d as d_marks from student_marks as sm 
left join(
select m.student_id, count(distinct m.marks) as d from student_marks as m group by m.student_id
) as smarks on smarks.student_id = sm.student_id;

是否可以在单个查询中做到这一点而不使用left join.

Answer 1

是的，有一种替代方法是使用窗口函数。没有办法在窗口函数中执行COUNT(DISTINCT，但是您可以使用DENSE_RANK()两次执行此操作，一次按您想要的升序计数排序，一次降序排序，将这些加在一起然后拿走一个:

SELECT sm.student_id, 
sm.marks, 
DENSE_RANK() OVER(PARTITION BY sm.student_id ORDER BY sm.marks DESC) + 
DENSE_RANK() OVER(PARTITION BY  sm.student_id ORDER BY sm.marks ASC) - 1 AS d_marks 
FROM   student_marks AS sm 

注意:不能因为少引用表一次就保证执行得更好。

为了解释DENSE_RANK()技巧，考虑一个简单的数据集:

tbody> <<tr>12

marks	dense_rank ASC	dense_rank DESC
1	1	3
	1	3
	2	2
3	3	1