按多种条件订购竞争对手

Question

我使用了一个具体但假设的示例。

考虑一个包含射击比赛结果的数据库，每个参赛者都进行了几个系列的射击。DB包含3个表：竞争对手、系列和镜头。

竞争对手：

id	名称
1	A
2	B

您应该能够非常简单地做到这一点，因为您的需求可以通过正常的聚合和窗口函数来完成。

对于每个级别的订购：

• "所有系列的总分">可以通过对所有分数求和来满足
• "中心命中数(中心命中得分为10分(">可以满足条件计数
• 要按每个系列按日期向后排序，我们可以使用STRING_AGG聚合每个系列的总分(我们使用窗口函数计算(，并按日期(或id(对聚合进行排序。然后，如果我们通过该聚合对最终查询进行排序，那么后面的系列将首先进行排序。

  此方法允许您按任意数量的序列排序，而不是按其他答案排序。

尚不清楚如何定义"；稍后的"；以及"；较早的"；因为您没有日期列，但我使用了series.id作为代理。

SELECT
comp.name,
SUM(shots.score) as totalScore,
COUNT(CASE WHEN shots.score = 10 THEN 1 END) AS centerHits,
STRING_AGG(NCHAR(shots.MaxScore + 65), ',') WITHIN GROUP (ORDER BY series.id DESC) as AllShots
FROM (
SELECT *,
SUM(shots.score) OVER (PARTITION BY shots.serieID) MaxScore
FROM Shots shots
) shots
INNER JOIN Series series ON series.id = shots.serieId
INNER JOIN Competitors comp ON comp.id = series.competitorId
GROUP BY
comp.id,
comp.name
ORDER BY
totalScore DESC,
centerHits DESC,
AllShots DESC;

请注意，按名称分组时，还应将主键添加到GROUP BY中，因为名称可能不是唯一的。

---

类似但稍微复杂一点的查询是在派生表中预聚合shots。这可能比使用窗口函数执行得更好。

SELECT
comp.name,
SUM(shots.totalScore) as totalScore,
SUM(centerHits) AS centerHits,
STRING_AGG(NCHAR(shots.totalScore + 65), ',') WITHIN GROUP (ORDER BY series.id DESC) as AllShots
FROM (
SELECT
shots.serieId,
SUM(shots.score) as totalScore,
COUNT(CASE WHEN shots.score = 10 THEN 1 END) AS centerHits
FROM Shots shots
GROUP BY
shots.serieId
) shots
INNER JOIN Series series ON series.id = shots.serieId
INNER JOIN Competitors comp ON comp.id = series.competitorId
GROUP BY
comp.id,
comp.name
ORDER BY
totalScore DESC,
centerHits DESC,
AllShots DESC;

db<gt；小提琴

似乎需要一个多级查询，每个查询都建立在前一个查询的基础上。

别名为PQ的INNER-MOST查询是对每个序列ID的简单求和，它可以获得每个相应集合的中心命中总数和总点数。类似于你的计数。

从中，你需要知道哪一个系列是最新的(最新的(，并在之前一次又一次地倒退。通过使用OVER/PPARTITION，我加入到系列表中，以在我参与时获得竞争对手ID和名称。

通过根据每个竞争对手对数据进行分区，并根据序列号DESCENDING应用顺序，我得到的行号将使最近的row_number((分别变为1、2和3，这样对于序列号为1、然后是2、然后是3的竞争对手A来说，将具有最终的"；MostRent"；列分别为3、2和1，因此序列号3=1——最新的，序列号1=3——OLDEST系列或竞争对手。

类似地，对于第二个竞争对手B，序列ID 4、5和6分别变为3、2、1。所以现在，你有了一个基础来知道什么是最新的(1=最新的(，倒数第二个(2=倒数第二最近的(，以及倒数第三个(3…(

现在这两个部分都设置好了，我可以对各自的总数、中心命中率进行求和，现在可以清楚地知道最近的(1(是什么，倒数第二的(2(和倒数第三的(3(是什么。这些由.添加到组中

现在，如果一个参赛者有6个射击系列赛，而另一个有4个系列赛(不是说这会发生在真正的比赛中，而是为了理解上下文(，那么6个系列赛的最新成绩将为MostRenet=1，类似于4个系列，第四个系列赛将为MosdRenet=1。

因此，在最后一组中，通过竞争级别，您可以评估所有有问题的部分。

select
F.Name,
F.CompetitorID,
sum( F.SeriesTotalScore ) TotalScore,
sum( F.CenterHits ) CenterHits,
sum( case when F.MostRecent = 1 
then F.SeriesTotalScore else 0 end ) MostRecentScore,
sum( case when F.MostRecent = 2 
then F.SeriesTotalScore else 0 end ) SecondToMostRecentScore,
sum( case when F.MostRecent = 3 
then F.SeriesTotalScore else 0 end ) ThirdToMostRecentScore
from
( select
c.Name,
Se.CompetitorID,
PQ.SerieId,
PQ.CenterHits,
PQ.SeriesTotalScore,
ROW_NUMBER() OVER( PARTITION BY Se.CompetitorID 
order by PQ.SerieId DESC) AS MostRecent
from
( select
s.serieId,
sum( case when s.score = 10 then 1 else 0 end ) as CenterHits,
sum( s.Score ) SeriesTotalScore
from
Shots s
group by
s.SerieID ) PQ
Join Series Se
on PQ.SerieID = se.id
JOIN Competitors c
on Se.CompetitorID = c.id 
) F
group by
F.Name,
F.CompetitorID
order by
sum( F.SeriesTotalScore ) desc,
sum( F.CenterHits ),
sum( case when F.MostRecent = 1 
then F.SeriesTotalScore else 0 end ) desc,
sum( case when F.MostRecent = 2 
then F.SeriesTotalScore else 0 end ) desc,
sum( case when F.MostRecent = 3 
then F.SeriesTotalScore else 0 end ) desc