# 如何用纯SQL查询语句可以实现神经网络？

• 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.00569693 AS w_00,
0.00186517 AS w_01,
0.00414431 AS w_10,
0.0105101 AS w_11,
0.0 AS b_0,
0.0 AS b_1,
-0.01312284 AS w2_00,
-0.01269512 AS w2_01,
0.00379152 AS w2_10,
-0.01218354 AS w2_11,
0.0 AS b2_0,
0.0 AS b2_1
FROM `example_project.example_dataset.example_table````

```SELECT *,
(CASE
WHEN ((x1*w_00 + x2*w_10) + b_0) > 0.0 THEN ((x1*w_00 + x2*w_10) + b_0)
ELSE 0.0
END) AS d0,
(CASE
WHEN ((x1*w_01 + x2*w_11) + b_0) > 0.0 THEN ((x1*w_01 + x2*w_11) + b_1)
ELSE 0.0
END) AS d1
FROM {inner subquery}```

```SELECT *,
EXP(scores_0)/(EXP(scores_0) + EXP(scores_1)) AS probs_0,
EXP(scores_1)/(EXP(scores_0) + EXP(scores_1)) AS probs_1
FROM
( SELECT *,
((d0*w2_00 + d1*w2_10) + b2_0) AS scores_0,
((d0*w2_01 + d1*w2_11) + b2_1) AS scores_1
FROM {INNER sub-query})```

`在查询当中，我们同样会计算训练样本的数量(num_examples)。这对于后续我们计算平均值来说很有用。SQL 查询中计算整体损失函数的语句如下：`

```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)) AS loss
FROM
(SELECT *,
SUM(correct_logprobs) OVER () sum_correct_logprobs,
COUNT(1) OVER () num_examples
FROM
(SELECT *,
(CASE
WHEN y = 0 THEN -1*LOG(probs_0)
ELSE -1*LOG(probs_1)
END) AS correct_logprobs
FROM {inner subquery}))```

```SELECT *,
(CASE
WHEN y = 0 THEN (probs_0–1)/num_examples
ELSE probs_0/num_examples
END) AS dscores_0,
(CASE
WHEN y = 1 THEN (probs_1–1)/num_examples
ELSE probs_1/num_examples
END) AS dscores_1
FROM {inner subquery}```

```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,
SUM(dscores_1) OVER () AS db2_1,

CASE
WHEN (d0) <= 0.0 THEN 0.0
ELSE (dscores_0*w2_00 + dscores_1*w2_01)
END AS dhidden_0,
CASE
WHEN (d1) <= 0.0 THEN 0.0
ELSE (dscores_0*w2_10 + dscores_1*w2_11)
END AS dhidden_1
FROM {inner subquery}```

```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 {inner subquery}```

```SELECT x1,
x2,

y,

w_00 — (2.0)*(dw_00+(1e-3)*w_00) AS w_00,
w_01 — (2.0)*(dw_01+(1e-3)*w_01) AS w_01,
w_10 — (2.0)*(dw_10+(1e-3)*w_10) AS w_10,
w_11 — (2.0)*(dw_11+(1e-3)*w_11) AS w_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 {inner subquery}```

• 创建中间表和多个 SQL 语句有助于增加迭代数。例如，前 10 次迭代的结果可以存储在一个中间表中。同一查询语句在执行下 10 次迭代时可以基于这个中间表。如此，我们就执行了 20 个迭代。这个方法可以反复使用，以应对更大的查询迭代。
• 相比于在每一步增加外查询，我们应该尽可能的使用函数的嵌套。例如，在一个子查询中，我们可以同时计算 scores 和 probs，而不应使用 2 层嵌套查询。
• 在上例中，所有的中间项都被保留直到最后一个外查询执行。其中有些项如 `correct_logprobs `可以早些删除（尽管 SQL 引擎可能会自动的执行这类优化）。
• 多尝试应用用户自定义的函数。如果感兴趣，你可以看看这个 BigQuery 的用户自定义函数的服务模型的项目（但是，无法使用 SQL 或者 UDFs 进行训练）。

1296 篇文章121 人订阅

0 条评论

## 相关文章

3874

5207

3987

### 【专知-Deeplearning4j深度学习教程03】使用多层神经网络分类MNIST数据集:图文+代码

【导读】主题链路知识是我们专知的核心功能之一，为用户提供AI领域系统性的知识学习服务，一站式学习人工智能的知识，包含人工智能（ 机器学习、自然语言处理、计算机视...

46011

1.4K7

1341

3742

3315

85010

1872