最近写SQL写多了,突发奇想SQL是不是也能实现简单的神经网络训练呢?于是带着这个问题在GitHub上找了找,还真有....那么本文就来分享一下如何用纯SQL实现一个神经网络吧!
题外话,可能有很多人会有疑问,你一个搞算法的,为啥在写SQL?这....就说来话长了... 总之,技多不压身嘛!
回归正题,我们再用SQL建模时,利用列来定义参数,从输入层到隐藏层,我们用 w1_00, w1_01, w1_10, w1_11表示权重矩阵W1,用b1_0, b1_1表示偏置向量B1。从隐藏层到输出层,我们用 w2_00, w2_01, w2_10, w2_11表示权重矩阵W2,用b2_0, b2_1表示偏置向量B2。这样我们通过一个多层嵌套的查询语句实现了整个训练过程。
![7fe4e564b2f48d6f04532c9571119bc9.png](https://i-blog.csdnimg.cn/blog_migrate/db7b723dd5a1bd2d9d9b641f4e94f28c.png)
add_iteration_sql = """
SELECT
x1,x2,y,
w_00 - (2.0)*(dw_00+(1e-3)*w_00) AS w1_00,
w_01 - (2.0)*(dw_01+(1e-3)*w_01) AS w1_01,
w_10 - (2.0)*(dw_10+(1e-3)*w_10) AS w1_10,
w_11 - (2.0)*(dw_11+(1e-3)*w_11) AS w1_11,
b_0 - (2.0)*db_0 AS b_0,
b_1 - (2.0)*db_1 AS b_1,
w2_00 - (2.0)*(dw2_00+(1e-3)*w2_00) AS w2_00,
w2_01 - (2.0)*(dw2_01+(1e-3)*w2_01) AS w2_01,
w2_10 - (2.0)*(dw2_10+(1e-3)*w2_10) AS w2_10,
w2_11 - (2.0)*(dw2_11+(1e-3)*w2_11) AS w2_11,
b2_0 - (2.0)*db2_0 AS b2_0,
b2_1 - (2.0)*db2_1 AS b2_1
FROM (
SELECT
*,
SUM(x1*dhidden_0) OVER () AS dw_00,
SUM(x1*dhidden_1) OVER () AS dw_01,
SUM(x2*dhidden_0) OVER () AS dw_10,
SUM(x2*dhidden_1) OVER () AS dw_11,
SUM(dhidden_0) OVER () AS db_0,
SUM(dhidden_1) OVER () AS db_1
FROM (
SELECT
*,
SUM(d0*dscores_0) OVER () AS dw2_00,
SUM(d0*dscores_1) OVER () AS dw2_01,
SUM(d1*dscores_0) OVER () AS dw2_10,
SUM(d1*dscores_1) OVER () AS dw2_11,
SUM(dscores_0) OVER () AS db2_0,