SQL Server语言 - 连接前 5 行

Question

我有sql代码，它在分区和排序后在列1中创建前5行的平均值：

CAST (AVG(col1) OVER (Partition by col2 ORDER BY col3 ASC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS DECIMAL(3,2)) AS col1avg

我想做类似的事情，但我想创建一个字符串，其中 5 个值用逗号分隔，而不是平均 5 个值。 我尝试了多个想法，但没有成功。 任何帮助将不胜感激。

Answer 1

SQL Server 2012+ 中最简单的方法是使用CONCAT()和LAG()：

select CONCAT(LAG(col1, 1) OVER (PARTITION BY col2 ORDER BY col3), ',',
LAG(col1, 2) OVER (PARTITION BY col2 ORDER BY col3), ',',
LAG(col1, 3) OVER (PARTITION BY col2 ORDER BY col3), ',',
LAG(col1, 4) OVER (PARTITION BY col2 ORDER BY col3), ','
LAG(col1, 5) OVER (PARTITION BY col2 ORDER BY col3)
)

如果您需要处理NULL值，这有点棘手。 我建议使用空格、修剪和替换：

select REPLACE(RTRIM(CONCAT(LAG(col1, 1, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
LAG(col1, 2, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
LAG(col1, 3, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
LAG(col1, 4, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
LAG(col1, 5, '') OVER (PARTITION BY col2 ORDER BY col3)
)
), ' ', ','
)