基于存储桶sql的聚合

Question

我有下面的查询(它的一部分)，我想要的是首先为LTV制作桶，像0-0.2；0.2-0.4；0.4-0.6。然后在这些存储桶中，我想将它们划分为更小的存储桶，如2-4；4-6；6-8。所以总共有9个桶。

对于这些桶，我想取SavRate和SavIncentive的平均值，以及PartialPrepay和OutNot的和。

我该怎么做呢，非常感谢

   LTV             CRate    SavRate SavIncentive    PartialPrepay   OutNot
   0.6684459906     5,5     4,5     0,4              0           26,81
   0.1329765857     5,1     3       2,5              28          77,2
   0.212585034      6,8     4,5     2,3              17981       22689,01
   0.6613789002     4,3     3,2     1,1              17          37,04
   0.4251691757     6,3     3       3,3              99          121,09
   0.1774713259     4,9     3       1,9              0           63

Answer 1

您可以使用case语句定义存储桶。然后您可以按存储桶进行聚合：

select ltvBucket, CrateBucket,
       avg(SavRate), avg(SavIncentive), sum(PartialPrepay)
from (select t.*,
             (case when ltv between 0.0 and 0.2 then 'LTV:0.0-0.2'
                   when ltv between 0.2 and 0.4 then 'LTV:0.2-0.4'
                   when ltv between 0.4 and 0.6 then 'LTV:0.4-0.6'
                   when ltv between 0.6 and 0.8 then 'LTV:0.6-0.8'
                   else 'LTV:other'
             end) as ltvbucket,
            (case when Crate between 2 and 4 then 'Crate:2-4'
                  when Crate between 4 and 6 then 'Crate:4-6'
                  when Crate between 6 and 8 then 'Crate:6-8'
                  else 'Crate:Other'
            end) as CrateBucket
      from t
     ) t
group by ltvBucket, CrateBucket