动机：这看起来很糟糕,但我正在尝试使用多个查询在Redshift中编写string_agg,这将合并相邻的行.我的最大组大小不是那么大,所以我认为查询只会运行几次迭代.我已经设法以一种形式预处理我的数据,
key | merge index | value a | 0 | foo a | 0 | bar a | 1 | baz b | 0 | fandangle
在一个步骤中,所有具有相同的内容(key,merge_index)应该连接在一起,所以我们得到了,
key | merge index | value a | 0 | foo,bar a | 1 | baz b | 0 | fandangle
我想在GROUP BY语句中使用first_value和last_value,如下所示,
SELECT key,merge_index,FIRST_VALUE(value) || COALESCE((',' || NTH_VALUE(value,2)),'')
GROUP BY key,merge_index;
但是,当然,你不能这样做,因为FIRST_VALUE和NTH_VALUE是窗口函数,而不是聚合函数.
问题：为什么我不能在GROUP BY组中使用FIRST_VALUE和朋友？
注意：它在功能上可以执行SELECT DISTINCT,省略GROUP BY,并使用相关的OVER(PARTITION BY键,merge_index)窗口,但我无法想象如果它试图对整个结果表进行重复数据删除,这是有效的.我还意识到我可以做更多的预处理,并添加一个像left_or_right这样的列,它指示它试图合并哪一侧,然后使用左连接.这似乎也没有太高效,但也许它并不坏.
解决方法
你尝试过类似下面的东西吗？这样您就可以避免FIRST_VALUE()和NTH_VALUE()以及聚合：
WITH p AS (
SELECT key,merge,value,ROW_NUMBER() OVER ( PARTITION BY key,merge ) AS rn
FROM mytable
)
SELECT p1.key,p1.merge,p1.value || p1.value || COALESCE(',' || p2.value,'')
FROM p p1 LEFT JOIN p p2
ON p1.key = p2.key
AND p1.merge = p2.merge
AND p2.rn = 2
WHERE p1.rn = 1
Please see SQL Fiddle demo here.是的,我确实使用Postgres 9作为小提琴;我无法在8上获得连接(但我认为我没有使用9的任何功能).
或者,您可以使用以下内容并避免自联接：
WITH p AS (
SELECT key,LEAD(value) OVER ( PARTITION BY key,merge ) AS next_value,merge ) AS rn
FROM mytable
)
SELECT key,value || COALESCE(',' || next_value,'')
FROM p
WHERE rn = 1
SQL Fiddle here.如果您事先知道连接需要多少个值,则可以使用增加的偏移值(more SQL Fiddle)对LEAD()进行多次调用：
WITH p AS (
SELECT key,LEAD(value,2) OVER ( PARTITION BY key,merge ) AS n2_value,3) OVER ( PARTITION BY key,merge ) AS n3_value,merge ) AS rn
FROM mytable
)
