主页 / dba / 问题 / 218163
Accepted
Bill Hughes
Asked: 2018-09-21 05:50:40 +0800 CST

案例未按预期工作的百分比计算

  • 772

从昨天下午开始就一直在绞尽脑汁，我想是时候召唤那些比我聪明的人了。我有一些数据代表如下。抱歉，我必须使用图像，我无法弄清楚如何设置格式以使其看起来像表格。

create table fico_scores
(
    fundingyear int,
    fico int,
    Correspondent varchar(50)
)

insert into fico_scores (fundingyear,fico,Correspondent)
        values (2017,593,'Amer Equit Mtg'),(2017,703,'Amer Equit Mtg'), 
(2018,684,'Amer Equit Mtg'),
               (2017,691,'Amer Equit Mtg'),(2017,650,'Amer Equit Mtg'),(2017,697,'Am Corp'),
               (2017,652,'Am Corp'),(2017,792,'Am Corp'),(2018,0,'Am Corp'),
               (2018,796,'Am Corp'),(2017,729,'Appr Fun Corp'),(2017,748,'Appr Fun Corp'),
               (2017,780,'Appr Fun Corp'),(2018,770,'Appr Fun Corp'),(2017,663,'Appr Fun Corp'),
               (2018,752,'Appr Fun Corp'),(2018,715,'Cacl Risk Analytics'),(2018,683,'Cacl Risk Analytics'),
               (2018,735,'Cacl Risk Analytics'),(2017,693,'Cap Mtg LLC'),(2017,624,'Cap Mtg LLC'),(2018,746,'Cap Mtg LLC'),
               (2018,666,'Cap Mtg LLC'),(2018,669,'Cap Mtg LLC')

我有以下代码，当运行它时只看一个通讯员，一切看起来都很好，正如您在代码下方看到的那样。

select fundingyear,case
                when fico = 1 then 'Less than 620'
                when fico = 2 then '620-679'
                when fico = 3 then '680-719'
                when fico = 4 then '720+' end as fico,
                sum(countfico) as count_Fico, count(countfico) * 1.0 / 
           SUM(count(countfico)) OVER (PARTITION BY fundingyear) as fico_perc
FROM
(
    select fundingyear, case 
                    when fico < 620 then 1
                    when fico >= 620 and fico <= 679 then 2
                    when fico >= 680 and fico <= 719 then 3
                    when fico >= 720 then 4
                    end as fico, count(fico) as countfico
    from vw_LoanRpt
    where Correspondent = 'Am Corp'
    group by fico,FundingYear
) t
group by FundingYear,fico

相应的百分比阵容

如您所见，百分比与计数正确对齐。但是当我运行代码来查看所有通讯员时，它看起来真的很不一样。

百分比不一致

我的问题是，我做错了什么导致计算出现这种偏差？

再次抱歉使用了这么多图片。我最终会学习这种格式化的东西。

蒂亚！

                    2017          2018
Less Than 620      521 | 14%    499 | 13%
620 - 679         2027 | 28%   2619 | 31%
680 - 719         1260 | 21%   1661 | 20%
720+              2137 | 37%   3320 | 35%
sql-server t-sql

1 个回答

  1. Best Answer

    Quick-n-dirty 双/CTE 解决方案：

    -- sum up the fico groups

with fico_groups as
(select   fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end as fico_txt,
          count(fico) as count_fico
 from     fico_scores
 group by fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end
),

-- get the yearly totals

fico_totals as
(select   fundingyear,
          sum(count_fico) as fico_total
 from     fico_groups
 group by fundingyear
)

-- final result set

select fg.fundingyear,
       fg.fico_txt                           as fico,
       fg.count_fico,
       (fg.count_fico *1.0 / ft.fico_total)  as fico_perc

from   fico_groups fg,
       fico_totals ft

where  fg.fundingyear = ft.fundingyear

order by fg.fundingyear, fg.fico_txt

    结果集（基于OP提供的样本数据）：

    fundingyear   fico      count_fico  fico_perc
2017          0-619     1           0,076923076923
2017          620-679   4           0,307692307692
2017          680-719   4           0,307692307692
2017          720+      4           0,307692307692
2018          0-619     1           0,090909090909
2018          620-679   2           0,181818181818
2018          680-719   3           0,272727272727
2018          720+      5           0,454545454545

    这是上面的数据库小提琴。

    • 1

相关问题