Azure SQL数据库中数据点的T-SQL填充优化需求

嘿，我懂你现在的烦恼——手动添加的点没让线条达到想要的顺滑效果，对吧？在Azure SQL里实现数据插值让线条更连续，其实用窗口函数结合递归CTE或者数字表就能搞定，而且能解决你说的相对值问题。

核心思路是用线性插值在相邻原始数据点之间生成均匀分布的中间点，这样新增点的相对值会和原始点保持合理的比例，线条自然就连续了。下面给你两种实用的T-SQL方案：

方案1：用递归CTE生成插值点 #

这种方法适合数据量不大的场景，逻辑直观，容易调整插值密度。假设你的表叫data_points，有x_col（X轴字段，比如数值或时间）和y_col（Y轴数值字段）：

WITH ranked_points AS (
    -- 先给每个原始点标记它的下一个相邻点，计算差值
    SELECT 
        x_col,
        y_col,
        LEAD(x_col) OVER (ORDER BY x_col) AS next_x,
        LEAD(y_col) OVER (ORDER BY x_col) AS next_y,
        LEAD(x_col) OVER (ORDER BY x_col) - x_col AS x_diff,
        LEAD(y_col) OVER (ORDER BY x_col) - y_col AS y_diff
    FROM data_points
), interpolated_points AS (
    -- 第一步：包含所有原始点
    SELECT x_col, y_col, 0 AS step FROM ranked_points
    UNION ALL
    -- 第二步：递归生成中间插值点
    SELECT 
        -- 计算插值点的X值：原始X + 差值比例 * 当前步骤
        rp.x_col + (rp.x_diff / 5.0) * ip.step,
        -- 计算插值点的Y值：原始Y + 差值比例 * 当前步骤
        rp.y_col + (rp.y_diff / 5.0) * ip.step,
        ip.step + 1
    FROM ranked_points rp
    JOIN interpolated_points ip 
        ON rp.x_col = ip.x_col 
        AND ip.step < 5 -- 控制每个间隔生成的中间点数量（这里是4个中间点）
    WHERE rp.next_x IS NOT NULL -- 最后一个点不需要插值
)
-- 输出所有点（原始+插值）并排序
SELECT x_col, y_col FROM interpolated_points ORDER BY x_col;

方案2：用数字表生成插值点（更高效） #

如果你的数据量很大，递归CTE可能效率不高，这时候可以用数字表来批量生成插值点：

首先如果没有现成的数字表，先创建一个（按需添加更多数字）：

CREATE TABLE Numbers (n INT PRIMARY KEY);
INSERT INTO Numbers VALUES (1),(2),(3),(4),(5); -- 数字越多，插值点越密

然后执行插值逻辑：

WITH ranked_points AS (
    SELECT 
        x_col,
        y_col,
        LEAD(x_col) OVER (ORDER BY x_col) AS next_x,
        LEAD(y_col) OVER (ORDER BY x_col) AS next_y,
        LEAD(x_col) OVER (ORDER BY x_col) - x_col AS x_diff,
        LEAD(y_col) OVER (ORDER BY x_col) - y_col AS y_diff
    FROM data_points
)
-- 生成中间插值点
SELECT 
    rp.x_col + (rp.x_diff / (n.n + 1.0)) * n.n,
    rp.y_col + (rp.y_diff / (n.n + 1.0)) * n.n
FROM ranked_points rp
CROSS JOIN Numbers n
WHERE rp.next_x IS NOT NULL
-- 合并原始点
UNION ALL
SELECT x_col, y_col FROM data_points
-- 按X轴排序
ORDER BY x_col;

针对时间序列的特殊处理 #

如果你的X轴是datetime类型，只需要调整X值的计算逻辑，用DATEADD和DATEDIFF来处理时间差：

WITH ranked_points AS (
    SELECT 
        time_col,
        y_col,
        LEAD(time_col) OVER (ORDER BY time_col) AS next_time,
        LEAD(y_col) OVER (ORDER BY time_col) AS next_y,
        DATEDIFF(SECOND, time_col, LEAD(time_col) OVER (ORDER BY time_col)) AS time_diff_sec,
        LEAD(y_col) OVER (ORDER BY time_col) - y_col AS y_diff
    FROM data_points
), interpolated_points AS (
    SELECT time_col, y_col, 0 AS step FROM ranked_points
    UNION ALL
    SELECT 
        DATEADD(SECOND, (rp.time_diff_sec / 5.0) * ip.step, rp.time_col),
        rp.y_col + (rp.y_diff / 5.0) * ip.step,
        ip.step + 1
    FROM ranked_points rp
    JOIN interpolated_points ip 
        ON rp.time_col = ip.time_col 
        AND ip.step < 5
    WHERE rp.next_time IS NOT NULL
)
SELECT time_col, y_col FROM interpolated_points ORDER BY time_col;

关键调整点 #

• 替换data_points、x_col、y_col为你实际的表名和字段名
• 调整插值点数量：比如把方案1的ip.step < 5改成ip.step < 10，就能在每个间隔生成9个中间点，线条会更平滑
• 如果需要非线性插值（比如平滑曲线），可以在此基础上扩展算法，但线性插值已经能满足大部分“让线条更连续”的需求

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