SQL是否有类似R shift.left的函数？如何动态实现列数据左移？

需求背景 #

需要实现报表中每行数据依次左移一位，效果等价于R语言的matrix.shift.left。现有数据表结构、数据如下：

数据表结构与数据 #

create table sample_data (
    year_month_str char(7),
    month01 int,
    month02  int,
    month03  int
);

insert into sample_data values 
    ('2021-01',49000,25000,24000),
    ('2021-02',null,35000,19000),
    ('2021-03',null,null,34000)
;

当前数据展示 #

期望左移结果 #

已实现的硬编码方式 #

用户已掌握硬编码查询实现，但扩展性差：

select
    year_month_str,
    case when year_month_str = '2021-01' then month01
         when year_month_str = '2021-02' then month02
         when year_month_str = '2021-03' then month03
         else null
         end
         as month01,
    case when year_month_str = '2021-01' then month02
         when year_month_str = '2021-02' then month03
         else null
         end
         as month02,
    case when year_month_str = '2021-01' then month03
         else null
         end
         as month03
from 
    sample_data
;

核心疑问 #

1. SQL中是否存在类似R语言matrix.shift.left的等价方法？
2. 能否在不使用动态SQL的前提下，实现对任意数量字段和记录（方阵结构）的动态处理？

解决方案 #

一、SQL中实现类似matrix.shift.left的等价逻辑 #

SQL本身没有直接对应matrix.shift.left的内置函数，但可以通过行列转换+窗口函数或条件逻辑+字段偏移实现核心效果，针对场景有两种常用思路：

思路1：基于UNPIVOT+PIVOT的通用方法

先将列转成行，计算每个值的目标位置，再转回列结构：

WITH unpivoted AS (
    SELECT 
        year_month_str,
        CAST(SUBSTRING(month_col, 6) AS INT) AS month_idx,
        month_val
    FROM sample_data
    UNPIVOT (
        month_val FOR month_col IN (month01, month02, month03)
    ) AS up
    WHERE month_val IS NOT NULL
),
shifted AS (
    SELECT 
        year_month_str,
        month_val,
        -- 目标列索引=原索引 - 行偏移量（当前行与首行的月份差）
        month_idx - DATEDIFF(MONTH, '2021-01', year_month_str) AS target_idx
    FROM unpivoted
)
SELECT 
    year_month_str,
    MAX(CASE WHEN target_idx = 1 THEN month_val END) AS month01,
    MAX(CASE WHEN target_idx = 2 THEN month_val END) AS month02,
    MAX(CASE WHEN target_idx = 3 THEN month_val END) AS month03
FROM shifted
GROUP BY year_month_str
ORDER BY year_month_str;

此方法通过行列转换重新计算非空值的位置，逻辑比硬编码更通用，无需针对每个日期写判断。

思路2：利用窗口函数的批量偏移（适用于方阵场景）

如果数据是标准方阵（N行N列，第k行前k-1列为空），可通过窗口函数按顺序提取非空值，再分配到对应列：

WITH all_values AS (
    SELECT 
        year_month_str,
        month_val,
        ROW_NUMBER() OVER (ORDER BY year_month_str, month_idx) AS val_seq
    FROM (
        SELECT 
            year_month_str,
            CAST(SUBSTRING(month_col, 6) AS INT) AS month_idx,
            month_val
        FROM sample_data
        UNPIVOT (
            month_val FOR month_col IN (month01, month02, month03)
        ) AS up
        WHERE month_val IS NOT NULL
    ) t
),
row_seqs AS (
    SELECT 
        year_month_str,
        ROW_NUMBER() OVER (ORDER BY year_month_str) AS row_seq
    FROM sample_data
)
SELECT 
    rs.year_month_str,
    MAX(CASE WHEN av.val_seq = rs.row_seq THEN av.month_val END) AS month01,
    MAX(CASE WHEN av.val_seq = rs.row_seq + 1 THEN av.month_val END) AS month02,
    MAX(CASE WHEN av.val_seq = rs.row_seq + 2 THEN av.month_val END) AS month03
FROM row_seqs rs
LEFT JOIN all_values av ON av.val_seq BETWEEN rs.row_seq AND rs.row_seq + 2
GROUP BY rs.year_month_str
ORDER BY rs.year_month_str;

二、不使用动态SQL处理任意方阵的可能性 #

纯静态SQL无法完全实现任意数量字段和记录的动态宽表输出，因为SQL是静态编译语言，必须在编译阶段确定返回的列数和结构。但可以通过以下方式接近“动态”效果：

1. 返回长表格式：放弃宽表结构，输出year_month_str、target_month、value的长表，后续报表工具可再转成宽表：

WITH unpivoted AS (
    SELECT 
        year_month_str,
        CAST(SUBSTRING(month_col, 6) AS INT) AS month_idx,
        month_val
    FROM sample_data
    UNPIVOT (
        month_val FOR month_col IN (month01, month02, month03)
    ) AS up
    WHERE month_val IS NOT NULL
),
shifted AS (
    SELECT 
        year_month_str,
        month_val,
        'month' || LPAD(CAST(month_idx - DATEDIFF(MONTH, MIN(year_month_str) OVER(), year_month_str) AS VARCHAR), 2, '0') AS target_month
    FROM unpivoted
)
SELECT * FROM shifted ORDER BY year_month_str, target_month;

输出结果示例：

2. 预留足够列数：预先定义超出预期的列数（比如预留12个月列），超出部分留空，适用于字段数量可预估的场景。

总结 #

• SQL没有直接对应matrix.shift.left的内置函数，但通过行列转换+窗口函数可实现等价逻辑，比硬编码更通用。
• 纯静态SQL无法实现任意列数的动态宽表输出，但可通过长表格式规避限制，或依赖数据库特定动态透视功能（需动态SQL）。

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