模型

• W: 2×2 的权重矩阵（元素： w_00, w_01, w_10, w_11）
• B: 2×1 的偏置向量（元素：b_0, b_1）

• W2: 2×2 的权重矩阵(元素： w2_00, w2_01, w2_10, w2_11)
• B2: 2×1 的偏置向量(元素：b2_0, b2_1)

前向传播

`SELECT*,-0.00569693ASw_00,0.00186517ASw_01,0.00414431ASw_10,0.0105101ASw_11,0.0ASb_0, 0.0ASb_1, -0.01312284ASw2_00, -0.01269512ASw2_01, 0.00379152ASw2_10, -0.01218354ASw2_11, 0.0ASb2_0, 0.0ASb2_1FROM`example_project.example_dataset.example_table``

`SELECT*,(CASEWHEN((x1*w_00+x2*w_10)+b_0)>0.0THEN((x1*w_00+x2*w_10)+b_0)ELSE0.0END)ASd0,(CASEWHEN((x1*w_01+x2*w_11)+b_0)>0.0THEN((x1*w_01+x2*w_11)+b_1)ELSE0.0END)ASd1FROM{innersubquery}`

`SELECT*,EXP(scores_0)/(EXP(scores_0)+EXP(scores_1))ASprobs_0,EXP(scores_1)/(EXP(scores_0)+EXP(scores_1))ASprobs_1FROM(SELECT*,((d0*w2_00+d1*w2_10)+b2_0)ASscores_0,((d0*w2_01+d1*w2_11)+b2_1)ASscores_1FROM{INNERsub-query})`

`SELECT*,(sum_correct_logprobs/num_examples)+1e-3*(0.5*(w_00*w_00+w_01*w_01+w_10*w_10+w_11*w_11)+0.5*(w2_00*w2_00+w2_01*w2_01+w2_10*w2_10+w2_11*w2_11))ASlossFROM(SELECT*,SUM(correct_logprobs)OVER()sum_correct_logprobs,COUNT(1)OVER()num_examplesFROM(SELECT*,(CASEWHENy=0THEN-1*LOG(probs_0)ELSE-1*LOG(probs_1)END)AScorrect_logprobsFROM{innersubquery}))`

反向传播

`SELECT*,(CASEWHENy=0THEN(probs_0–1)/num_examplesELSEprobs_0/num_examplesEND)ASdscores_0,(CASEWHENy=1THEN(probs_1–1)/num_examplesELSEprobs_1/num_examplesEND)ASdscores_1FROM{innersubquery}`

`SELECT*,SUM(d0*dscores_0)OVER()ASdw2_00,SUM(d0*dscores_1)OVER()ASdw2_01,SUM(d1*dscores_0)OVER()ASdw2_10,SUM(d1*dscores_1)OVER()ASdw2_11,SUM(dscores_0)OVER()ASdb2_0,SUM(dscores_1)OVER()ASdb2_1, CASEWHEN(d0)<=0.0THEN0.0ELSE(dscores_0*w2_00+dscores_1*w2_01) ENDASdhidden_0,CASEWHEN(d1)<=0.0THEN0.0ELSE(dscores_0*w2_10+dscores_1*w2_11)ENDASdhidden_1FROM{innersubquery}`

`SELECT*,SUM(x1*dhidden_0)OVER()ASdw_00,SUM(x1*dhidden_1)OVER()ASdw_01,SUM(x2*dhidden_0)OVER()ASdw_10,SUM(x2*dhidden_1)OVER()ASdw_11,SUM(dhidden_0)OVER()ASdb_0,SUM(dhidden_1)OVER()ASdb_1FROM{innersubquery}`

L2 正则化，我们会在计算 dW 和 dW2 时加入一个正则项 reg*weight。我们也去掉如 dw_00, correct_logprobs 等缓存的列，它们曾在子查询时被创建，用于保存训练数据(x1, x2 及 y 列) 和模型参数（权重和偏置项）。对应的查询语句如下：

`SELECTx1,x2,y,w_00—(2.0)*(dw_00+(1e-3)*w_00)ASw_00,w_01—(2.0)*(dw_01+(1e-3)*w_01)ASw_01,w_10—(2.0)*(dw_10+(1e-3)*w_10)ASw_10,w_11—(2.0)*(dw_11+(1e-3)*w_11)ASw_11,b_0—(2.0)*db_0ASb_0, b_1—(2.0)*db_1ASb_1,w2_00—(2.0)*(dw2_00+(1e-3)*w2_00)ASw2_00,w2_01—(2.0)*(dw2_01+(1e-3)*w2_01)ASw2_01,w2_10—(2.0)*(dw2_10+(1e-3)*w2_10)ASw2_10,w2_11—(2.0)*(dw2_11+(1e-3)*w2_11)ASw2_11,b2_0—(2.0)*db2_0ASb2_0,b2_1—(2.0)*db2_1ASb2_1FROM{innersubquery}`

https://github.com/harisankarh/nn-sql-bq/blob/master/out.txt

https://github.com/harisankarh/nn-sql-bq/blob/master/query_for_prediction.sql。