改进了一个(工作)函数，以获取十进制数具有一定小数位数的行

Question

我已经创建了三个测试表:用户、团队和成员，它们将用户和团队联系起来。users表包含一个user_id列，它是主键。memberships表包含一个user_id外键和另一个名为cost的列，该列具有十进制值。

然后根据我读过的一些面试问题，我给自己提出了以下SQL挑战:

"编写所需的SQL代码，以便以最多有N个小数点的代价获得用户"

SQL代码必须使用SQL函数(我使用PostgreSQL)。

我写的实际代码是:

CREATE OR REPLACE FUNCTION GET_NTH_DEC_RESIDUE(NUMERIC, INTEGER) RETURNS NUMERIC AS
    $function$
    SELECT CAST(CAST($1 AS NUMERIC) * POW(10,$2) - FLOOR(CAST($1 AS NUMERIC)*POW(10,$2)) AS NUMERIC);
    $function$
LANGUAGE SQL;
SELECT user_id, cost, nth_pos_dec
FROM (SELECT user_id, GET_NTH_DEC_RESIDUE(CAST(memberships.cost AS NUMERIC), 2) AS nth_pos_dec
      FROM users
        JOIN memberships
        USING (user_id)) AS T
WHERE NOT (nth_pos >= 0.0 AND nth_pos < 1.0);

函数GET_NTH_DEC_RESIDUE获取残差十进制数(例如，对于0.345和2位小数，函数返回0.5，对于0.12345和3位小数，函数返回0.45)。我们要查找的代价值就是那些不在[0,1)范围内的值。

通过将该函数"应用"到已加入的users+memberships视图，它生成一个包含剩余十进制数的新列，并且可以选择正确的行。

这个解决方案似乎做得很好，但我并不完全满意。

我试图将逻辑比较包装到另一个SQL函数中，以便简化主查询，但我没有设法做到。

有谁能想出一个更优雅的方法来做到这一点吗?(请注意，我对使用SQL函数感兴趣，我不想做字符串转换)。

谢谢!

Answer 1

你的函数很好。这里有一些我认为可以优化的地方:

1. 你不需要将第一个参数强制转换为NUMERIC -它已经是这种类型，所以第一个优化可以是:

   CREATE OR REPLACE FUNCTION GET_NTH_DEC_RESIDUE(NUMERIC, INTEGER) RETURNS NUMERIC AS
       $function$
       SELECT CAST($1 * POW(10, $2) - FLOOR($1 * POW(10, $2)) AS NUMERIC);
       $function$
   LANGUAGE SQL;
   

2. 当你检查函数的返回值时，没有必要检查它是否小于1 -它永远不可能等于或大于1，所以你可以检查它是否等于0(我已经检查过这个函数在负值下也能正常工作):

   ...
   WHERE nth_pos = 0
   

3. 如果您不需要此函数返回的数值，您可以将其更改为返回boolean并仅在WHERE子句中使用它(注意，您根本不需要强制转换):

   CREATE OR REPLACE FUNCTION GET_NTH_DEC_RESIDUE(NUMERIC, INTEGER) RETURNS BOOLEAN AS
       $function$
       SELECT $1 * POW(10, $2) - FLOOR($1 * POW(10, $2)) = 0;
       $function$
   LANGUAGE SQL;
   SELECT
           user_id,
           cost
   FROM
           users
           JOIN memberships
           USING (user_id)
   WHERE
           GET_NTH_DEC_RESIDUE(CAST(memberships.cost AS NUMERIC), 2);
   

4. 你可以计算POW(10, $2)只有一次(我不确定如果查询规划器不会这样做):

   CREATE OR REPLACE FUNCTION GET_NTH_DEC_RESIDUE(NUMERIC, INTEGER) RETURNS BOOLEAN AS
       $function$
       WITH precalc AS (
           SELECT POW(10, $2) AS power
       )
       SELECT 
           $1 * power - FLOOR($1 * power) = 0
       FROM
           precalc;
       $function$
   LANGUAGE SQL;