查询以输出每个类型的最大电影票

Question

我正在尝试找出每种类型的电影观看次数最多的客户。查询将输出customerId和movieGenre，表示他们在记录的所有客户中观察最多的客户。如果某个movieGenre没有被任何人查看，则会从输出中忽略它。如果某个movieGenre绑定了客户，则会输出所有绑定的客户。

以下是模式：

CREATE TABLE customers ( 
    customerId      INTEGER PRIMARY KEY,
    customerName    VARCHAR(20),
    city            CHAR(20)     
);

CREATE TABLE movies (  
    movieId         INTEGER PRIMARY KEY,
    movieGenre      VARCHAR(20),
    moviePrice      NUMERIC(10,2)
);

CREATE TABLE tickets (    
    ticketId      INTEGER PRIMARY KEY,
    ticketDate    DATE,        
    customerId    INTEGER NOT NULL REFERENCES customers
);

CREATE TABLE details (    
    ticketId            INTEGER REFERENCES tickets,  
    movieId             INTEGER REFERENCES movies, 
    numOfTickets        SMALLINT,
    PRIMARY KEY (ticketId, movieId)
);

CREATE TABLE transactions (   
    transactionId       SERIAL PRIMARY KEY,
    ticketId            INTEGER NOT NULL UNIQUE REFERENCES tickets,
    transactionAmount   NUMERIC(8,2)    
);

CREATE TABLE Payments (   
    paymentId       INTEGER PRIMARY KEY,
    paymentAmount   NUMERIC(8,2) CHECK ( amount > 0 ),
    transactionId   INTEGER NOT NULL REFERENCES transactions
);

这是我的问题：

select m.movieGenre, max(c.customerId) 
from movies m join details d 
    on m.movieId = d.movieId join tickets t 
    on d.ticketId = t.ticketId join customers c 
    on t.customerId < c.customerId 
group by m.movieGenre;

我的查询输出所有电影类型的96，这是不正确的。有人能帮我解决这个问题吗？

Answer 1

您需要获取数据中所有配对的客户/类型计数。我认为这是：

select m.movieGenre, t.customerId, count(*)
from movies m join
     details d 
     on m.movieId = d.movieId join
     tickets t 
     on d.ticketId = t.ticketId 
group by m.movieGenre, t.customerId;

然后，要获取每个组的最大值，请使用distinct on

select distinct on (m.movieGenre) m.movieGenre, t.customerId, count(*)
from movies m join
     details d 
     on m.movieId = d.movieId join
     tickets t 
     on d.ticketId = t.ticketId 
group by m.movieGenre, t.customerId
order by m.movieGenre, count(*) desc;