PostgreSQL按客户账户统计每月交易数：COUNT函数结果异常求助

一、COUNT统计异常的原因及修复 #

你的COUNT语句出错的核心原因是：COUNT()函数会统计所有非NULL的值，而你的CASE语句里ELSE返回了0（非NULL），不管月份是否匹配，每个CASE表达式都会返回一个非NULL值，导致每个月份的COUNT结果都等于总行数。

修复方法有两种：

方法1：将ELSE改为NULL #

当月份不匹配时返回NULL，这样COUNT就只会统计匹配的行：

SELECT institution_account,
        COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount END) AS Jan,
        COUNT(CASE WHEN extract('month' FROM date) = 2 THEN amount END) AS Feb,
        COUNT(CASE WHEN extract('month' FROM date) = 3 THEN amount END) AS Mar,
        COUNT(CASE WHEN extract('month' FROM date) = 4 THEN amount END) AS Apr,
        COUNT(CASE WHEN extract('month' FROM date) = 5 THEN amount END) AS May,
        COUNT(CASE WHEN extract('month' FROM date) = 6 THEN amount END) AS Jun,
        COUNT(CASE WHEN extract('month' FROM date) = 7 THEN amount END) AS Jul,
        COUNT(CASE WHEN extract('month' FROM date) = 8 THEN amount END) AS Aug,
        COUNT(CASE WHEN extract('month' FROM date) = 9 THEN amount END) AS Sep,
        COUNT(CASE WHEN extract('month' FROM date) = 10 THEN amount END) AS Oct,
        COUNT(CASE WHEN extract('month' FROM date) = 11 THEN amount END) AS Nov,
        COUNT(CASE WHEN extract('month' FROM date) = 12 THEN amount END) AS Dec,
        COUNT(Amount) AS Total
FROM transactioning
GROUP BY institution_account

方法2：改用SUM统计符合条件的行数 #

用SUM替代COUNT，匹配时返回1，不匹配返回0，这样可以直接得到每个月的交易数：

SELECT institution_account,
        SUM(CASE WHEN extract('month' FROM date) = 1 THEN 1 ELSE 0 END) AS Jan,
        SUM(CASE WHEN extract('month' FROM date) = 2 THEN 1 ELSE 0 END) AS Feb,
        SUM(CASE WHEN extract('month' FROM date) = 3 THEN 1 ELSE 0 END) AS Mar,
        SUM(CASE WHEN extract('month' FROM date) = 4 THEN 1 ELSE 0 END) AS Apr,
        SUM(CASE WHEN extract('month' FROM date) = 5 THEN 1 ELSE 0 END) AS May,
        SUM(CASE WHEN extract('month' FROM date) = 6 THEN 1 ELSE 0 END) AS Jun,
        SUM(CASE WHEN extract('month' FROM date) = 7 THEN 1 ELSE 0 END) AS Jul,
        SUM(CASE WHEN extract('month' FROM date) = 8 THEN 1 ELSE 0 END) AS Aug,
        SUM(CASE WHEN extract('month' FROM date) = 9 THEN 1 ELSE 0 END) AS Sep,
        SUM(CASE WHEN extract('month' FROM date) = 10 THEN 1 ELSE 0 END) AS Oct,
        SUM(CASE WHEN extract('month' FROM date) = 11 THEN 1 ELSE 0 END) AS Nov,
        SUM(CASE WHEN extract('month' FROM date) = 12 THEN 1 ELSE 0 END) AS Dec,
        COUNT(Amount) AS Total
FROM transactioning
GROUP BY institution_account

二、PostgreSQL中crosstab的使用方法 #

PostgreSQL仍然支持crosstab，但需要先启用tablefunc扩展，步骤如下：

1. 首先执行以下语句安装扩展（需要超级用户权限）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 使用crosstab实现按月统计交易数的示例：

SELECT *
FROM crosstab(
    'SELECT institution_account, extract(''month'' FROM date)::int, COUNT(amount)
     FROM transactioning
     GROUP BY institution_account, extract(''month'' FROM date)
     ORDER BY 1, 2',
    'SELECT generate_series(1,12)'
) AS ct (
    institution_account text, -- 根据你的字段实际类型调整
    Jan int, Feb int, Mar int, Apr int, May int, Jun int,
    Jul int, Aug int, Sep int, Oct int, Nov int, Dec int
);

如果需要将交易数为0的月份显示为0而非NULL，可以用COALESCE函数转换每个月份字段。

三、关于PIVOT的说明 #

PostgreSQL原生并不支持PIVOT语法（该语法属于Oracle、SQL Server等数据库），因此你需要通过CASE表达式或者crosstab来实现行转列的需求。

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