我将如何将 PERSIST 函数添加到排名中?
我计划从此代码创建一个视图,并将经常对其运行子查询。因此,我当然不希望排名数字根据我运行的任何子查询而更改。我希望它是一个普通的、非公式化的列。
SELECT a.player_id, a.full_name, a.first_name, a.last_name, d.[rank],
d.column3, a.tournament, c.column5, a.[year], a.[round],
a.score, RANK() OVER (PARTITION BY a.[round] + b.tournament_id + a.
[year] ORDER BY a.score) AS round_leaderboard
FROM df1 AS a JOIN
df2 AS b ON a.tournament = b.tournament JOIN
df3 AS c ON c.[Event] = a.tournament
LEFT JOIN df4 as d
On a.player_id=d.player_id and a.[year]=d.[year]
您可以使用实例化视图:
create view myview with schemabinding as
SELECT a.player_id, a.full_name, a.first_name, a.last_name, d.[rank],
d.column3, a.tournament, c.column5, a.[year], a.[round],
a.score,
RANK() OVER (PARTITION BY a.[round] + b.tournament_id + a.[year] ORDER BY a.score) AS round_leaderboard
FROM df1 a JOIN
df2 b
ON a.tournament = b.tournament JOIN
df3 c
ON c.[Event] = a.tournament LEFT JOIN
df4 d
On a.player_id = d.player_id and a.[year] = d.[year];
create index idx_myview_playerid on myview(playerid);
文档中对实例化视图进行了说明。