基于Snowflake SQL用线性回归函数计算Nov-22/Dec-22趋势

我需要基于给定的Mar-22至Oct-22的x（1-8）与y值数据，计算x=9（Nov-22）和x=10（Dec-22）的趋势值，请问能否使用SQL线性回归函数实现该需求？

示例数据 #

with
data_table(month, x, y) as
(
    select * from values
      ('Mar-22',1 ,7894),
      ('Apr-22',2 ,7964),
      ('May-22',3 ,8016),
      ('Jun-22', 4, 8005),
      ('Jul-22', 5, 8063),
      ('Aug-22', 6, 8101),
      ('Sep-22', 7, 8101),
      ('Oct-22', 8, 8204)
)
SELECT * FROM data_table;

期望输出 #

可以，你可以利用SQL的REGR_SLOPE（计算回归斜率）和REGR_INTERCEPT（计算回归截距）这两个线性回归聚合函数来实现需求。线性回归的核心公式为：趋势值 = 截距 + 斜率 × x，通过这两个函数得到全局的斜率和截距后，就能为已知x和待预测的x计算对应的趋势值。

完整SQL代码 #

with
data_table(month, x, y) as
(
    select * from values
      ('Mar-22',1 ,7894),
      ('Apr-22',2 ,7964),
      ('May-22',3 ,8016),
      ('Jun-22', 4, 8005),
      ('Jul-22', 5, 8063),
      ('Aug-22', 6, 8101),
      ('Sep-22', 7, 8101),
      ('Oct-22', 8, 8204)
),
-- 计算线性回归的斜率和截距参数
regression_params as (
    select
        regr_slope(y, x) as slope,
        regr_intercept(y, x) as intercept
    from data_table
),
-- 合并原有数据与待预测的x值（9和10）
all_records as (
    select month, x, y from data_table
    union all
    select 'Nov-22', 9, null union all
    select 'Dec-22', 10, null
)
select
    month as "Month",
    x as "Order",
    -- 原有数据保留原始y值，预测数据用趋势值填充（与示例输出对齐）
    coalesce(y, round(intercept + slope * x, 6)) as "Amount",
    round(intercept + slope * x, 8) as "Trend"
from all_records, regression_params
order by x;

代码说明 #

1. regression_params：基于全部样本数据，计算y关于x的线性回归斜率和截距，这两个参数是全局唯一的。
2. all_records：将原始数据与需要预测的x=9（Nov-22）、x=10（Dec-22）的记录合并，预测记录的y值设为null。
3. 最终查询：通过线性回归公式计算每个x对应的趋势值；对于原始数据，保留其y值作为Amount；预测数据则用计算出的趋势值填充Amount列，最后按x排序输出结果。

执行该代码后，即可得到与期望格式一致的输出，包含原有数据的趋势值以及Nov-22、Dec-22的预测趋势值。

内容的提问来源于stack exchange，提问作者user21359260