Select top 4 scores by person but need at least two locations

问题: I have data such as eventId locationId score athlete 8739 73 48 matt 8734 73 46 matt 8788 73 45 matt 8738 73 44 matt 8...

问题:

I have data such as

eventId locationId score athlete
8739    73          48  matt
8734    73          46  matt
8788    73          45  matt
8738    73          44  matt
8787    73          44  matt
8735    73          43  matt
8789    6           43  matt

I need to capture the top 4 scores by person, but at least 1 of the top 4 scores needs to be from a different locationId than the other 3

In this case I would want this returned

eventId locationId score athlete
8739    73          48  matt
8734    73          46  matt
8788    73          45  matt
8789    6           43  matt

I've tried writing out queries that would use a GROUP BY HAVING MIN(locationId) != MAX(locationId) but I'm not sure how to accomplish that while also doing an ORDER BY and LIMIT.

I've also tried doing a self-join, but I'm not sure how to return the top results based on s.score and score2.

Start of a self-join that seems on the right track

SELECT s.eventid, s.locationid, athlete, score
, s2.eventid, s2.locationid, s2.athlete, score score2
FROM singles s
  INNER JOIN singles s2 ON s.athlete = s2.athlete AND s.locationid != s2.locationid
WHERE s.athlete = 'matt'
ORDER BY score DESC;

回答1:

So, what you really want are the top three scores and then the first score after that that guarantees at least two locations.

This is a rather hard condition, but I think this does the trick:

with s as (
      select t.*,
             row_number() over (partition by athlete order by score desc) as seqnum
      from t
     ),
     s3 as (
      select s.*
      from s
      where seqnum <= 3
     )
select *
from s3
union all
(select s.*
 from s
 where ( (select count(distinct locationid) from s3) > 1 and seqnum = 4 ) or
       ( (select count(distinct locationid) from s3) = 1 and
         seqnum = (select min(seqnum)
                   from s
                   where locationid not in (select locationid from s3)
                  )
       )
);

Here is a db<>fiddle.


回答2:

You may use row_number analytic function and limit clause including a self-join as the below one

select locationId, score, athlete
 from
   (
    select locationId, score, athlete, rn, rn2
      from(
            select *
              from
              (
                 with singles(locationId, score, athlete) as
                 (
                  select 73, 48, 'matt' union all
                  select 73, 46, 'matt' union all
                  select 73, 45, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 43, 'matt' union all
                  select  6, 43, 'matt'     
                 )
                 select row_number() over (partition by s.locationId order by s.score desc) as rn,
                        row_number() over (partition by s2.locationId order by s2.score desc) as rn2,
                        s.athlete, s.locationId, s.score
                   from singles s join singles s2 
                     on s.score = s2.score
                  where s.athlete = 'matt'
               ) q1
               order by score desc, rn, rn2 
            ) q2 
      group by locationId, score
     having sum(rn) <= sum(rn2)
      order by rn, score desc
      limit 4
   ) q3
 order by score desc

dbfiddle.uk demo

  • 发表于 2018-12-29 02:09
  • 阅读 ( 232 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除