如何创建自定义SQL GROUP BY聚合函数？求参考站点及示例

当然可以创建自定义聚合函数来实现分组内取最常见值这类需求，不同数据库系统的实现方式各有不同，以下是主流数据库的具体方案和示例：

PostgreSQL #

PostgreSQL支持通过CREATE AGGREGATE语法自定义聚合函数，结合PL/pgSQL即可编写核心逻辑：

1. 创建用于存储中间统计状态的函数：

CREATE OR REPLACE FUNCTION count_freq_state(state jsonb, val text)
RETURNS jsonb AS $$
BEGIN
  state := jsonb_set(state, ARRAY[val], (COALESCE(state->>val, '0')::int + 1)::text::jsonb, true);
  RETURN state;
END;
$$ LANGUAGE plpgsql;

2. 创建最终计算函数，从统计结果中提取出现次数最多的值：

CREATE OR REPLACE FUNCTION count_freq_final(state jsonb)
RETURNS text AS $$
DECLARE
  max_count int := 0;
  result text;
  rec record;
BEGIN
  FOR rec IN SELECT key, value::int FROM jsonb_each_text(state) LOOP
    IF rec.value > max_count THEN
      max_count := rec.value;
      result := rec.key;
    END IF;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

3. 注册聚合函数：

CREATE AGGREGATE most_common(text) (
  SFUNC = count_freq_state,
  STYPE = jsonb,
  FINALFUNC = count_freq_final,
  INITCOND = '{}'
);

完成后即可按你期望的方式使用：

SELECT company_name, most_common(first_name) AS most_common_first_name
FROM company
GROUP BY company_name;

MySQL #

MySQL 8.0+可通过自定义函数结合GROUP_CONCAT实现类似效果：

DELIMITER //
CREATE FUNCTION most_common(val_list TEXT) RETURNS TEXT
DETERMINISTIC
BEGIN
  DECLARE max_count INT DEFAULT 0;
  DECLARE result TEXT;
  DECLARE current_val TEXT;
  DECLARE current_count INT;
  DECLARE val_cursor CURSOR FOR 
    SELECT val, COUNT(*) AS cnt 
    FROM (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(val_list, ',', n), ',', -1)) AS val
          FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) nums
          WHERE n <= LENGTH(val_list) - LENGTH(REPLACE(val_list, ',', '')) + 1) vals
    GROUP BY val;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET current_val = NULL;
  
  OPEN val_cursor;
  read_loop: LOOP
    FETCH val_cursor INTO current_val, current_count;
    IF current_val IS NULL THEN
      LEAVE read_loop;
    END IF;
    IF current_count > max_count THEN
      max_count = current_count;
      result = current_val;
    END IF;
  END LOOP;
  CLOSE val_cursor;
  RETURN result;
END //
DELIMITER ;

使用时配合GROUP_CONCAT：

SELECT company_name, most_common(GROUP_CONCAT(first_name)) AS most_common_first_name
FROM company
GROUP BY company_name;

注：若字符串长度超出GROUP_CONCAT默认限制，需先调整group_concat_max_len参数。

SQL Server #

SQL Server可通过CLR集成创建正式的自定义聚合函数，也可通过T-SQL窗口函数模拟实现：

WITH name_counts AS (
  SELECT 
    company_name,
    first_name,
    COUNT(*) OVER (PARTITION BY company_name, first_name) AS name_count,
    ROW_NUMBER() OVER (PARTITION BY company_name ORDER BY COUNT(*) DESC) AS rn
  FROM company
)
SELECT company_name, first_name AS most_common_first_name
FROM name_counts
WHERE rn = 1
GROUP BY company_name, first_name;

若需封装成可直接调用的聚合函数，需用C#编写CLR聚合逻辑后部署到SQL Server中。

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