正确的 Postgres 数据分组技巧

本篇文章给大家分享《正确的 Postgres 数据分组技巧》，覆盖了Golang的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

在 postgresql 中，我有这样的表：

| question_text                          | category | agree_percent | disagree_percent |
|----------------------------------------|----------|---------------|------------------|
| do you support the president's policy? | policy   | 50            | 50               |
| do you support democrats?              | policy   | 32            | 68               |
| do you support the lannisters?         | cinema   | 45            | 55               |
| do you support spielberg's work?       | cinema   | 60            | 40               |

在我的 go 应用程序中，在 gorm 库的帮助下，我向 postgresql 数据库发出 sql 请求，如下所示：

type entry struct {
     questiontext string `json:"question_text"`
     category string `json:"category"`
     agreepercent float64 `json:"agree_percent"`
     disagreepercent float64 `json:"disagree_percent"`
}

rows, _ := database.dbgorm.raw("select * from specification").rows()

for rows.next() {
     entry := &entry{}

     if err = rows.scan(&entry.questiontext, & entry.category,  &entry.agreepercent, &entry.disagreepercent); err != nil {
          utils.logger().println(err)   
     }
}

如何得到类似的结果？正如您所看到的，数组中的每个对象都按 category 列中的值进行分组：

[
     {
          category: "Policy",
          questions: ["Do you support the President's policy?", "Do you support Democrats?"],
          series: [
               {
                    name: "Agree, %",
                    data: [50, 32]
               },
               {
                    name: "Disagree, %",
                    data: [50, 68]
               },
          ] 
     },
     {
          category: "Cinema",
          questions: ["Do you support the Lannisters?", "Do you support Spielberg's work?"],
          series: [
               {
                    name: "Agree, %",
                    data: [45, 60]
               },
               {
                    name: "Disagree, %",
                    data: [55, 40]
               },
          ] 
     },
]

解决方案

嗯，我不能说这是一种优雅的方式，但最终我解决了我的任务：

// Create struct called "Series".
type Series struct {
    Name string `json:"name"`
    Data []float64 `json:"data"`
}

// Create struct called "Specification".
type Specification struct {
    Category string `json:"category"`
    Questions []string `json:"questions"`
    Series []Series `json:"series"`
}

// Initialize an array of struct.
var specifications []Specification

// Initialize several variables.
var catogoryName string
var arrayQuestionText []string
var arrayAgreePercent []float64
var arrayDisagreePercent []float64


for rows.Next() {
    // Check the change in the name of the category.
    if entry.Category == catogoryName {
        // Add new elements to arrays.
        arrayQuestionText = append(arrayQuestionText, entry.QuestionText)
        arrayDisagreePercent = append(arrayDisagreePercent, entry.DisagreePercent)
        arrayAgreePercent = append(arrayAgreePercent, entry.AgreePercent)
    } else {
        if len(catogoryName) > 0 {
            // Fill the struct with data.
            specification := Specification{
                Category: catogoryName,
                Questions: arrayQuestionText,
                Series: []Series{
                    {
                        Name: "Agree, %",
                        Data: arrayAgreePercent,
                    },
                    {
                        Name: "Disagree, %",
                        Data: arrayDisagreePercent,
                    },
                },
            }

            // Add new struct to array.
            specifications = append(specifications, specification)
        }
    }
    // Update data in arrays.
    catogoryName = entry.Category
    arrayQuestionText = nil
    arrayQuestionText = append(arrayQuestionText, entry.QuestionText)
    arrayDisagreePercent = nil
    arrayDisagreePercent = append(arrayDisagreePercent, entry.DisagreePercent)
    arrayAgreePercent = nil
    arrayAgreePercent = append(arrayAgreePercent, entry.AgreePercent)
}

终于介绍完啦！小伙伴们，这篇关于《正确的 Postgres 数据分组技巧》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布Golang相关知识，快来关注吧！