我对复杂的rank函数所需的SQL感到不知所措。这是一个赛车运动,我需要每一个排名的应用程序Entry
的Timesheet
基础上,进入的:total_time
。
相关型号:
class Timesheet
has_many :entries
end
class Entry
belongs_to :timesheet
belongs_to :athlete
end
class Run
belongs_to :entry
end
条目的:total time
不存储在数据库中。这是的计算列runs.sum(:finish)
。我使用Postgres(9.3)rank()
函数获取给定时间表的条目,并按此计算列对它们进行排名。
def ranked_entries
Entry.find_by_sql([
"SELECT *, rank() OVER (ORDER BY total_time asc)
FROM(
SELECT Entries.id, Entries.timesheet_id, Entries.athlete_id,
SUM(Runs.finish) AS total_time
FROM Entries
INNER JOIN Runs ON (Entries.id = Runs.entry_id)
GROUP BY Entries.id) AS FinalRanks
WHERE timesheet_id = ?", self.id])
end
到目前为止,一切都很好。这将返回带有rank
属性的输入对象,可以在上显示该属性timesheet#show
。
现在是棘手的部分。在上Timesheet
,并非每个Entry
人都有相同的跑步次数。有一个临界点(通常是前20名,但并非总是如此)。这使得Postgres的rank()不准确,因为某些参赛作品的参赛者人数低于:total_time
比赛获胜者的人数,因为他们没有为第二轮比赛设定分界线。
我的问题:是否可以rank()
在内执行类似的操作rank()
以生成类似于下图的表?还是有另一种首选的方式?谢谢!
注意:我将时间存储为整数,但为了简化起见,在下面的简化表中将其格式化为更熟悉的MM:SS
| rank | entry_id | total_time |
|------|-----------|------------|
| 1 | 6 | 1:59.05 |
| 2 | 3 | 1:59.35 |
| 3 | 17 | 1:59.52 |
|......|...........|............|
| 20 | 13 | 56.56 | <- didn't make the top-20 cutoff, only has one run.
让我们创建一个表。(养成在所有SQL问题中都包括CREATE TABLE和INSERT语句的习惯。)
create table runs (
entry_id integer not null,
run_num integer not null
check (run_num between 1 and 3),
run_time interval not null
);
insert into runs values
(1, 1, '00:59.33'),
(2, 1, '00:59.93'),
(3, 1, '01:03.27'),
(1, 2, '00:59.88'),
(2, 2, '00:59.27');
该SQL语句将按所需顺序为您提供总计,但不会对它们进行排名。
with num_runs as (
select entry_id, count(*) as num_runs
from runs
group by entry_id
)
select r.entry_id, n.num_runs, sum(r.run_time) as total_time
from runs r
inner join num_runs n on n.entry_id = r.entry_id
group by r.entry_id, n.num_runs
order by num_runs desc, total_time asc
entry_id num_runs total_time - 2 2 00:01:59.2 1 2 00:01:59.21 3 1 00:01:03.27
该语句为等级添加一列。
with num_runs as (
select entry_id, count(*) as num_runs
from runs
group by entry_id
)
select
rank() over (order by num_runs desc, sum(r.run_time) asc),
r.entry_id, n.num_runs, sum(r.run_time) as total_time
from runs r
inner join num_runs n on n.entry_id = r.entry_id
group by r.entry_id, n.num_runs
order by rank asc
等级entry_id num_runs total_time - 1 2 2 00:01:59.2 2 1 2 00:01:59.21 3 3 1 00:01:03.27
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句