我写了一个sql查询,它为所有销售代表计算我过去三个月的销售额四分位数,并在存储过程中的临时表中捕获,如下所示:
过去三个月所有销售代表的四分位数:
Date 25th% 50th% 75th% 100th%
10/2020 88.89 90.00 95.00 100.00
11/2020 85.63 91.00 96.00 100.00
12/2020 70.00 80 .00 90.00 100.00
现在,在我的另一个CTE中,我有销售代表的实际价值,如下所示:
SalesRepId Month salesvalue
101 10/2020 77
101 11/2020 90
101 12/2020 100
当我同时连接cte和temp表时,查询性能很差,在temp表中查找销售值并将四分位数分配给我的salesrepid的最佳方法是什么?
基本上,对于2020年10月,销售额77小于第25个四分位数,那么销售代表应该为10月份分配第25个分位数。
谢谢
这正是percentile_disc()
和percentile_cont()
。不幸的是,这些不是聚合函数,但有一种方法是:
select distinct month,
percentile_disc(0.25) over (partition by month order by salesvalue) as value_25,
percentile_disc(0.50) over (partition by month order by salesvalue) as value_50,
percentile_disc(0.75) over (partition by month order by salesvalue) as value_75
from sales;
如果你想计算四分位数,最简单的方法是ntile()
:
select s.*,
ntile(4) over (partition by month order by sales)
from sales s;
你不需要计算休息时间。对ntile()
的一个警告是瓦片的大小尽可能接近。这意味着领带可以在不同的瓷砖中。要解决这个问题,只需手动进行计算:
select s.*,
ceiling(rank() over (partition by month order by sales) * 4.0 /
count(*) over (partition by month)
) as quartile
我更新了查询,这样我就可以将数据保存到临时表中,而不是CTE中。现在,当我加入临时工表时,一切都很轻松。
https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/