dass

This example shows how you can replace a SQL join with a RowFn. First, the data that would be the smaller table in the join is created. Then a function is built to apply that data to a Row. We then pull the larger table and use Apply to add the new element to each row.

const n = 100

table := os.Getenv("table")
zipQry := fmt.Sprintf("SELECT distinct zip3 from %s ORDER BY zip3", table)

db := newConnectCH()
defer db.Close()

// pull the unique zip3 (3-digit zip codes)
r, _ := db.Query(zipQry)
rows, _ := ReadSQL(r)
zipMap := make(map[string]int)

// build a simple map of zip3 to int.  We'll apply this to the data adding the "int" value to it.
for j, zip := range rows.Iter() {
	zipMap[zip["zip3"].(string)] = j
}

qry := fmt.Sprintf("SELECT lnId, fico, cltv, numBorr, purpose, propType, units, occ, state, msa, zip3 FROM %s ORDER BY lnId limit %v", table, n)
r, _ = db.Query(qry)

rows, _ = ReadSQL(r)

// rf applys the map to its input
rf := func(zip3 string) int {
	if v, ok := zipMap[zip3]; ok {
		return v
	}

	return -1
}

fs, _ := NewFnSpec("zipInt", rf, []string{"zip3"}, "zipInt")

_ = rows.Apply(fs)

fmt.Println(rows)

Output:

cltv,fico,lnId,msa,numBorr,occ,propType,purpose,state,units,zip3,zipInt
82,765,"000097473062","47900",2,"P","PU","P","MD",1,"207",194
95,766,"000097473063","42660",2,"P","SF","P","WA",1,"980",914
95,769,"000097473064","40140",1,"P","PU","P","CA",1,"923",858
95,687,"000097473065","35380",1,"P","SF","P","LA",1,"704",662
85,779,"000097473066","19820",1,"P","SF","R","MI",1,"480",459

Using Apply to run a function over *Rows

table := os.Getenv("table")
qry := fmt.Sprintf("SELECT lnId, state, ltv, cltv FROM %s WHERE cltv < ltv ORDER BY lnId LIMIT 10", table)

db := newConnectCH()
defer db.Close()

r, _ := db.Query(qry)
rows, _ := ReadSQL(r)

fixCLTV := func(ltv, cltv float64) float64 {
	if cltv < ltv {
		return ltv
	}

	return cltv
}

fs, _ := NewFnSpec("cltvFixed", fixCLTV, []string{"ltv", "cltv"}, "cltvFixed")

_ = rows.Apply(fs)

fmt.Println(rows)

Output:

cltv,cltvFixed,lnId,ltv,state
-1,88,"000106024156",88,"NC"
-1,27,"000106024193",27,"CA"
-1,84,"000106024226",84,"CA"
-1,100,"000106024234",100,"ME"
-1,90,"000106024278",90,"GA"

Using Apply to run a function that returns a slice

table := os.Getenv("table")
qry := fmt.Sprintf("SELECT lnId, state, ltv, cltv FROM %s ORDER BY lnId LIMIT 10", table)

db := newConnectCH()
defer db.Close()

r, _ := db.Query(qry)
rows, _ := ReadSQL(r)

fn := func(ltv, cltv float64) []float64 {
	if cltv < ltv {
		cltv = ltv
	}

	sltv := cltv - ltv
	return []float64{ltv, sltv, cltv}
}

fs, _ := NewFnSpec("ltvs", fn, []string{"ltv", "cltv"}, "ltvs")

_ = rows.Apply(fs)

fmt.Println(rows)

Output:

cltv,lnId,ltv,ltvs,state
82,"000097473062",82,[82 0 82],"MD"
95,"000097473063",95,[95 0 95],"WA"
95,"000097473064",95,[95 0 95],"CA"
95,"000097473065",95,[95 0 95],"LA"
85,"000097473066",85,[85 0 85],"MI"

This example shows how to create a CSV from a *Rows object.

table := os.Getenv("table")
qry := fmt.Sprintf("SELECT state, count(*) As n FROM %s GROUP BY state ORDER BY n DESC", table)

db := newConnectCH()
defer db.Close()

// run query
r, _ := db.Query(qry)

// load data
rows, _ := ReadSQL(r)

fmt.Println(rows)
outFile := os.TempDir() + "/states.csv"

f, _ := os.Create(outFile)
defer f.Close()

rows.Write(f)

Output:

state,n
"CA",10968569
"FL",4679574
"TX",4544139
"IL",3328800
"MI",2683424