时间戳

首页 > 解决方案 > 十分位分析 - 客户和收入，反之亦然

sql - 十分位分析 - 客户和收入，反之亦然

问题描述

我正在尝试生成一个显示两件事的分析。

  1. 客户及其收入的十分位数（或百分位数），因此我可以看到 10% 的客户数量产生的收入最多。

  2. 收入的十分位数：有多少客户产生 10% 的收入。

select yeardate, decile, sum(revenue) as revenue,  count(distinct(customername)) as cust_count
 from 
(
select yeardate,
customername,
ntile(10) over (order by sum(revenue) ) as decile,
sum(revenue) as revenue
from 
(select 
year(DateStamp) as yeardate ,
customername, 
sum(Sell) as revenue
from MarginListView
where reporttype = 'Invoice' and sell >0 and year(datestamp) = 2018
group by year(DateStamp), customername) d
group by yeardate, CustomerName) c
group by yeardate, decile
order by 1,2

我可以得到客户计数十分位数，但不能得到收入十分位数......

使用 MS SQL 服务器 - 任何帮助表示赞赏。

标签： sqlsql-serverpercentile

解决方案

要获得产生 10% 收入的客户数量，请从累计收入开始：

select customername, sum(sell) as revenue,
       sum(sum(sell)) over (order by sum(sell) desc) as running_revenue
from MarginListView
where reporttype = 'Invoice' and sell > 0 and 
      year(datestamp) = 2018
group by customername;

要获得占 10% 的数字：

select count(*)
from (select customername, sum(sell) as revenue,
             sum(sum(sell)) over (order by sum(sell) desc) as running_revenue,
             sum(sum(sell)) over () as total_revenue
      from MarginListView
      where reporttype = 'Invoice' and sell > 0 and 
            year(datestamp) = 2018
      group by customername
     ) c
where running_revenue - revenue >= 0.1 * total_revenue;

推荐阅读