表格:
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)
谢谢!!
2条答案
按热度按时间5cnsuln71#
oracle支持聚合的first/last:
643ylb082#
您可以为每个项目创建一个包含最长和最短年份的派生表
Id
值,然后计算该值的年数id
匹配这些值并减去它们得到Max-Min
:输出:
在dbfiddle上演示