简单 group by :

select column1, 
       count(distinct column2) count, --remove distinct if you need count not null column2 by column1
                                      --use count(*) if you need count all rows by column1
       min(date)               min_date
from table
group by column1

我们来测试一下：

select column1, 
       count(distinct column2) count, --remove distinct if you need count not null column2 by column1
                                      --use count(*) if you need count all rows by column1
       min(date)               min_date
from (
select 
stack(6,
'a','a', 2016, 
'a','b', 2016, 
'a','c', 2017, 
'b','d', 2017, 
'b','e', 2017, 
'c','e', 2015) as( column1, column2, date)
) s
group by column1

结果：

a   3   2016    
b   2   2017    
c   1   2015

请注意，minu date为每个column1值选择了最小值。