PostgreSQL中如何按自定义函数计算结果实现ORDER BY排序

问题背景 #

现有PostgreSQL查询通过自定义函数earned_media_direct计算media_points字段，需求是基于media_points生成排名（rank），并按该排名排序返回结果。同时需要确认之前尝试的rank生成写法是否正确，以及其他可行实现方式。

现有查询代码 #

SELECT
        message.id,
        note,
        earned_media_direct(
            SUM(message_stat.posts_delivered)::int,
            CAST(SUM(message_stat.clicks) AS bigint),
            team.earned_media_multi_clicks::int,
            SUM(message_stat.likes)::int,
            team.earned_media_multi_likes::int,
            SUM(message_stat.comments)::int,
            team.earned_media_multi_comments::int,
            SUM(message_stat.shares)::int,
            team.earned_media_multi_shares::int
        ) AS media_points,
        count(*) OVER() AS total_count
    FROM message
    LEFT JOIN team ON team.id = 10
    WHERE team_id = 10
    GROUP BY message.id, team.id
    {$orderBy}
    LIMIT 20 OFFSET 1

自定义函数定义 #

CREATE OR REPLACE FUNCTION public.earned_media_direct(posts bigint, clicks bigint, clicks_multiplier numeric, likes bigint, likes_multiplier numeric, comments bigint, comments_multiplier numeric, reshares bigint, shares_multiplier numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $function$

BEGIN
    RETURN COALESCE(clicks, 0) * clicks_multiplier + 
        COALESCE(likes, 0) * likes_multiplier + 
        COALESCE(comments, 0) * comments_multiplier + 
        (COALESCE(posts, 0) + COALESCE(reshares, 0)) * shares_multiplier;
    
END;

$function$

尝试的rank生成代码（存在问题） #

ROW_NUMBER() OVER (
            ORDER BY earned_media_direct(
            SUM(message_stat.posts_delivered), 
            CAST(SUM(message_stat.clicks) AS bigint), 
            team.earned_media_multi_clicks, 
            SUM(message_stat.likes), 
            team.earned_media_multi_likes, 
            SUM(message_stat.comments), 
            team.earned_media_multi_comments, 
            SUM(message_stat.shares), 
            team.earned_media_multi_shares) DESC
        ) AS rank

问题分析 #

你尝试的写法存在两个问题：

1. 类型不匹配：SUM(message_stat.posts_delivered)未做类型转换，而函数要求第一个参数为bigint，会引发类型错误。
2. 性能冗余：即使修正类型，重复调用函数会导致计算量翻倍，降低查询效率。

正确实现方法 #

方法1：用CTE复用media_points（推荐） #

通过CTE先完成聚合和media_points计算，再在外部查询生成rank并排序，避免重复计算函数，性能最优：

WITH aggregated_data AS (
    SELECT
        message.id,
        note,
        earned_media_direct(
            SUM(message_stat.posts_delivered)::int,
            CAST(SUM(message_stat.clicks) AS bigint),
            team.earned_media_multi_clicks::int,
            SUM(message_stat.likes)::int,
            team.earned_media_multi_likes::int,
            SUM(message_stat.comments)::int,
            team.earned_media_multi_comments::int,
            SUM(message_stat.shares)::int,
            team.earned_media_multi_shares::int
        ) AS media_points,
        count(*) OVER() AS total_count
    FROM message
    LEFT JOIN team ON team.id = 10
    WHERE team_id = 10
    GROUP BY message.id, team.id
)
SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY media_points DESC) AS rank
FROM aggregated_data
ORDER BY rank
LIMIT 20 OFFSET 1;

方法2：子查询替代CTE #

逻辑和方法1一致，仅用子查询替代CTE，适用于习惯子查询写法的场景：

SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY media_points DESC) AS rank
FROM (
    SELECT
        message.id,
        note,
        earned_media_direct(
            SUM(message_stat.posts_delivered)::int,
            CAST(SUM(message_stat.clicks) AS bigint),
            team.earned_media_multi_clicks::int,
            SUM(message_stat.likes)::int,
            team.earned_media_multi_likes::int,
            SUM(message_stat.comments)::int,
            team.earned_media_multi_comments::int,
            SUM(message_stat.shares)::int,
            team.earned_media_multi_shares::int
        ) AS media_points,
        count(*) OVER() AS total_count
    FROM message
    LEFT JOIN team ON team.id = 10
    WHERE team_id = 10
    GROUP BY message.id, team.id
) AS aggregated_data
ORDER BY rank
LIMIT 20 OFFSET 1;

方法3：重复函数调用（不推荐） #

若不想用CTE/子查询，可在窗口函数中重复完整的函数调用（需修正类型转换），但会导致函数被执行两次，性能较差：

SELECT
    message.id,
    note,
    earned_media_direct(
        SUM(message_stat.posts_delivered)::int,
        CAST(SUM(message_stat.clicks) AS bigint),
        team.earned_media_multi_clicks::int,
        SUM(message_stat.likes)::int,
        team.earned_media_multi_likes::int,
        SUM(message_stat.comments)::int,
        team.earned_media_multi_comments::int,
        SUM(message_stat.shares)::int,
        team.earned_media_multi_shares::int
    ) AS media_points,
    count(*) OVER() AS total_count,
    ROW_NUMBER() OVER(
        ORDER BY earned_media_direct(
            SUM(message_stat.posts_delivered)::int,
            CAST(SUM(message_stat.clicks) AS bigint),
            team.earned_media_multi_clicks::int,
            SUM(message_stat.likes)::int,
            team.earned_media_multi_likes::int,
            SUM(message_stat.comments)::int,
            team.earned_media_multi_comments::int,
            SUM(message_stat.shares)::int,
            team.earned_media_multi_shares::int
        ) DESC
    ) AS rank
FROM message
LEFT JOIN team ON team.id = 10
WHERE team_id = 10
GROUP BY message.id, team.id
ORDER BY rank
LIMIT 20 OFFSET 1;

内容的提问来源于stack exchange，提问作者jabepa