Big Sky

なんとなく SQLite3 でロジスティック回帰できたら面白そうと思ったので作ってみた。

データセットは iris、sqlflow の DDL を使わせて頂いた。

sqlflow/example/datasets at develop · sql-machine-learning/sqlflow - GitHub

It should print the number of rows as the following: count(*) 10 Troubleshooting It usually takes ab...
https://github.com/sql-machine-learning/sqlflow/tree/develop/example/datasets


CREATE DATABASE IF NOT EXISTS iris;

DROP TABLE IF EXISTS iris.train;

CREATE TABLE iris.train (

       sepal_length float,

       sepal_width  float,

       petal_length float,

       petal_width  float,

       class int);

INSERT INTO iris.train VALUES(6.4,2.8,5.6,2.2,2);

INSERT INTO iris.train VALUES(5.0,2.3,3.3,1.0,1);

INSERT INTO iris.train VALUES(4.9,2.5,4.5,1.7,2);

INSERT INTO iris.train VALUES(4.9,3.1,1.5,0.1,0);

INSERT INTO iris.train VALUES(5.7,3.8,1.7,0.3,0);

INSERT INTO iris.train VALUES(4.4,3.2,1.3,0.2,0);

INSERT INTO iris.train VALUES(5.4,3.4,1.5,0.4,0);

INSERT INTO iris.train VALUES(6.9,3.1,5.1,2.3,2);

INSERT INTO iris.train VALUES(6.7,3.1,4.4,1.4,1);

INSERT INTO iris.train VALUES(5.1,3.7,1.5,0.4,0);

INSERT INTO iris.train VALUES(5.2,2.7,3.9,1.4,1);

INSERT INTO iris.train VALUES(6.9,3.1,4.9,1.5,1);

INSERT INTO iris.train VALUES(5.8,4.0,1.2,0.2,0);

INSERT INTO iris.train VALUES(5.4,3.9,1.7,0.4,0);

INSERT INTO iris.train VALUES(7.7,3.8,6.7,2.2,2);

INSERT INTO iris.train VALUES(6.3,3.3,4.7,1.6,1);

INSERT INTO iris.train VALUES(6.8,3.2,5.9,2.3,2);

INSERT INTO iris.train VALUES(7.6,3.0,6.6,2.1,2);

INSERT INTO iris.train VALUES(6.4,3.2,5.3,2.3,2);

INSERT INTO iris.train VALUES(5.7,4.4,1.5,0.4,0);

INSERT INTO iris.train VALUES(6.7,3.3,5.7,2.1,2);

INSERT INTO iris.train VALUES(6.4,2.8,5.6,2.1,2);

INSERT INTO iris.train VALUES(5.4,3.9,1.3,0.4,0);

INSERT INTO iris.train VALUES(6.1,2.6,5.6,1.4,2);

INSERT INTO iris.train VALUES(7.2,3.0,5.8,1.6,2);

INSERT INTO iris.train VALUES(5.2,3.5,1.5,0.2,0);

INSERT INTO iris.train VALUES(5.8,2.6,4.0,1.2,1);

INSERT INTO iris.train VALUES(5.9,3.0,5.1,1.8,2);

INSERT INTO iris.train VALUES(5.4,3.0,4.5,1.5,1);

INSERT INTO iris.train VALUES(6.7,3.0,5.0,1.7,1);

INSERT INTO iris.train VALUES(6.3,2.3,4.4,1.3,1);

INSERT INTO iris.train VALUES(5.1,2.5,3.0,1.1,1);

INSERT INTO iris.train VALUES(6.4,3.2,4.5,1.5,1);

INSERT INTO iris.train VALUES(6.8,3.0,5.5,2.1,2);

INSERT INTO iris.train VALUES(6.2,2.8,4.8,1.8,2);

INSERT INTO iris.train VALUES(6.9,3.2,5.7,2.3,2);

INSERT INTO iris.train VALUES(6.5,3.2,5.1,2.0,2);

INSERT INTO iris.train VALUES(5.8,2.8,5.1,2.4,2);

INSERT INTO iris.train VALUES(5.1,3.8,1.5,0.3,0);

INSERT INTO iris.train VALUES(4.8,3.0,1.4,0.3,0);

INSERT INTO iris.train VALUES(7.9,3.8,6.4,2.0,2);

INSERT INTO iris.train VALUES(5.8,2.7,5.1,1.9,2);

INSERT INTO iris.train VALUES(6.7,3.0,5.2,2.3,2);

INSERT INTO iris.train VALUES(5.1,3.8,1.9,0.4,0);

INSERT INTO iris.train VALUES(4.7,3.2,1.6,0.2,0);

INSERT INTO iris.train VALUES(6.0,2.2,5.0,1.5,2);

INSERT INTO iris.train VALUES(4.8,3.4,1.6,0.2,0);

INSERT INTO iris.train VALUES(7.7,2.6,6.9,2.3,2);

INSERT INTO iris.train VALUES(4.6,3.6,1.0,0.2,0);

INSERT INTO iris.train VALUES(7.2,3.2,6.0,1.8,2);

INSERT INTO iris.train VALUES(5.0,3.3,1.4,0.2,0);

INSERT INTO iris.train VALUES(6.6,3.0,4.4,1.4,1);

INSERT INTO iris.train VALUES(6.1,2.8,4.0,1.3,1);

INSERT INTO iris.train VALUES(5.0,3.2,1.2,0.2,0);

INSERT INTO iris.train VALUES(7.0,3.2,4.7,1.4,1);

INSERT INTO iris.train VALUES(6.0,3.0,4.8,1.8,2);

INSERT INTO iris.train VALUES(7.4,2.8,6.1,1.9,2);

INSERT INTO iris.train VALUES(5.8,2.7,5.1,1.9,2);

INSERT INTO iris.train VALUES(6.2,3.4,5.4,2.3,2);

INSERT INTO iris.train VALUES(5.0,2.0,3.5,1.0,1);

INSERT INTO iris.train VALUES(5.6,2.5,3.9,1.1,1);

INSERT INTO iris.train VALUES(6.7,3.1,5.6,2.4,2);

INSERT INTO iris.train VALUES(6.3,2.5,5.0,1.9,2);

INSERT INTO iris.train VALUES(6.4,3.1,5.5,1.8,2);

INSERT INTO iris.train VALUES(6.2,2.2,4.5,1.5,1);

INSERT INTO iris.train VALUES(7.3,2.9,6.3,1.8,2);

INSERT INTO iris.train VALUES(4.4,3.0,1.3,0.2,0);

INSERT INTO iris.train VALUES(7.2,3.6,6.1,2.5,2);

INSERT INTO iris.train VALUES(6.5,3.0,5.5,1.8,2);

INSERT INTO iris.train VALUES(5.0,3.4,1.5,0.2,0);

INSERT INTO iris.train VALUES(4.7,3.2,1.3,0.2,0);

INSERT INTO iris.train VALUES(6.6,2.9,4.6,1.3,1);

INSERT INTO iris.train VALUES(5.5,3.5,1.3,0.2,0);

INSERT INTO iris.train VALUES(7.7,3.0,6.1,2.3,2);

INSERT INTO iris.train VALUES(6.1,3.0,4.9,1.8,2);

INSERT INTO iris.train VALUES(4.9,3.1,1.5,0.1,0);

INSERT INTO iris.train VALUES(5.5,2.4,3.8,1.1,1);

INSERT INTO iris.train VALUES(5.7,2.9,4.2,1.3,1);

INSERT INTO iris.train VALUES(6.0,2.9,4.5,1.5,1);

INSERT INTO iris.train VALUES(6.4,2.7,5.3,1.9,2);

INSERT INTO iris.train VALUES(5.4,3.7,1.5,0.2,0);

INSERT INTO iris.train VALUES(6.1,2.9,4.7,1.4,1);

INSERT INTO iris.train VALUES(6.5,2.8,4.6,1.5,1);

INSERT INTO iris.train VALUES(5.6,2.7,4.2,1.3,1);

INSERT INTO iris.train VALUES(6.3,3.4,5.6,2.4,2);

INSERT INTO iris.train VALUES(4.9,3.1,1.5,0.1,0);

INSERT INTO iris.train VALUES(6.8,2.8,4.8,1.4,1);

INSERT INTO iris.train VALUES(5.7,2.8,4.5,1.3,1);

INSERT INTO iris.train VALUES(6.0,2.7,5.1,1.6,1);

INSERT INTO iris.train VALUES(5.0,3.5,1.3,0.3,0);

INSERT INTO iris.train VALUES(6.5,3.0,5.2,2.0,2);

INSERT INTO iris.train VALUES(6.1,2.8,4.7,1.2,1);

INSERT INTO iris.train VALUES(5.1,3.5,1.4,0.3,0);

INSERT INTO iris.train VALUES(4.6,3.1,1.5,0.2,0);

INSERT INTO iris.train VALUES(6.5,3.0,5.8,2.2,2);

INSERT INTO iris.train VALUES(4.6,3.4,1.4,0.3,0);

INSERT INTO iris.train VALUES(4.6,3.2,1.4,0.2,0);

INSERT INTO iris.train VALUES(7.7,2.8,6.7,2.0,2);

INSERT INTO iris.train VALUES(5.9,3.2,4.8,1.8,1);

INSERT INTO iris.train VALUES(5.1,3.8,1.6,0.2,0);

INSERT INTO iris.train VALUES(4.9,3.0,1.4,0.2,0);

INSERT INTO iris.train VALUES(4.9,2.4,3.3,1.0,1);

INSERT INTO iris.train VALUES(4.5,2.3,1.3,0.3,0);

INSERT INTO iris.train VALUES(5.8,2.7,4.1,1.0,1);

INSERT INTO iris.train VALUES(5.0,3.4,1.6,0.4,0);

INSERT INTO iris.train VALUES(5.2,3.4,1.4,0.2,0);

INSERT INTO iris.train VALUES(5.3,3.7,1.5,0.2,0);

INSERT INTO iris.train VALUES(5.0,3.6,1.4,0.2,0);

INSERT INTO iris.train VALUES(5.6,2.9,3.6,1.3,1);

INSERT INTO iris.train VALUES(4.8,3.1,1.6,0.2,0);



DROP TABLE IF EXISTS iris.test;

CREATE TABLE iris.test (

       sepal_length float,

       sepal_width  float,

       petal_length float,

       petal_width  float,

       class int);

INSERT INTO iris.test VALUES(6.3,2.7,4.9,1.8,2);

INSERT INTO iris.test VALUES(5.7,2.8,4.1,1.3,1);

INSERT INTO iris.test VALUES(5.0,3.0,1.6,0.2,0);

INSERT INTO iris.test VALUES(6.3,3.3,6.0,2.5,2);

INSERT INTO iris.test VALUES(5.0,3.5,1.6,0.6,0);

INSERT INTO iris.test VALUES(5.5,2.6,4.4,1.2,1);

INSERT INTO iris.test VALUES(5.7,3.0,4.2,1.2,1);

INSERT INTO iris.test VALUES(4.4,2.9,1.4,0.2,0);

INSERT INTO iris.test VALUES(4.8,3.0,1.4,0.1,0);

INSERT INTO iris.test VALUES(5.5,2.4,3.7,1.0,1);

僕が作ってる Go の SQLite3 ドライバはユーザ関数を Go で書く事が出来る。


    sql.Register("sqlite3_custom", &sqlite3.SQLiteDriver{

        ConnectHook: func(conn *sqlite3.SQLiteConn) error {

            if err := conn.RegisterAggregator("logistic_regression_train", createLogisticRegressionTrain(conn), true); err != nil {

                return err

            }

            if err := conn.RegisterFunc("logistic_regression_predict", createLogisticRegressionPredict(conn), true); err != nil {

                return err

            }

            return nil

        },

    })



    db, err := sql.Open("sqlite3_custom", ":memory:")

    if err != nil {

        log.Fatal(err)

    }

    defer db.Close()



    _, err = db.Exec(`attach "iris.sqlite" as iris`)

    if err != nil {

        log.Fatal(err)

    }

ユーザ関数とアグリゲート関数は動作が異なっていて、ユーザ関数は SELECT で使うと行毎に呼び出され、行毎の結果が返る。アグリゲート関数は行毎に Step メソッドが呼ばれ、最後に Done メソッドが呼ばれる。つまり集計関数になる。アグリゲート関数で以下の様に SELECT した結果を全て貰いモデルを作る。モデルは JSON 形式で出力する様にした。文字列を持ったテーブルにそのまま突っ込める。これを logistic_regression_train という関数名にした。


    _, err = db.Exec(`

    drop table if exists iris.model;

    create table iris.model(config text);

    insert into iris.model

    select

        logistic_regression_train('{

                "rate":    0.1,

                "ntrains": 5000

            }',

            sepal_length,

            sepal_width,

            petal_length,

            petal_width,

            class

        )

    from

        iris.train

    `)

    if err != nil {

        log.Fatal(err)

    }

次にこの JSON からモデルに戻し、引数で渡されたテストデータから推論する関数 logistics_regression_predict を作った。


    rows, err := db.Query(`

    select

        logistic_regression_predict('iris.model',

            sepal_length,

            sepal_width,

            petal_length,

            petal_width

        ), class

    from

        iris.test

    `)

    if err != nil {

        log.Fatal(err)

    }

    defer rows.Close()



    for rows.Next() {

        var predicted, class float64

        err = rows.Scan(&predicted, &class)

        if err != nil {

            log.Fatal(err)

        }

        fmt.Println(math.RoundToEven(predicted), class)

    }

ロジスティック回帰そのものは gonum を使って書いた。


func (s *logistic_regression) Done() (string, error) {

    ws := make([]float64, s.X[0].Len())

    for i := range ws {

        ws[i] = s.rand.Float64()

    }

    for i := range s.y {

        s.y[i] = s.y[i] / (s.maxy + 1)

    }

    w := mat.NewVecDense(len(ws), ws)

    y := mat.NewVecDense(len(s.y), s.y)

    for n := 0; n < s.cfg.NTrains; n++ {

        for i, x := range s.X {

            t := mat.NewVecDense(x.Len(), nil)

            t.CopyVec(x)

            pred := softmax(t, w)

            perr := y.AtVec(i) - pred

            scale := s.cfg.Rate * perr * pred * (1 - pred)



            for j := 0; j < x.Len(); j++ {

                dx := mat.NewVecDense(x.Len(), nil)

                dx.CopyVec(x)

                dx.ScaleVec(scale, x)

                w.AddVec(w, dx)

            }

        }

    }



    fargs := make([]float64, w.Len())

    for i := 0; i < w.Len(); i++ {

        fargs[i] = w.AtVec(i)

    }

    var buf bytes.Buffer

    err := json.NewEncoder(&buf).Encode(&model{

        W: fargs,

        M: s.maxy,

    })

    if err != nil {

        return "", err

    }

    return buf.String(), nil

}

この例では推論した値 predict と、正解の値 class が SELECT されるので Go で値を取り出すと推論が正しいか判断できる。


    for rows.Next() {

        var predicted, class float64

        err = rows.Scan(&predicted, &class)

        if err != nil {

            log.Fatal(err)

        }

        fmt.Printf(

            "predict: %d (%d)\n",

            int(math.RoundToEven(predicted)), int(class))

    }


predict: 1 (2)

predict: 1 (1)

predict: 0 (0)

predict: 2 (2)

predict: 0 (0)

predict: 1 (1)

predict: 1 (1)

predict: 0 (0)

predict: 0 (0)

predict: 1 (1)

正解率 90% なのでまずまずと言っていいのかな。

サンプルコードの位置づけだけど GitHub にコードを置いておきます。

GitHub - mattn/go-sqlite3-logistics-regression

Features → Code review Project management Integrations Actions Package registry Team management...
https://github.com/mattn/go-sqlite3-logistics-regression

RとPythonで学ぶ[実践的]データサイエンス&機械学習
有賀友紀, 大橋俊介
技術評論社単行本（ソフトカバー） / ￥3,100 (2019年03月26日)

発送可能時間：

Big Sky

2019/05/12

Recent entries from same category

SQLite3 でロジスティック回帰