如何正确对 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 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)

}