SQL - 获取按列分组的计数,但也选择组的顶部项目
我想弄清楚正确的sql来完成一些半复杂的事情。我在这个特定的场景中使用postgres,但是可以用于其他平台的sql语句也会受到赞赏。SQL - 获取按列分组的计数,但也选择组的顶部项目
我有表称为用户和响应。用户将完成活动,并且对于活动中的每次尝试,我们都会跟踪用户的回复,以及该回复是否导致活动的合格分数。
相关表和列:
用户:ID(INT),用户名(VARCHAR),classroom_id(INT),类型(VARCHAR)
对策:ID(INT),ACTIVITY_ID(INT ),USER_ID(INT),is_passing_score(布尔)
活动:ID(INT),类型(VARCHAR),名(为varchar)
我想什么能够从这些表拉是ACTIVITY_ID ,尝试次数每项活动以及活动是否通过。
select activity_id, count(activity_id)
from (
select *
from responses R
inner join users U on U.id = R.user_id
where U.classroom_id = '114' AND U.type = 'Student'
group by R.activity_id, R.id, U.id
order by activity_id, is_passing_score DESC
) as foo
group by activity_id
order by activity_id
上面的查询会得到我尝试的ACTIVITY_ID和计数,但是我努力也得到任何答复是否导致了传递活动(通过is_passing_score列)。如果任何响应将is_passing_score设置为true,则情况会如此。
我也尝试过这样的事情,我认为可以让我知道我是否要通过活动的真/假值。
select rn, *
from (
select row_number() OVER(PARTITION BY activity_id ORDER BY is_passing_score DESC) AS rn, *
from responses R
inner join users U on U.id = R.user_id
where U.classroom_id = '114' AND U.type = 'Student'
group by R.activity_id, R.id, U.id
order by activity_id, is_passing_score DESC
) as foo
WHERE rn = '1'
order by activity_id
我想查询将由is_passing_score责令子查询,然后抢只是最上面的一个,而如果其真正的将是,否则,如果它只是假什么。如果第二个查询实际上是正确的,那么我的大问题就是获取count和true/false值。我觉得我很接近它只是一个能够进行行选择并在一个查询中计数的问题。
我希望我的最终结果是activity_id,user_id,尝试次数以及是否通过的列表。
- ACTIVITY_ID,USER_ID,传递尝试
- 17,3069,T,2
- 18,3069,T,3
- 151,3069,T,1
- 152,3069, T,4
我想你想是这样的:
SELECT r.activity_id, count(*) AS num_attempts,
CAST(MAX(CAST(is_passing_score AS INT)) AS BOOL) AS has_passed
FROM responses r INNER JOIN
users u
ON u.id = r.user_id
WHERE u.classroom_id = 114 AND u.type = 'Student'
GROUP BY r.activity_id;
我试过,但是max(is_passing_score)失败,因为is_passing_score是一个布尔值。确切的错误:“函数max(boolean)不存在” –
实际上,如果我将is_passing_score转换为可能工作的int –
样本数据和期望的结果真的有帮助。 –
如果有必要,我可以尝试提供更多示例数据,但不确定最好的方法。 –