sql - 带有 case when 和 group by 的 Value 列的计数

Question

table :

Date            Id       Value                    
2019-02-09      a1       ab            
2019-01-12      a2       cd
2019-12-12      a1       ab
2017-07-23      a1       ab
2018-12-09      a1       ab
2018-12-28      a1       cd
2016-11-09      a2       cd
2016-05-19      a2       ab

输出:

      Id        Max-Min               
      a1        1     
      a2       -1   

宗旨是打造 Max_year_count-Min_year_count per Id .
例如 Value column中的计算:
(count of occurrence of value in max_year with group by Id)-(count of occurrence of value in min_year with group by Id)谢谢 !!

Answer 1

Oracle 支持 FIRST/LAST对于聚合:

SELECT id,
    -- latest year's count
    Count(*) KEEP (Dense_Rank LAST  ORDER BY Extract(YEAR From "Date"))
     -- oldest year's count
  - Count(*) KEEP (Dense_Rank FIRST ORDER BY Extract(YEAR From "Date"))
FROM DATA
GROUP BY Id