按组排列行的查询

Question

我正在使用 Apache Derby 10.10。

我有一个参与者列表，并想要计算他们在其所属国家的排名，如下：

|        Country |         Participant | Points | country_rank |
|----------------|---------------------|--------|--------------|
|      Australia |      Bridget Ciriac |      1 |            1 |
|      Australia |     Austin Bjorklun |      4 |            2 |
|      Australia |        Carrol Motto |      7 |            3 |
|      Australia |     Valeria Seligma |      8 |            4 |
|      Australia |     Desmond Miyamot |     27 |            5 |
|      Australia |      Maryjane Digma |     33 |            6 |
|      Australia |       Kena Elmendor |     38 |            7 |
|      Australia |         Emmie Hicke |     39 |            8 |
|      Australia |        Kaitlyn Mund |     50 |            9 |
|      Australia |    Alisia Vitaglian |     65 |           10 |
|      Australia |          Anika Bulo |     65 |           11 |
|             UK |          Angle Ifil |      2 |            1 |
|             UK |     Demetrius Buelo |     12 |            2 |
|             UK |      Ermelinda Mell |     12 |            3 |
|             UK |         Adeline Pee |     21 |            4 |
|             UK |     Alvera Cangelos |     23 |            5 |
|             UK |   Keshia Mccalliste |     23 |            6 |
|             UK |        Alayna Rashi |     24 |            7 |
|             UK |    Malinda Mcfarlan |     25 |            8 |
|  United States |     Gricelda Quirog |      3 |            1 |
|  United States |      Carmina Britto |      5 |            2 |
|  United States |         Noemi Blase |      6 |            3 |
|  United States |        Britta Swayn |      8 |            4 |
|  United States |        An Heidelber |     12 |            5 |
|  United States |        Maris Padill |     21 |            6 |
|  United States |     Rachele Italian |     21 |            7 |
|  United States |   Jacquiline Speake |     28 |            8 |
|  United States |      Hipolito Elami |     45 |            9 |
|  United States |          Earl Sayle |     65 |           10 |
|  United States |       Georgeann Ves |     66 |           11 |
|  United States |       Conchit Salli |     77 |           12 |

该模式看起来像这样（sqlfiddle）：

create table Country(
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  name varchar(255),
  PRIMARY KEY (id)
);

create table Team(
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  country_id int not null,
  PRIMARY KEY (id),
  FOREIGN KEY (country_id) REFERENCES Country(id)
);

create table Participant(
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  team_id int not null,
  name varchar(100),
  points int,
  PRIMARY KEY (id),
  FOREIGN KEY (team_id) REFERENCES Team(id)
);

这是我尝试过的方法：

select
    Country.name,
    Participant.name,
    Participant.points,
    ROW_NUMBER() OVER(order by Country.name, Participant.points) as country_rank
from Country
join Team
    on Country.id = Team.country_id
join Participant
    on Team.id = Participant.team_id;

但根据Apache Derby文档，OVER()语句不接受任何参数。

是否有人知道如何实现国家排名？

- Fidel

Answer 1

SQL

SELECT c.name AS Country,
       p.name AS Participant,
       p.points AS Points,
       (SELECT COUNT(*)
        FROM Participant p2
        JOIN Team t2 ON p2.team_id = t2.id
        WHERE t2.country_id = t.country_id
          AND (p2.points < p.points
               OR p2.points = p.points AND p2.name <= p.name)) AS country_rank
FROM Country c
JOIN Team t ON c.id = t.country_id
JOIN Participant p ON t.id = p.team_id
ORDER BY c.name, p.points, p.name;

在线演示

SQL Fiddle演示：http://sqlfiddle.com/#!5/f48f8/14

解释

一个简单的ANSI-SQL子查询可以用来完成相同国家参赛者记录数的计算，这些参赛者的得分要么更低，要么得分相同但姓名按字母顺序不高于当前选手。

- Steve Chambers

---

向你致敬！你处理了这个平局更好！ - Parfait

Answer 2

考虑一个非Windows函数SQL查询，它使用相关的聚合计数子查询。由于分组列（Country.name）不在与排名标准（Participant.points）相同的表中，我们需要在子查询中运行相同的连接，但重命名表别名以正确比较内部和外部查询。

现在当然，在完美的世界中，这就是全部，但我们现在必须考虑并列的分数。因此，第二个非常类似的子查询（用于打破平局）将添加到第一个子查询中。这个第二个嵌套查询匹配内部和外部查询的Country.name和Participant.points，但按Participant.name的字母顺序排序。

SELECT
    Country.name AS Country,
    Participant.name AS Participant,
    Participant.points,
    (SELECT Count(*) + 1
       FROM Country subC
      INNER JOIN Team subT
              ON subC.id = subT.country_id
      INNER JOIN Participant subP
              ON subT.id = subP.team_id
      WHERE subC.name = Country.name
        AND subP.points < Participant.points) 

     +

    (SELECT Count(*)
       FROM Country subC
      INNER JOIN Team subT
              ON subC.id = subT.country_id
      INNER JOIN Participant subP
              ON subT.id = subP.team_id
      WHERE subC.name = Country.name
        AND subP.points = Participant.points
        AND subP.name < Participant.name)  As country_rank

FROM Country
INNER JOIN Team
        ON Country.id = Team.country_id
INNER JOIN Participant
        ON Team.id = Participant.team_id
ORDER BY Country.name, Participant.points;

- Parfait

Answer 3

你只需要按国家进行分区，就可以得到你所需的结果。

SELECT
Country.name,
Participant.name,
Participant.points,
ROW_NUMBER() OVER(PARTITION BY country order by Country.name, Participant.points) as country_rank
from Country
join Team
  on Country.id = Team.country_id
join Participant
  on Team.id = Participant.team_id;

- viejoEngineer

---

谢谢JVM，PARTITION BY在Apache Derby中可行吗？我已经尝试过了，但它显示“在第x行，第y列遇到PARTITION”。 - Fidel

我对Apache Derby不是很熟悉，但我进行了谷歌搜索并发现它们确实存在于Apache Derby中。这是链接https://wiki.apache.org/db-derby/OLAPOperations - viejoEngineer

不幸的是，该维基页面描述了构建此功能的设计提案，但并非所有提案都得到了实现。 - Bryan Pendleton