PostgreSQL按日期与类别分组去重并聚合统计问题

1. 修复聚合查询，实现按日期+唯一类别统计总和 #

原查询的核心问题是GROUP BY子句包含了good和not_good字段，导致每个不同的数值都会生成独立分组，进而出现重复的category。只需用SUM()聚合这两个字段，仅按date和category分组即可得到期望结果：

SELECT 
    t.date,
    NULL::uuid AS id_fruit,
    t.category, 
    SUM(t.good) AS good,
    SUM(t.not_good) AS not_good
FROM fruit_table t
JOIN main_table mt ON mt.id = t.id_fruit
WHERE t.date BETWEEN '2023-10-19' AND '2023-10-31'
    AND mt.main_id = '2089gh46'
    AND (t.category = ANY(ARRAY['apple', 'peach']) OR t.category IS NULL)
GROUP BY t.date, t.category;

2. 创建获取id_fruit对应类别的函数 #

以下是PostgreSQL环境下的函数实现，接收id_fruit参数后返回对应的类别：

CREATE OR REPLACE FUNCTION get_fruit_category(p_id_fruit uuid)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT category
        FROM fruit_table
        WHERE id_fruit = p_id_fruit
        LIMIT 1 -- 若一个id_fruit对应多个类别，取第一个；可根据业务逻辑调整
    );
END;
$$;

如果需要通过main_table校验关联关系，可修改函数内的查询逻辑：

CREATE OR REPLACE FUNCTION get_fruit_category(p_id_fruit uuid)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT t.category
        FROM fruit_table t
        JOIN main_table mt ON mt.id = t.id_fruit
        WHERE t.id_fruit = p_id_fruit
        LIMIT 1
    );
END;
$$;

3. 结合函数与聚合查询（可选） #

若需要在查询中直接调用函数关联id_fruit并展示统计值，可使用以下查询：

SELECT 
    t.date,
    t.id_fruit,
    get_fruit_category(t.id_fruit) AS category,
    SUM(t.good) AS good,
    SUM(t.not_good) AS not_good
FROM fruit_table t
JOIN main_table mt ON mt.id = t.id_fruit
WHERE t.date BETWEEN '2023-10-19' AND '2023-10-31'
    AND mt.main_id = '2089gh46'
    AND (t.category = ANY(ARRAY['apple', 'peach']) OR t.category IS NULL)
GROUP BY t.date, t.id_fruit;

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