如何获取月度/季度销售百分比
我有每月销量如何获取月度/季度销售百分比
Quarter Month Monthly Sales 1 Jan 586
1 Feb 204
1 Mar 465
2 Apr 684
2 May 756
2 Jun 97
现在我想下面
Quarter Month Monthly Sales Total Sales% Quarterly Sales% 1 Jan 586 20.98% 45.25%
1 Feb 204 7.30% 16.25%
1 Mar 465 16.65% 37.05%
2 Apr 684 24.49% 44.50%
2 May 756 27.07% 49.18%
2 Jun 97 3.47 6.31%
总销售额%结果的=每月销售/总和的表(每月销售) 季度销售额%=月销售额/季度销售额
如何在SQL中获取此输出?
回答:
使用CROSS APPLY
:
SELECT s.*,
[Total Sales %] = CAST(CAST(s.MonthlySales/t.Total * 100.00 AS DECIMAL(5, 2)) AS VARCHAR(5)) +'%',
[Quarterly Sales %] = CAST(CAST(s.MonthlySales/q.QtrTotal* 100.00 AS DECIMAL(5, 2)) AS VARCHAR(5)) +'%'
FROM Sales s
CROSS APPLY(
SELECT Total = SUM(MonthlySales) * 1.0
FROM Sales
) t
CROSS APPLY(
SELECT QtrTotal = SUM(MonthlySales) * 1.0
FROM Sales
WHERE Quarter = s.Quarter
GROUP BY Quarter
)q
回答:
如果您使用的是Oracle,你应该使用ratio_to_report
解析函数。 (http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions124.htm)
select quarter, month,
monthly_sales,
round(ratio_to_report(monthly_sales) over() * 100, 2) as total_sales_pct,
round(ratio_to_report(monthly_sales) over(partition by quarter) * 100, 2) as qtr_sales_pct
from monthly_sales;
小提琴:http://sqlfiddle.com/#!4/71aeb7/1/0
以上假设你的短短一年的解析函数会解释给定结果集选择数据。如果您的查询跨越多年,您需要另外分区表示实际表中的年份。
以上是 如何获取月度/季度销售百分比 的全部内容, 来源链接: utcz.com/qa/257684.html