这篇文章最初是 Ensemble Analytics 的一篇博文，他们友好地允许重新发布此内容。我们欢迎来自社区的帖子，并感谢他们的贡献。
介绍
本文是系列文章的一部分，我们将在其中探讨如何在 ClickHouse 中进行数据科学工作。本系列文章包括 预测、 异常检测、 线性回归 和 时间序列分类。
虽然这种类型的分析通常会在 ClickHouse 之外使用 Python 或 R 等编程语言进行，但我们更倾向于尽可能地只使用数据库。
通过这样做，我们可以依靠 ClickHouse 的强大功能来以高性能处理大型数据集，并减少甚至完全避免需要编写的代码量。这也意味着我们可以在客户端使用更小的内存数据集，并可能避免使用 Spark 等框架进行分布式计算。
描述完整工作示例的笔记本可以在这里找到这里。
关于这个示例
在本文中，我们将进行简单的线性回归分析，我们将使用它根据两个变量（送货距离和包裹被取走送货的小时）预测送货时间。
我们将使用并呈现地理数据作为分析的一部分，例如利用 Clickhouse 的 geoDistance 函数根据地理坐标计算距离。
数据集
我们的数据集是 Hugging Face 的 末端配送数据集 的一小部分提取。
虽然数据集很大且详细，但为了方便理解示例，我们将关注中国吉林省第 53 区由单一快递员（编号 75）送出的 2293 个订单的子集。
下面是数据的预览。我们只使用包含快递员取件和送货时间和地点的列，以及订单 ID。
SELECT *
FROM deliveries
LIMIT 5
┌─order_id─┬─────accept_gps_time─┬─accept_gps_lat─┬─accept_gps_lng─┬───delivery_gps_time─┬─delivery_gps_lat─┬─delivery_gps_lng─┐
│ 7350 │ 2022-07-15 08:45:00 │ 43.81204 │ 126.5669 │ 2022-07-15 13:38:00 │ 43.83002 │ 126.5517 │
│ 7540 │ 2022-07-21 08:27:00 │ 43.81219 │ 126.56692 │ 2022-07-21 14:27:00 │ 43.82541 │ 126.55379 │
│ 7660 │ 2022-08-30 08:30:00 │ 43.81199 │ 126.56993 │ 2022-08-30 13:52:00 │ 43.82757 │ 126.55321 │
│ 8542 │ 2022-08-19 09:09:00 │ 43.81219 │ 126.56689 │ 2022-08-19 15:59:00 │ 43.83033 │ 126.55078 │
│ 12350 │ 2022-08-05 08:52:00 │ 43.81215 │ 126.56693 │ 2022-08-05 09:10:00 │ 43.81307 │ 126.56889 │
└──────────┴─────────────────────┴────────────────┴────────────────┴─────────────────────┴──────────────────┴──────────────────┘
5 rows in set. Elapsed: 0.030 sec. Processed 2.29 thousand rows, 64.18 KB (75.64 thousand rows/s., 2.12 MB/s.)
Peak memory usage: 723.95 KiB.
使用我们的Hex 笔记本，我们可以轻松地渲染吉林周边配送地点的热图，观察到中心区域的配送更多
我们的模型还将考虑取件时间作为第二个变量。因此，我们还将可视化按取件小时数的订单数量分布，可以观察到大多数包裹是在早上 8 点收集的。
数据准备
我们的模型将预测取件和送货之间的时间差（以分钟为单位）作为取件地点和送货地点之间距离（以米为单位）和取件小时数的函数。
我们使用 Clickhouse 的 geoDistance 函数根据其坐标（纬度和经度）计算取件地点和送货地点之间的距离，而我们使用 Clickhouse 的 date_diff 函数计算取件和送货之间的时间差。
我们还使用 randUniform 函数向数据集添加随机生成的训练索引，该索引对于 80% 的数据（将用于训练）等于 1，对于剩余的 20% 的数据（将用于测试模型的性能）等于 0。
CREATE TABLE deliveries_dataset (
order_id UInt32,
delivery_time Float64,
delivery_distance Float64,
Hour7 Float64,
Hour8 Float64,
Hour9 Float64,
Hour10 Float64,
Hour11 Float64,
Hour12 Float64,
Hour13 Float64,
Hour14 Float64,
Hour15 Float64,
Hour16 Float64,
training Float64
)
ENGINE = MERGETREE
ORDER BY order_id
INSERT INTO deliveries_dataset
SELECT
order_id,
date_diff('minute', accept_gps_time, delivery_gps_time) as delivery_time,
geoDistance(accept_gps_lng, accept_gps_lat, delivery_gps_lng, delivery_gps_lat) as delivery_distance,
if(toHour(accept_gps_time) = 7, 1, 0) as Hour7,
if(toHour(accept_gps_time) = 8, 1, 0) as Hour8,
if(toHour(accept_gps_time) = 9, 1, 0) as Hour9,
if(toHour(accept_gps_time) = 10, 1, 0) as Hour10,
if(toHour(accept_gps_time) = 11, 1, 0) as Hour11,
if(toHour(accept_gps_time) = 12, 1, 0) as Hour12,
if(toHour(accept_gps_time) = 13, 1, 0) as Hour13,
if(toHour(accept_gps_time) = 14, 1, 0) as Hour14,
if(toHour(accept_gps_time) = 15, 1, 0) as Hour15,
if(toHour(accept_gps_time) = 16, 1, 0) as Hour16,
if(randUniform(0, 1) <= 0.8, 1, 0) as training
FROM
deliveries
可视化后，配送距离和配送时间呈正相关，随着行程的延长，方差更大。这与我们的直觉一致，因为更长的行程更难以预测。
模型训练
我们使用 Clickhouse 的 stochasticLinearRegression 函数根据包含训练数据的 80% 数据集拟合线性回归模型。
鉴于此函数使用梯度下降，我们将配送距离（这是唯一一个连续特征）通过减去训练集的平均值并除以训练集的标准差进行缩放。我们对目标取对数，以确保模型预测的送货时间永远不会为负。
CREATE VIEW deliveries_model AS WITH
(SELECT avg(delivery_distance) FROM deliveries_dataset WHERE training = 1) AS loc,
(SELECT stddevSamp(delivery_distance) FROM deliveries_dataset WHERE training = 1) AS scale
SELECT
stochasticLinearRegressionState(0.1, 0.0001, 15, 'SGD')(
log(delivery_time),
assumeNotNull((delivery_distance - loc) / scale),
Hour7,
Hour8,
Hour9,
Hour10,
Hour11,
Hour12,
Hour13,
Hour14,
Hour15,
Hour16
) AS STATE
FROM deliveries_dataset WHERE training = 1
模型评估
现在我们可以使用拟合后的模型对数据集剩余的 20% 进行预测。我们将通过比较预测的送货时间和实际时间来计算模型的准确性。
CREATE VIEW deliveries_results AS WITH
(SELECT avg(delivery_distance) FROM deliveries_dataset WHERE training = 1) AS loc,
(SELECT stddevSamp(delivery_distance) FROM deliveries_dataset WHERE training = 1) AS scale,
(SELECT state from deliveries_model) AS model
SELECT
toInt32(delivery_time) as ACTUAL,
toInt32(exp(evalMLMethod(
model,
assumeNotNull((delivery_distance - loc) / scale),
Hour7,
Hour8,
Hour9,
Hour10,
Hour11,
Hour12,
Hour13,
Hour14,
Hour15,
Hour16
))) AS PREDICTED
FROM deliveries_dataset WHERE training = 0
现在我们有了一个包含 20% 测试数据集的实际送货时间和预测送货时间的表。
SELECT * FROM deliveries_results LIMIT 10
┌─ACTUAL─┬─PREDICTED─┐
│ 410 │ 370 │
│ 101 │ 122 │
│ 361 │ 214 │
│ 189 │ 69 │
│ 122 │ 92 │
│ 454 │ 365 │
│ 155 │ 354 │
│ 323 │ 334 │
│ 145 │ 153 │
│ 17 │ 20 │
└────────┴───────────┘
10 rows in set. Elapsed: 0.015 sec. Processed 9.17 thousand rows, 267.76 KB (619.10 thousand rows/s., 18.07 MB/s.)
Peak memory usage: 2.28 MiB.
我们也可以在笔记本中按以下方式可视化这些数据
为了解释该图，如果模型表现完美，那么我们预计预测值和实际值在所有情况下都匹配，这意味着所有点都将位于橙色曲线线上。实际上，我们的模型确实存在误差，我们现在将对其进行分析。
模型性能
查看上面的可视化图，我们可以看到我们的模型在不到 120 分钟的较短行程中表现相对较好，但随着行程的延长，预测准确性开始下降，因为它们变得更加复杂，更难以预测。
这符合我们现实世界中的经验，即行程越长越艰苦，就越难以预测。
更科学地说，我们可以通过查看模型的平均绝对误差 (MAE) 和均方根误差 (RMSE) 来评估模型的性能。这给我们一个大约 1 小时的值，涵盖整个数据集
SELECT
avg(abs(ACTUAL - PREDICTED)) AS MAE,
sqrt(avg(pow(ACTUAL - PREDICTED, 2))) AS RMSE
FROM deliveries_results
┌───────────────MAE─┬──────────────RMSE─┐
│ 58.18494623655914 │ 78.10208373578114 │
└───────────────────┴───────────────────┘
1 row in set. Elapsed: 0.022 sec. Processed 9.17 thousand rows, 267.76 KB (407.90 thousand rows/s., 11.91 MB/s.)
Peak memory usage: 2.28 MiB.
如果我们将其限制在实际时间少于 2 小时（120 分钟）的较短行程，那么我们可以看到我们的模型表现更好，MAE 和 RMSE 更接近 30 分钟
SELECT
avg(abs(ACTUAL - PREDICTED)) AS MAE,
sqrt(avg(pow(ACTUAL - PREDICTED, 2))) AS RMSE
FROM deliveries_results
WHERE ACTUAL < 120
┌────────────────MAE─┬──────────────RMSE─┐
│ 29.681159420289855 │ 41.68671981213744 │
└────────────────────┴───────────────────┘
1 row in set. Elapsed: 0.014 sec. Processed 9.17 thousand rows, 267.76 KB (654.46 thousand rows/s., 19.11 MB/s.)
Peak memory usage: 2.35 MiB.
结论
在本文中，我们演示了如何使用简单的线性回归函数根据 2 个输入变量预测输出值。
模型的性能在较短距离上是合理的，但在输出变量变得难以预测时开始下降。也就是说，我们可以看到，完全在 ClickHouse 中进行的简单线性回归，只使用 2 个变量确实具有一定的预测能力，并且可能在其他数据集和领域中表现更好。
描述完整工作示例的笔记本可以在这里找到这里。
