SQL 中 DATE 的 RANK() 函数

我为此使用了 PopSQL。

请考虑以下事项： 我正在尝试使用 PayDate 对每个国家/地区进行排名。对于每个 PayDate，关联的国家不止一个。我的愿望是将每个国家与特定的 PayDate 或其计数分组，并相应地对它们进行排名。

CREATE TABLE Country
(
    CountryID INT PRIMARY KEY,
    CountryName VARCHAR(40)
);

CREATE TABLE State
(
    StateID INT PRIMARY KEY,
    StateName VARCHAR(40),
    CountryID INT,
    FOREIGN KEY(CountryID) REFERENCES Country(CountryID)
);

CREATE TABLE City
(
    CityID INT PRIMARY KEY,
    CityName VARCHAR(40),
    StateID INT,
    FOREIGN KEY(StateID) REFERENCES State(StateID)
);

CREATE TABLE Rooms 
(
    RoomID INT PRIMARY KEY,
    RoomTypeID INT,
    RoomBandID INT,
    RoomFacilityID INT,
    CityID INT,
    Floor INT,
    AddionalNotes VARCHAR(255),
    FOREIGN KEY(RoomTypeID) REFERENCES RoomType(RoomTypeID),
    FOREIGN KEY(RoomBandID) REFERENCES RoomBand(RoomBandID),
    FOREIGN KEY(RoomFacilityID) REFERENCES RoomFacility(RoomFacilityID),
    FOREIGN KEY(CityID) REFERENCES City(CityID)
);

CREATE TABLE DT_Date
(
    DateID INT NOT NULL,
    FullDate Datetime NOT NULL, 
    DateMonth INT NOT NULL, 
    Quarter INT NOT NULL,
    DateYear year NOT NULL, 
    PRIMARY KEY(DateID)
);

CREATE TABLE Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerForename VARCHAR(20),
    CustomerSurname VARCHAR(20),
    CustomerDOB DATetime,
    CustomerHomePhone INT,
    CustomerMobilePhone INT,
    CustomerWorkPhone INT,
    CustomerEmail VARCHAR(40),
    CityID INT,
    FOREIGN KEY (CityID) REFERENCES City(CityID)

);

CREATE TABLE Payments
(
    PaymentID INT PRIMARY KEY, 
    PaymentComment VARCHAR(255), 
    PaymentsMethodID INT, 
    PayDate Datetime,
    RoomID INT,
    DateID INT,
    CustomerID INT,
    Price INT,
    PaymentAmount INT,
    FOREIGN KEY(RoomID) REFERENCES Rooms(RoomID),
    FOREIGN KEY(PaymentsMethodID) REFERENCES PaymentsMethod(PaymentsMethodID),
    FOREIGN KEY(DateID) REFERENCES DT_Date(DateID),
    FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);

另请查看我的最后一次尝试：

select 
    rank() over (PARTITION BY CountryName order by count(PayDate)),
    CountryName, PayDate, count(Paydate)
from 
    City, Payments, Rooms, Customer, State, Country, DT_Date
where
    Payments.PayDate >= "2010-00-00 00:00:00" 
    and Payments.CustomerID = Customer.CustomerID
    and State.CountryID = Country.CountryID
    and City.StateID = State.StateID
    and Customer.CityID = City.CityID
    and Payments.DateID = DT_Date.DateID
group by 
    CountryName, PayDate;

查询有效。但是，结果不正确，因为英国有两个 PayDate，美国有一个 PayDate。以下是显示的结果：

Rank CountryName         PayDate       count(Paydate)
1       UK        2015-12-31 00:00:00    4
1       UK        2014-06-10 00:00:00    4
1       USA       2011-11-25 00:00:00    4

预期输出：

Rank CountryName        
1       UK        

2       USA