SQL LEFT_JOIN表本身，同时替换列

Question

我有以下数据：

value1    value2    value3
qwe       bird      1
qwe                 2
qwe                 3
asd       dog       4
asd                 5

我想要以下数据：

value1    value2    value3
qwe       bird      1
qwe       bird      2
qwe       bird      3
asd       dog       4
asd       dog       5

对我来说，这个问题似乎可以通过将表中的两列单独连接起来，同时替换一列来解决。类似于：

# Selecting unique value1 and value2 combinations
SELECT value1, value2
FROM mytable
WHERE value2 != ''
GROUP BY value1, value2
# Left joining two tables
SELECT value1, value2
FROM selection_table
LEFT JOIN mytable
ON selection_table.value1 = mytable.value1;

我能以某种方式在一个调用中完成整个操作吗，这样我就不用制作中间表了？执行左联接时，是否可以覆盖value2列？

或者你有更聪明的方法来做到这一点吗？我相信肯定有一个：(

Answer 1

一个简单的解决方案应用窗口聚合

SELECT value1
,max(value2) -- group maximum
over (partition by value1)
,value3
from mytable

这通常比基于自联接的解决方案更有效。

Answer 2

在标准SQL中，可以使用lag(ignore nulls):

select t.*, lag(value2 ignore nulls) over (partition by value1 order by value3) 
from t;

但是，许多数据库没有实现此功能。一种方法是两个级别的聚合，一个用于获得具有值的value3，另一个用于扩展值：

select t.*,
max(value2) over (partition by value1, grp)
from (select t.*,
max(case when value2 is not null then value3 end) over (partition by value1 order by value3) as grp
from t
) t