如何使用Linq对PostgreSQL数据按月份求和并转置？

解决方案 #

方案1：PostgreSQL端直接实现聚合+转置

推荐在数据库层面完成，效率更高。需使用tablefunc扩展提供的crosstab函数：

1. 先确保扩展已安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行聚合与转置的SQL：

WITH monthly_sums AS (
    SELECT
        TO_CHAR(ListDate, 'MON-YY') AS month_label,
        SUM(MM) AS sum_mm,
        SUM(FC) AS sum_fc,
        SUM(AMS) AS sum_ams,
        SUM(KS) AS sum_ks
    FROM your_table_name  -- 替换为你的表名
    GROUP BY DATE_TRUNC('month', ListDate), TO_CHAR(ListDate, 'MON-YY')
    ORDER BY DATE_TRUNC('month', ListDate) DESC
)
SELECT
    category AS ".",
    "JAN-23",
    "DEC-22",
    "NOV-23"
FROM crosstab(
    -- 将聚合结果拆分为（月份标签、类别、值）的行格式
    'SELECT month_label, category, value FROM monthly_sums
     LATERAL (VALUES
         (''MM'', sum_mm),
         (''FC'', sum_fc),
         (''AMS'', sum_ams),
         (''KS'', sum_ks)
     ) AS t(category, value)
     ORDER BY 1, 2',
    -- 指定转置后的行类别
    'SELECT unnest(''{MM,FC,AMS,KS}''::text[])'
) AS ct(
    month_label text,
    "JAN-23" numeric,
    "DEC-22" numeric,
    "NOV-23" numeric
);

方案2：C#代码层面处理

若需在代码中处理，可分两步：先按月份聚合求和，再手动构造转置结构：

假设数据已加载到List<DataRecord>，模型定义：

public class DataRecord
{
    public DateTime ListDate { get; set; }
    public int MM { get; set; }
    public int FC { get; set; }
    public int AMS { get; set; }
    public int KS { get; set; }
}

处理逻辑：

using System.Globalization;
using System.Collections.Generic;
using System.Linq;

// 1. 按年月分组计算各字段总和
var monthlySums = dataRecords
    .GroupBy(r => new { r.ListDate.Year, r.ListDate.Month })
    .Select(g => new
    {
        MonthLabel = $"{CultureInfo.InvariantCulture.DateTimeFormat.GetAbbreviatedMonthName(g.Key.Month).ToUpper()}-{g.Key.Year.ToString().Substring(2)}",
        SumMM = g.Sum(r => r.MM),
        SumFC = g.Sum(r => r.FC),
        SumAMS = g.Sum(r => r.AMS),
        SumKS = g.Sum(r => r.KS)
    })
    .ToList();

// 2. 构造转置后的结果集
var transposed = new List<Dictionary<string, object>>
{
    new() { { ".", "MM" } },
    new() { { ".", "FC" } },
    new() { { ".", "AMS" } },
    new() { { ".", "KS" } }
};

foreach (var sum in monthlySums)
{
    transposed[0][sum.MonthLabel] = sum.SumMM;
    transposed[1][sum.MonthLabel] = sum.SumFC;
    transposed[2][sum.MonthLabel] = sum.SumAMS;
    transposed[3][sum.MonthLabel] = sum.SumKS;
}

// transposed即为转置后的结构，可按需转换为DataTable或直接使用

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