按性别、合同序号按年统计贷款合同数的透视表实现问询

数据表定义与初始化SQL #

CREATE TABLE loans
(
    loan_id int,
    client_id int,
    loan_date date
);

CREATE TABLE clients
(
    client_id int,
    client_name varchar(20),
    gender varchar(20)
);

INSERT INTO clients
VALUES (1, 'arnold', 'male'),
       (2, 'lilly', 'female'),
       (3, 'betty', 'female'),
       (4, 'tom', 'male'),
       (5, 'jim', 'male');

INSERT INTO loans
VALUES (1, 1, '20220522'),
       (2, 2, '20220522'),
       (3, 3, '20220525'),
       (4, 4, '20220525'),
       (5, 1, '20220527'),
       (6, 2, '20220527'),
       (7, 3, '20220601'),
       (8, 1, '20220603'),
       (9, 2, '20220603'),
       (10, 1, '20220603');

需求说明 #

按年份统计不同性别、不同客户合同序号对应的贷款合同数量，输出如下格式的透视表：

其中“1 contract、2 contract”指客户的合同序号（即该客户第N次贷款的编号）。

原尝试的问题 #

原CTE存在表名拼写错误（clientc应为clients，别名c未正确关联），且需要实现列的透视补全，尝试crosstab时遇到结合问题。

可行实现方案 #

方案1：条件聚合（通用SQL，无需扩展） #

兼容性强，适用于大多数关系型数据库：

WITH client_loan_serials AS (
    SELECT
        c.gender,
        EXTRACT(YEAR FROM l.loan_date) AS loan_year,
        ROW_NUMBER() OVER (PARTITION BY l.client_id ORDER BY l.loan_date ASC) AS serial_number
    FROM loans l
    JOIN clients c ON l.client_id = c.client_id
),
aggregated_data AS (
    SELECT
        gender,
        loan_year,
        serial_number,
        COUNT(*) AS count_loan
    FROM client_loan_serials
    GROUP BY gender, loan_year, serial_number
)
SELECT
    gender AS sex,
    COALESCE(SUM(CASE WHEN serial_number = 1 AND loan_year = 2022 THEN count_loan END), 0) AS "1 contract, 2022",
    COALESCE(SUM(CASE WHEN serial_number = 2 AND loan_year = 2022 THEN count_loan END), 0) AS "2 contract, 2022",
    COALESCE(SUM(CASE WHEN serial_number = 3 AND loan_year = 2022 THEN count_loan END), 0) AS "3 contract, 2022"
FROM aggregated_data
GROUP BY gender
ORDER BY gender DESC;

方案2：PostgreSQL下使用crosstab函数（需tablefunc扩展） #

针对PostgreSQL环境，需先启用扩展：

-- 仅需执行一次，启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

WITH client_loan_serials AS (
    SELECT
        c.gender,
        CONCAT(ROW_NUMBER() OVER (PARTITION BY l.client_id ORDER BY l.loan_date ASC), ' contract, ', EXTRACT(YEAR FROM l.loan_date)) AS pivot_col,
        1 AS cnt
    FROM loans l
    JOIN clients c ON l.client_id = c.client_id
),
pivot_source AS (
    SELECT
        gender,
        pivot_col,
        COUNT(*) AS count_loan
    FROM client_loan_serials
    GROUP BY gender, pivot_col
)
SELECT
    gender AS sex,
    COALESCE("1 contract, 2022", 0) AS "1 contract, 2022",
    COALESCE("2 contract, 2022", 0) AS "2 contract, 2022",
    COALESCE("3 contract, 2022", 0) AS "3 contract, 2022"
FROM crosstab(
    'SELECT gender, pivot_col, count_loan FROM pivot_source ORDER BY 1,2',
    'SELECT unnest(array[''1 contract, 2022'', ''2 contract, 2022'', ''3 contract, 2022''])'
) AS ct(
    sex varchar(20),
    "1 contract, 2022" int,
    "2 contract, 2022" int,
    "3 contract, 2022" int
);

说明 #

• 方案1通过CASE语句实现透视，自动补全缺失列值为0，无数据库版本/扩展依赖。
• 方案2利用PostgreSQL专属的crosstab函数实现动态透视，需提前定义目标列，适合固定列场景。

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