如何获取月度/季度销售百分比

我有每月销量如何获取月度/季度销售百分比

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

回到顶部