如何正确对 Postgres 数据进行分组

Question

在 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]

               },

          ] 

     },

]

Answer 1

好吧，我不能说这是一种优雅的方式，但最终我解决了我的任务：// 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 stringvar arrayQuestionText []stringvar arrayAgreePercent []float64var arrayDisagreePercent []float64for 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)}