PostgreSQL复杂分组查询：将游标数据按成员单行写入另一表

要实现每个member__c对应一行数据写入另一张表，最直接高效的方式是把你现有的聚合查询作为数据源，搭配INSERT INTO语句完成批量写入，不用单独遍历游标（当然如果业务必须依赖游标逻辑，我也可以补充游标版本的实现）。

完整的写入语句示例 #

-- 假设目标表名为target_table，字段与查询结果对应
INSERT INTO target_table (member__c, partner_category__c, month, year, prog_amt, exported_id__c, sfid)
SELECT 
    member__c, 
    partner_category__c, 
    COALESCE(NULLIF(to_char(activity_transaction_date__c,'Mon'),''), 'Mon') AS month,
    to_char(activity_transaction_date__c,'YYYY') AS year,
    -- 注意：如果target_table的prog_amt是数值类型，这里应该用0而不是'0'
    COALESCE(NULLIF(sum(transaction_reward__c), 0), 0) AS prog_amt,
    exported_id__c,
    sfid 
FROM h_activity1 
GROUP BY member__c, partner_category__c, month, year, exported_id__c, sfid 
ORDER BY month, year;

关键逻辑说明 #

• 月份格式化处理：COALESCE(NULLIF(to_char(activity_transaction_date__c,'Mon'),''), 'Mon') 是为了避免日期字段为空或格式化后返回空字符串的情况，确保month字段始终有有效值。
• 奖励金额聚合处理：COALESCE(NULLIF(sum(transaction_reward__c), 0), 0) 会把求和结果为0的情况替换成0（如果是字符串类型就用'0'，数值类型用0，根据你的目标表字段类型调整）。
• 分组逻辑：GROUP BY子句结合member__c和其他维度字段，确保每个member__c在相同维度下只生成一行数据，完全符合你"每个member__c对应一行"的要求。

如果你必须使用游标实现 #

如果业务场景要求必须通过游标遍历数据再写入，这里也给一个简单的游标框架示例：

DECLARE
    CURSOR activity_cursor IS
        SELECT 
            member__c, 
            partner_category__c, 
            COALESCE(NULLIF(to_char(activity_transaction_date__c,'Mon'),''), 'Mon') AS month,
            to_char(activity_transaction_date__c,'YYYY') AS year,
            COALESCE(NULLIF(sum(transaction_reward__c), 0), 0) AS prog_amt,
            exported_id__c,
            sfid 
        FROM h_activity1 
        GROUP BY member__c, partner_category__c, month, year, exported_id__c, sfid 
        ORDER BY month, year;
    rec activity_cursor%ROWTYPE;
BEGIN
    OPEN activity_cursor;
    LOOP
        FETCH activity_cursor INTO rec;
        EXIT WHEN activity_cursor%NOTFOUND;
        
        -- 写入目标表
        INSERT INTO target_table (member__c, partner_category__c, month, year, prog_amt, exported_id__c, sfid)
        VALUES (rec.member__c, rec.partner_category__c, rec.month, rec.year, rec.prog_amt, rec.exported_id__c, rec.sfid);
    END LOOP;
    CLOSE activity_cursor;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

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