sql - 有人可以解释这个 SQL 吗？ (我如何将它设为 'parametrize' 并作为函数调用？)

Question

我遇到过这段“voodoo”SQL，它用于对表中的数据执行自定义分组。我想了解它是如何神奇的，但我无法理解它。那里的 SQL 专家可以用简单的英语向不太懂 SQL 的人解释这个片段的各个部分，从而让它发挥它的魔力吗？

select ceil(rnk/10.0) as grp,
       col1, col2, col3, col4, col5, col6, col7
from (select e.col1, e.col2, e.col3, e.col4, e.col5, e.col6, e.col7,
             (select count(*) 
              from mytable d
              where e.col1 > d.col1)+1 as rnk
      from mytable e) x
order by grp;

上面 SQL 中我似乎无法理解的部分是返回列 'x' 的内部 SQL:

(select count(*) from mytable d
                where e.col1 > d.col1)+1 as rnk
                from mytable e
                ) x

我希望能够自己运行该查询:

select count(*) from mytable d
                where e.col1 > d.col1)+1 as rnk
                from mytable e

但是，当我这样做时，出现错误:

ERROR: syntax error at or near "+" LINE 2: where e.col1 > d.col1)+1 as rnk

那么，那里发生了什么？!

此外，当前的 SQL 是用数字 10 硬编码的。我想围绕它包装一个函数，以便能够使用 10 以外的数字调用该函数。

后端数据库是PostgreSQL，所以函数将在PL/pgSQL中。这是我第一次尝试编写这样的函数 - 然而，这不太正确，因为我想返回指定列的多行 - 所以下面的函数需要进行一些修改，不完全确定如何修改:

CREATE OR REPLACE FUNCTION my_custom_grouping(in integer, 
                                              out grp integer,
                                              out col1 double,
                                              out col2 double,
                                              out col3 double,
                                              out col4 double,
                                              out col5 double,
                                              out col6 double,
                                              out col7 double
                                              )

    AS $$ SELECT 
       ceil(rnk/$1) as grp,
                col1, col2, col3, col4, col5, col6, col7
                from (
                select e.col1, e.col2, e.col3, e.col4, e.col5, e.col6, e.col7,
                (select count(*) from mytable d
                where e.col1 > d.col1)+1 as rnk
                from mytable e
                ) x
                order by grp;
    $$
    LANGUAGE SQL;

除了不返回超过一行的函数外，我不确定这是否是参数化查询的最佳方式 - 我在正确的路径上吗？ - 如果是，我将如何修改上面的函数以返回多行而不是当前的单个“行”(即“多列”输出)？

这是对函数返回的数据运行聚合函数(按“grp”分组)的正确方法吗？

Answer 1

您的(简化的!)函数可能如下所示:

CREATE OR REPLACE FUNCTION my_custom_grouping(integer)
RETURNS TABLE (
   grp integer,
   col1 double precision,
   col2 double precision,
   col3 double precision,
   col4 double precision,
   col5 double precision,
   col6 double precision,
   col7 double precision) AS
$BODY$
    SELECT ceil(rank() OVER (ORDER BY col1) / $1)::int as grp
          ,col1, col2, col3, col4, col5, col6, col7
    FROM   mytable 
    ORDER  BY 1;
$BODY$ LANGUAGE SQL;

要点:

• 注意这是language SQL ，所以不是 PL/pgSQL 函数。您也可以使用 language plpgsql，但这里没有必要。

• 我用 window function rank() 替换了你的 voodoo 核心，它应该做同样的事情，只是更简单。

• 我还完全删除了子查询。没必要。

• double 类型称为 double precision在 PostgreSQL 中。

• 要返回多行，将函数定义为RETURNS SETOF record 或 RETURNS TABLE和我一样。

• ORDER BY可以使用位置参数，这样就不用再拼出第一列的计算了:ORDER BY 1。
  但是，同一 grp 中的多行。向 ORDER BY 子句添加更多列或表达式以获得稳定的排序顺序。