如何在Golang Gin+Gorm中从PostgreSQL获取多周期统计数据

1. 定义统计结果结构体 #

针对前端图表需求，先定义各维度的统计数据结构，包含日期维度标识和核心业务指标（以销售总量、总营收为例）：

// DailyStats 每日统计数据
type DailyStats struct {
    Date         string  `json:"date"`          // 格式：YYYY-MM-DD
    TotalSold    float64 `json:"total_sold"`    // 累计销量
    TotalRevenue float64 `json:"total_revenue"` // 累计营收：Sold*(Unit_cost-Discount)*(1+Tax/100)
}

// WeeklyStats 每周统计数据（以周一为周起始）
type WeeklyStats struct {
    WeekStart    string  `json:"week_start"`    // 周起始日期：YYYY-MM-DD
    TotalSold    float64 `json:"total_sold"`
    TotalRevenue float64 `json:"total_revenue"`
}

// MonthlyStats 每月统计数据
type MonthlyStats struct {
    Month        string  `json:"month"`         // 格式：YYYY-MM
    TotalSold    float64 `json:"total_sold"`
    TotalRevenue float64 `json:"total_revenue"`
}

// AnnualStats 年度统计数据
type AnnualStats struct {
    Year         int     `json:"year"`          // 年份：2024
    TotalSold    float64 `json:"total_sold"`
    TotalRevenue float64 `json:"total_revenue"`
}

2. Gorm聚合查询实现 #

利用PostgreSQL的日期截断函数DATE_TRUNC、格式化函数TO_CHAR结合Gorm的分组查询，实现各维度数据聚合：

2.1 每日统计 #

func GetDailyStats(db *gorm.DB) ([]DailyStats, error) {
    var stats []DailyStats
    err := db.Model(&Item{}).
        Select(`
            TO_CHAR(created_at, 'YYYY-MM-DD') as date,
            SUM(sold) as total_sold,
            SUM(sold * (unit_cost - discount) * (1 + tax::float / 100)) as total_revenue
        `).
        Group("TO_CHAR(created_at, 'YYYY-MM-DD')").
        Order("date ASC").
        Scan(&stats).Error
    return stats, err
}

2.2 每周统计 #

func GetWeeklyStats(db *gorm.DB) ([]WeeklyStats, error) {
    var stats []WeeklyStats
    err := db.Model(&Item{}).
        Select(`
            DATE_TRUNC('week', created_at)::date as week_start,
            SUM(sold) as total_sold,
            SUM(sold * (unit_cost - discount) * (1 + tax::float / 100)) as total_revenue
        `).
        Group("DATE_TRUNC('week', created_at)").
        Order("week_start ASC").
        Scan(&stats).Error
    return stats, err
}

2.3 每月统计 #

func GetMonthlyStats(db *gorm.DB) ([]MonthlyStats, error) {
    var stats []MonthlyStats
    err := db.Model(&Item{}).
        Select(`
            TO_CHAR(created_at, 'YYYY-MM') as month,
            SUM(sold) as total_sold,
            SUM(sold * (unit_cost - discount) * (1 + tax::float / 100)) as total_revenue
        `).
        Group("TO_CHAR(created_at, 'YYYY-MM')").
        Order("month ASC").
        Scan(&stats).Error
    return stats, err
}

2.4 年度统计 #

func GetAnnualStats(db *gorm.DB) ([]AnnualStats, error) {
    var stats []AnnualStats
    err := db.Model(&Item{}).
        Select(`
            EXTRACT(YEAR FROM created_at)::int as year,
            SUM(sold) as total_sold,
            SUM(sold * (unit_cost - discount) * (1 + tax::float / 100)) as total_revenue
        `).
        Group("EXTRACT(YEAR FROM created_at)").
        Order("year ASC").
        Scan(&stats).Error
    return stats, err
}

3. Gin接口封装 #

将统计方法封装为HTTP接口，供前端调用：

func RegisterStatsRoutes(r *gin.Engine, db *gorm.DB) {
    statsGroup := r.Group("/api/stats")
    {
        // 获取每日统计数据
        statsGroup.GET("/daily", func(c *gin.Context) {
            stats, err := GetDailyStats(db)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
                return
            }
            c.JSON(http.StatusOK, stats)
        })

        // 获取每周统计数据
        statsGroup.GET("/weekly", func(c *gin.Context) {
            stats, err := GetWeeklyStats(db)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
                return
            }
            c.JSON(http.StatusOK, stats)
        })

        // 获取每月统计数据
        statsGroup.GET("/monthly", func(c *gin.Context) {
            stats, err := GetMonthlyStats(db)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
                return
            }
            c.JSON(http.StatusOK, stats)
        })

        // 获取年度统计数据
        statsGroup.GET("/annual", func(c *gin.Context) {
            stats, err := GetAnnualStats(db)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
                return
            }
            c.JSON(http.StatusOK, stats)
        })
    }
}

4. 优化建议 #

• 给CreatedAt字段添加索引：在Item结构体中修改为CreatedAt time.Time gorm:"index"`，提升大数据量下的聚合查询效率
• 支持时间范围过滤：可在查询中添加Where("created_at BETWEEN ? AND ?", startDate, endDate)，前端通过query参数传入起始/结束时间
• 自定义业务指标：根据实际需求调整营收、销量的计算逻辑，比如是否忽略折扣、税的计算方式等

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