使用高级group by编写查询

Question

我有一个表数据库，由以下字段组成: ID、资历(年份)、结果和其他一些不太重要的字段。表行示例：

ID:36     Seniority(years):1.79   outcome:9627

我需要用相对简单的代码编写一个查询(sql server)，它返回按资历字段分组的平均结果，间隔为5年(0-5年、6-10年等)。条件是只有当组的行数超过3行时，才会显示平均值。

结果行示例：

range:0-5    average:xxxx

非常感谢

Answer 1

使用CASE语句创建不同的年龄组。尝尝这个

select case when Seniority between 0 and 5 then '0-5' 
            when Seniority between 6 and 10 then '6-10' 
            ..
       End,
       Avg(outcome)
From yourtable
Group by case when Seniority between 0 and 5 then '0-5' 
            when Seniority between 6 and 10 then '6-10' 
            ..
         End
 Having count(1)>=3

由于您有小数位，因此如果您希望将5.4计数到0-5组，将5.6计数到6-10，那么在CASE语句中使用Round(Seniority,0)而不是Seniority

Answer 2

这将类似于：

select floor(seniority / 5), avg(outcome)
from t
group by floor(seniority / 5)
having count(*) >= 3;

注意:这将资历分为相等大小的组，即0-4、5-9等等。这似乎比拥有不平等的群体更合理。

Answer 3

你可以遵循戈登的答案(但你应该稍微修改一下)，但我会用包含所有可能间隔的附加表来做到这一点。然后，您可以添加适当的索引来提高它。

create table intervals
(
    id int identity(1, 1),
    start int,
    end int
)

insert into intervals values
(0, 5),
(6, 10)
...


select i.id, avg(t.outcome) as outcome
from intervals i
join tablename t on t.seniority between i.start and i.end
group by i.id
having count(*) >=3

如果不能创建新表，则可以始终使用CTE

;with intervals as(
                    select * from 
                    (values
                            (0, 5),
                            (6, 10)
                            --...
                    ) t(start, [end])
                  )
select i.id, avg(t.outcome) as outcome
from intervals i
join tablename t on t.seniority between i.start and i.[end]
group by i.id
having count(*) >=3