这最初是 Ensemble Analytics 的一篇博文，他们慷慨地允许重新发布此内容。我们欢迎来自我们社区的帖子，并感谢他们的贡献。
简介
本文是我们研究在 ClickHouse 中进行数据科学工作系列文章的一部分。本系列文章包括预测、异常检测、线性回归和时间序列分类。
尽管这种类型的分析通常会在 ClickHouse 之外的编程语言（如 Python 或 R）中进行，但我们更倾向于尽可能仅使用数据库来完成。
通过这样做，我们可以依靠 ClickHouse 的强大功能来高性能地处理大型数据集，并减少甚至完全避免我们需要编写的代码量。这也意味着我们可以在客户端处理更小的内存数据集，并可能避免使用 Spark 等框架进行分布式计算的需求。
描述完整工作示例的 notebook 可以在这里找到。
关于本示例
在本文中，我们将进行一个简单的线性回归分析，我们将使用它来预测基于两个变量的交付时间 - 交付距离和包裹被取走交付的小时数。
我们将处理和渲染地理数据作为分析的一部分，例如使用 ClickHouse 的 geoDistance 函数来根据地理坐标计算距离。
数据集
我们的数据集是 Hugging Face 的这个最后一英里交付数据集的一个小型提取子集。
尽管数据集庞大而详细，但我们将查看中国吉林市 53 区的单个快递员 75 号交付的 2,293 个订单的子集，以便更容易理解该示例。
数据的预览如下所示。我们只使用包含快递员取件和交付的时间和位置以及订单 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 Notebook，我们可以轻松地渲染吉林周围交付位置的热图，观察到更多交付发生在中心区域
我们的模型还将考虑取件时间作为第二个变量。因此，我们还将可视化按取件小时数的订单数量分布，并且可以观察到大多数包裹在早上 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
我们现在有一个 ACTUAL 交付时间和 PREDICTED 交付时间表，用于我们数据集的 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.
我们还可以像下面在我们的 notebook 中那样以可视方式绘制这些图表
为了解释该图，如果模型表现完美，那么我们期望在每种情况下 PREDICTED 和 ACTUAL 都匹配，这意味着所有点都将在橙色曲线上对齐。实际上，我们的模型确实存在误差，我们现在将对其进行分析。
模型性能
查看上面的可视化，我们可以看到，对于小于 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 个变量的简单线性回归确实具有一定的预测能力，并且在其他数据集和领域中可能表现更好。
描述完整工作示例的 notebook 可以在这里找到。
