如果只需要有效值，可以使用where-between

select  ticker, max(value), year
    from my_table 
    where year between 2008 and 2010 
    group by ticker, year

    or if need  all the year you could build the set using union  

   select  m.ticker, max(coalesce(m.value,0)), m.year
    from  (
        select 2008 year
        union
        select 2009 year
        union 
        select 2010 year
        ) t
    left join my_table m on t.year = m.year
    group by ticker, year

如果你想为每一个股票代码和年份排一行，那么使用 cross join 生成行和 left join 引入价值观。多年来，使用子查询生成值非常容易：

select t.ticker, y.year, coalesce(max(d.value, 0)) as max_value
from (select distinct ticker from dataset) t cross join
     (select 2008 as year union all select 2009 union all select 2020) y left join
     dataset d
     on d.ticker = t.ticker and d.year = y.year
group by t.ticker, y.year
order by t.ticker, y.year;

对于较大的范围，可以使用如下子查询：

(select row_number() over () + 2008 - 1 as year
 from dataset
 limit 3
) y