PL/pgSQL分组异常：同类型同日期数据未合并问题排查

问题场景 #

编写PL/pgSQL统计函数时，尝试按employer_type（由is_employer字段衍生）和日期分组统计记录，但相同日期、相同employer_type的记录未合并，返回了两条独立结果。

原函数代码：

CREATE OR REPLACE FUNCTION get_employer_profiles_count(provided_date date)
RETURNS TABLE (
  is_employer boolean,
  type text,
  date date,
  item_count integer
)
LANGUAGE sql security definer
AS $$
  SELECT
    pr.is_employer, case when pr.is_employer then 'company' else 'employer' end as type,  pr.created_at, count(pr.id) as count
  FROM profiles as pr
  WHERE pr.created_at BETWEEN date_trunc('day', provided_date) and date_trunc('day', provided_date) + interval '23:59:59'
  GROUP BY pr.is_employer, pr.created_at, type
  ORDER BY pr.created_at;
$$;

当前输出：

[{"is_employer":true,"count":1,"date":"2023-07-24","type":"company"},{"is_employer":true,"count":1,"date":"2023-07-24","type":"company"}]

问题原因 #

1. 时间精度导致分组拆分：pr.created_at是带时分秒的datetime类型，即使同一天，不同时间的记录会被视为不同分组键，导致同一天的记录无法合并。
2. 冗余分组字段：type完全由is_employer计算而来，GROUP BY中同时包含两者属于冗余操作，但核心问题还是created_at的时间精度问题。

修正方案 #

1. 将pr.created_at转换为date类型，确保同一天的记录归为同一分组；
2. 移除GROUP BY中的type（因它由is_employer衍生，无需单独分组）；
3. 对齐SELECT字段与函数返回的字段名（将count改为item_count，pr.created_at::date映射为date）；
4. 优化日期条件判断，避免毫秒级时间差导致的边界遗漏。

修正后的函数代码：

CREATE OR REPLACE FUNCTION get_employer_profiles_count(provided_date date)
RETURNS TABLE (
  is_employer boolean,
  type text,
  date date,
  item_count integer
)
LANGUAGE sql security definer
AS $$
  SELECT
    pr.is_employer,
    CASE WHEN pr.is_employer THEN 'company' ELSE 'employer' END AS type,
    pr.created_at::date AS date,
    COUNT(pr.id) AS item_count
  FROM profiles as pr
  WHERE pr.created_at >= date_trunc('day', provided_date)
    AND pr.created_at < date_trunc('day', provided_date) + interval '1 day'
  GROUP BY pr.is_employer, pr.created_at::date
  ORDER BY pr.created_at::date;
$$;

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