问题:
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