如何在PostgreSQL表中确定可按客户组维度聚合的列

核心逻辑 #

要找出column1到column200里那些在date_column + customer_group_id维度下值唯一的列，关键就看：每个(date_column, customer_group_id)分组内，该列的所有取值完全一致。我们可以通过两种方式验证：要么检查分组内该列的COUNT(DISTINCT 列名)是否等于1，要么对比MAX(列名)和MIN(列名)是否相等（后者对数值类型效率更高）。

方法1：自动生成批量检查SQL #

手动写200条检查语句太麻烦，直接用PostgreSQL的系统表information_schema.columns生成所有检查语句：

SELECT 
    'SELECT ''' || column_name || ''' AS 列名, 
     CASE WHEN EXISTS (
         SELECT 1 FROM my_table
         GROUP BY date_column, customer_group_id
         HAVING COUNT(DISTINCT ' || column_name || ') > 1
     ) THEN ''不唯一'' ELSE ''唯一'' END AS 状态;' AS 检查语句
FROM information_schema.columns
WHERE table_name = 'my_table'
  AND column_name LIKE 'column%'
  AND column_name BETWEEN 'column1' AND 'column200';

执行这段SQL后，会输出对应每一列的检查语句。运行每条语句，若返回“不唯一”，说明该列在目标维度下存在不同值；返回“唯一”则符合要求。

方法2：用动态SQL直接获取结果 #

如果想一步到位拿到所有符合条件的列名，写个PL/pgSQL函数自动遍历检查：

CREATE OR REPLACE FUNCTION get_group_unique_columns()
RETURNS TABLE(column_name TEXT) AS $$
DECLARE
    col TEXT;
    is_valid BOOLEAN;
BEGIN
    -- 遍历所有目标列
    FOR col IN 
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'my_table'
          AND column_name LIKE 'column%'
          AND column_name BETWEEN 'column1' AND 'column200'
    LOOP
        -- 执行检查逻辑
        EXECUTE format(
            'SELECT NOT EXISTS (
                SELECT 1 FROM my_table
                GROUP BY date_column, customer_group_id
                HAVING COUNT(DISTINCT %I) > 1
            )', col
        ) INTO is_valid;
        
        -- 如果符合条件就返回列名
        IF is_valid THEN
            RETURN NEXT col;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数得到结果
SELECT * FROM get_group_unique_columns();

运行这个函数后，直接就能拿到所有在date_column + customer_group_id维度下唯一的列名，之后就可以把这些列和date_column、customer_group_id一起放到GROUP BY里做聚合。

注意点 #

• 处理NULL值：如果列里有NULL，COUNT(DISTINCT)会忽略NULL，这时候可以把判断改成COUNT(DISTINCT COALESCE(col, 'NULL_MARKER')) = 1（根据列类型选合适的标记值，比如数值型用-999999），确保NULL也被纳入检查。
• 性能优化：如果表数据量很大，建议给date_column和customer_group_id建个联合索引，能大幅加快分组查询的速度。

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