值列的计数，带有case when和group by

km0tfn4u 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(494)

表格：

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) 谢谢！！

sql oracle oracle-sqldeveloper

来源：https://stackoverflow.com/questions/61906138/count-of-value-column-with-case-when-and-group-by

2条答案

按热度按时间

5cnsuln71#

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

赞(0）回复(0）举报 2021-08-09

643ylb082#

您可以为每个项目创建一个包含最长和最短年份的派生表 Id 值，然后计算该值的年数 id 匹配这些值并减去它们得到 Max-Min :

SELECT d.Id,
       COUNT(CASE WHEN EXTRACT(YEAR FROM "Date") = Max_Year THEN 1 END) -
       COUNT(CASE WHEN EXTRACT(YEAR FROM "Date") = Min_Year THEN 1 END) AS "Max-Min"
FROM data d
JOIN (SELECT Id, 
             MAX(EXTRACT(YEAR FROM "Date")) AS Max_Year,
             MIN(EXTRACT(YEAR FROM "Date")) AS Min_Year
      FROM data
      GROUP BY Id) m ON m.Id = d.Id
GROUP BY d.Id

输出：

ID  Max-Min
a1  1
a2  -1

在dbfiddle上演示

赞(0）回复(0）举报 2021-08-09

我来回答

值列的计数，带有case when和group by

2条答案

相关问题

热门标签

最新问答