doujingke4981
doujingke4981
2018-09-30 17:30

如何将一行的AVERAGE放到新列中?

已采纳

Hey everyone I am using golang to build a super simple API. I have this json data being passed from a POST request and being stored in my DB. I would like to take the tts data which is an integer array and average that array and place it in the ttc column and return that number on the json response. I am having a hard time doing that any help would be greatly appreciated. My source code is below as well as my DB Model. I know I would have to use the AVG() function somehow in postgres but I am brand new to postgres so I am super confused.

main.go

package main

import (
"encoding/json"
"github.com/gorilla/mux"
"github.com/jinzhu/gorm"
"github.com/lib/pq"
"github.com/rs/cors"
"log"
"net/http"

_ "github.com/jinzhu/gorm/dialects/postgres"
)

type Resource struct {
    gorm.Model

    Name        string
    TTS     pq.Int64Array `gorm:"type:integer[]"`
    TTC int
}

var db *gorm.DB
var err error

func main() {
    router := mux.NewRouter()

    db, err = gorm.Open(
        "postgres",
        "host=localhost"+" user=postgres"+
        " dbname=Shoes"+" sslmode=disable password=root")

    if err != nil {
        panic("failed to connect database")
    }

    defer db.Close()

    db.AutoMigrate(&Resource{})

    router.HandleFunc("/resources", GetResources).Methods("GET")
    router.HandleFunc("/resources/{id}", GetResource).Methods("GET")
    router.HandleFunc("/resources", CreateResource).Methods("POST")
    router.HandleFunc("/resources/{id}", DeleteResource).Methods("DELETE")

    handler := cors.Default().Handler(router)

    log.Fatal(http.ListenAndServe(":8080", handler))
}

func GetResources(w http.ResponseWriter, r *http.Request) {
    var resources []Resource
    db.Find(&resources)
    json.NewEncoder(w).Encode(&resources)
}

func GetResource(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    var resource Resource
    db.First(&resource, params["id"])
    json.NewEncoder(w).Encode(&resource)
}

func CreateResource(w http.ResponseWriter, r *http.Request) {
    var resource Resource
    json.NewDecoder(r.Body).Decode(&resource)
    db.Create(&resource)
    json.NewEncoder(w).Encode(&resource)
}

func DeleteResource(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    var resource Resource
    db.First(&resource, params["id"])
    db.Delete(&resource)

    var resources []Resource
    db.Find(&resources)
    json.NewEncoder(w).Encode(&resources)
}

I am thinking I could do something like

db.Select("AVG(tts)")

I am just not sure how to put that result in the column ttc

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongzhuanlei0768 dongzhuanlei0768 3年前

    Since the json of the post request already contains the tts_data, you can get the average before setting it in the database

    sum := 0
    for _, i := range tts_data {
     sum += i
    }
    avg := sum / len(tts_data)
    // save the data in your db
    rs := Ressource{Name: "name", TTS: tts_data, ttc: avg}
    b := db.Create(&rs)
    if b {
      // send all the resource
      json.NewEncoder(w).Encode(&rs)
      // or send only the avg
      json.NewEncoder(w).Encode(struct{avg: int}{avg: avg})
    } else {
      // handle error
    }
    
    点赞 评论 复制链接分享

为你推荐