これを、
You
とお願いしたのですが、上手く動かないようなので、Go言語で書いて貰いました。
/*
f:/しゅらばしゅう/有吉先生データ/Moove/2023-07/test1124.go
SELECT id, user_id, start_at, end_at, end_at - start_at, distance, prediction_type FROM records WHERE user_id = 43 ORDER BY start_at ASC; で選ばれた上記のSQLを、first_place, last_placeとして追記できるようにするには、どのように記述すれば良いですか。
で、作ってもらったプログラム
*/
package main
import (
"database/sql"
"encoding/json"
"fmt"
"log"
_ "github.com/lib/pq"
)
type Record struct {
ID int
UserID int
StartAt string
EndAt string
Duration string
Distance float64
PredictionType int
FirstPlace Location
LastPlace Location
}
type Location struct {
Lat float64 `json:"lat"`
Lng float64 `json:"lng"`
}
func main() {
// PostgreSQL データベースへの接続情報
const (
host = "192.168.0.23"
port = 15432
user = "postgres"
password = "password"
dbname = "moove"
)
// PostgreSQL データベースへの接続
connStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
db, err := sql.Open("postgres", connStr)
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query("SELECT id, user_id, start_at, end_at, end_at - start_at, distance, prediction_type, place FROM records WHERE user_id = 43 ORDER BY start_at ASC")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var r Record
var placeJSON string
err := rows.Scan(&r.ID, &r.UserID, &r.StartAt, &r.EndAt, &r.Duration, &r.Distance, &r.PredictionType, &placeJSON)
if err != nil {
log.Fatal(err)
}
var places []Location
err = json.Unmarshal([]byte(placeJSON), &places)
if err != nil {
log.Fatal(err)
}
if len(places) > 0 {
r.FirstPlace = places[0]
r.LastPlace = places[len(places)-1]
}
fmt.Printf("%+v\n", r)
}
}
出力結果
f:\しゅらばしゅう\有吉先生データ\Moove\2023-07>go run test1124.go
go run test1124.go
{ID:20132 UserID:43 StartAt:2023-04-08T05:35:00Z EndAt:2023-04-08T06:20:00Z Duration:00:45:00 Distance:348821.9097841587 PredictionType:4 FirstPlace:{Lat:35.3714 Lng:139.6255} LastPlace:{Lat:35.4409 Lng:139.6591}}
{ID:20133 UserID:43 StartAt:2023-04-08T06:20:30Z EndAt:2023-04-08T06:30:00Z Duration:00:09:30 Distance:153.88780282790526 PredictionType:2 FirstPlace:{Lat:35.4408 Lng:139.659} LastPlace:{Lat:35.4402 Lng:139.6596}}
{ID:20134 UserID:43 StartAt:2023-04-08T06:30:30Z EndAt:2023-04-08T06:49:30Z Duration:00:19:00 Distance:180.2412488153271 PredictionType:1 FirstPlace:{Lat:35.4402 Lng:139.6596} LastPlace:{Lat:35.4401 Lng:139.6599}}
{ID:20135 UserID:43 StartAt:2023-04-08T06:50:00Z EndAt:2023-04-08T06:53:30Z Duration:00:03:30 Distance:65.08472909159703 PredictionType:2 FirstPlace:{Lat:35.4401 Lng:139.6598} LastPlace:{Lat:35.44 Lng:139.6599}}
go run test1124.go
{ID:20132 UserID:43 StartAt:2023-04-08T05:35:00Z EndAt:2023-04-08T06:20:00Z Duration:00:45:00 Distance:348821.9097841587 PredictionType:4 FirstPlace:{Lat:35.3714 Lng:139.6255} LastPlace:{Lat:35.4409 Lng:139.6591}}
{ID:20133 UserID:43 StartAt:2023-04-08T06:20:30Z EndAt:2023-04-08T06:30:00Z Duration:00:09:30 Distance:153.88780282790526 PredictionType:2 FirstPlace:{Lat:35.4408 Lng:139.659} LastPlace:{Lat:35.4402 Lng:139.6596}}
{ID:20134 UserID:43 StartAt:2023-04-08T06:30:30Z EndAt:2023-04-08T06:49:30Z Duration:00:19:00 Distance:180.2412488153271 PredictionType:1 FirstPlace:{Lat:35.4402 Lng:139.6596} LastPlace:{Lat:35.4401 Lng:139.6599}}
{ID:20135 UserID:43 StartAt:2023-04-08T06:50:00Z EndAt:2023-04-08T06:53:30Z Duration:00:03:30 Distance:65.08472909159703 PredictionType:2 FirstPlace:{Lat:35.4401 Lng:139.6598} LastPlace:{Lat:35.44 Lng:139.6599}}
(以下省略)