时间戳

首页 > 解决方案 > 如何根据sql中玩家的身高在表格中根据身高查找排名

sql - 如何根据sql中玩家的身高在表格中根据身高查找排名

问题描述

Q1：我试图做的是按高度排序，但这条线看起来可能是非法的，因为高度是我试图使列按顺序排列的变量，但我不能按顺序排序, rank 函数不会只是给每个高度一个等级，只需调用它并做over的事情，就像这样：

CREATE OR REPLACE FUNCTION get_rank(fn VARCHAR, ln VARCHAR)  
  RETURNS FLOAT AS $$ 
  DECLARE height Float=0.0;  
  BEGIN 
  SELECT rank() OVER(PARTITION BY height) rank 
     FROM
    (
       SELECT INTO height AVG(((p.h_feet*12)+ p.h_inches)*2.54) 
       FROM Players p
     ) 
     WHERE p.firstname=fn AND p.lastname=ln; return coalesce(rank,0.0) 
END; 

$$LANGUAGE plpgsql;

Q2：我需要换一种方式考虑吗？

Q3：我可以这样做吗：PARTITION BY 高度 ORDER BY 高度

Q4：我收到一个错误：ERROR: syntax error at or near "END" LINE 1: ...ERE p.firstname=fn AND p.lastname=ln; 返回排名END；$$局域网...

标签： sqlpostgresql

解决方案

我想评论你的函数，它有多个结构错误。那么让我们看看那些。首先从您的子选择开始：

   ( 
     select into height avg(((p.h_feet*12)+ p.h_inches)*2.54) 
       from players p 
   )

子选择只能将其结果传递给外部选择，但不能使用 INTO 子句（至少我不知道该怎么做）。但这不是必需的，您可以为结果设置别名并在外部查询中使用该别名。

此外，这是否可能会产生编译时错误。所需选择的格式是“将column_list选择到变量列表中”，您将变量列表和列列表颠倒。最后，Postgres 需要 subselect 的别名。因此，纠正您的需求：

   ( 
     select height avg(((p.h_feet*12)+ p.h_inches)*2.54) height 
       from players p 
   ) p1

移动到外部查询，并用我们有效得到的结果替换子选择：

  select rank() over(partition by height) rank 
     from subselect_results
    where p.firstname=fn and p.lastname=ln; return coalesce(rank,0.0)

我们在这里找到了什么：

  1. 最后一行包含 2 个语句，这是一个非常糟糕的过程。编译器可以接受，但它通常会生成很难找到的错误和/或很难找到运行时异常。养成每行 1 个语句的习惯。
  2. 代码块中的 select 语句需要此处缺少的“into 子句”。
  3. 没有对任何表的独立表引用。所以唯一可用的列是子查询结果，任何表引用都不可用。在这种情况下，单值高度。这会导致一个未定义列的错误。
  4. "rank() over(partition by height)" 在这一点高度基本上是恒定的。对常数进行排名总是产生 1 的结果。
  5. 即使您引用 Players 表，您的 where 子句也至少应该返回一行。对单行进行排名始终返回 1。
  6. 最后“返回合并（排名，0.0）”。Rank 是一个未定义的变量。此语句表示错误或无效参数调用排名。使用 Postgres 函数名称作为变量是一种不好的做法。您可能会侥幸成功，但也可能导致难以发现错误和/或异常。

更正以上所有内容，我们得到一个将实际运行的函数；

create or replace function get_rank(fn varchar, ln varchar) 
  returns float
  language plpgsql
as $$
declare
   rank_l float ;
begin
    select rank() over(partition by pi.height) 
         into rank_l
         from players p2
            , (
               select  avg(((p.h_feet*12)+ p.h_inches)*2.54) height
                 from players p 
              ) pi
    where p2.firstname = fn
      and p2.lastname  = ln;
    return rank_l;
end;
$$;

当然，由于它从玩家那里返回单行并且排名不变，因此可以用以下内容代替：

create or replace function get_rank(fn varchar, ln varchar) 
  returns float
  language plpgsql
as $$
begin 
   return 1.0
end;
$$;

在解决这个问题时，您似乎要问的是仅基于身高的特定玩家的排名。所以也许你需要的是：

-- setup 
create table players(id serial, firstname text, lastname text, h_feet integer, h_inches integer);
insert into players values (1,'A','B',6,3)
                         , (2,'C','D',6,4)
                         , (3,'E','F',6,9);

-- Build function  
create or replace function get_rank(fn text, ln text)
  returns table( firstname      text
               , lastname       text    
               , height         text
               , rank_by_height integer
               )
  language sql strict
as $$
    with player_ranking as
         (select firstname
               , lastname
               , concat(trim(to_char(h_feet, '9')), '''', trim(to_char(h_inches,'00')),  '"') size
               , 12*h_feet + h_inches height
            from players
         )
    select firstname, lastname, size, rnk
      from (
            select firstname, lastname, size, rank() over (order by height desc)::integer rnk
              from player_ranking pr
           ) pr
     where firstname = fn
       and lastname  = ln;
$$;

-- test 
select * 
  from  get_rank('C','D');

我将留给您研究使用的每个函数的作用，以及 SQL 与 pgplsql 函数的区别。为理解某事而进行的研究/研究绝不是浪费时间。

推荐阅读