该季度的平均销售与上一季度的平均销售 [英] avg sale of quarter with previous quarter avg sale
问题描述
我有一张表，其中有各种属性，如地区产品，年，qtr，月，销售.我必须计算每个具有相同区域的产品的avg_qtr销售，并显示其先前的avg_qtr销售.我的表结构是这样的
I have a table one in which there are various attribute like region product,year,qtr,month,sale. I have to calculate the avg_qtr sale of each product having same region and show their previous avg_qtr sale.I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated. My table structure is like this
Region Product Year Qtr Month Sales
NORTH P1 2015 1 JAN 1000
NORTH P1 2015 1 FEB 2000
NORTH P1 2015 1 MAR 3000
NORTH P1 2015 2 APR 4000
NORTH P1 2015 2 MAY 5000
NORTH P1 2015 2 JUN 6000
NORTH P1 2015 3 JUL 7000
NORTH P1 2015 3 AUG 8000
NORTH P1 2015 3 SEP 9000
NORTH P1 2015 4 OCT 1000
NORTH P1 2015 4 DEC 4000
NORTH P1 2015 4 NOV 2000
NORTH P3 2015 1 FEB 1000
NORTH P3 2015 1 FEB 9000
NORTH P3 2015 2 APR 2000
NORTH P3 2015 3 JUL 8000
NORTH P1 2016 1 MAR 3000
NORTH P1 2016 1 FEB 1000
NORTH P1 2016 1 JAN 2000
SOUTH P1 2015 1 JAN 2000
SOUTH P1 2015 1 FEB 3000
SOUTH P1 2015 1 JAN 4000
SOUTH P2 2015 1 MAR 1000
SOUTH P2 2015 1 JAN 8000
SOUTH P2 2015 1 FEB 9000
SOUTH P2 2015 2 JUN 9000
SOUTH P2 2015 2 MAY 8000
SOUTH P2 2015 2 APR 2000
SOUTH P2 2015 3 SEP 4000
SOUTH P2 2015 3 AUG 2000
SOUTH P2 2015 3 JUL 1000
SOUTH P2 2015 4 NOV 2000
SOUTH P2 2015 4 DEC 1000
SOUTH P2 2015 4 OCT 5000
SOUTH P3 2015 3 AUG 9000
SOUTH P3 2015 4 OCT 1000
SOUTH P3 2015 4 NOV 3000
SOUTH P2 2016 1 JAN 2000
SOUTH P2 2016 1 JAN 4000
我写了一个查询来计算当前的qtr并显示当前的前一个平均值和当前的平均值
I wrote the query which calculates current qtr and is showing previous one avg with current one
WITH AvgSales
AS (SELECT
region,
product,
year,
qtr,
ROUND(AVG(sales), 2) AS avg_Sale
FROM one
GROUP BY region,
product,
year,qtr
)
SELECT
s.region,
s.product,
s.year,
s.month,
s.sales,
avg.qtr,
avg.avg_Sale AS Qtr_Avg_Sale,
prev.avg_sale AS Prev_Qtr_Avg_Sale
FROM one s
JOIN AvgSales avg
ON s.region = avg.region
AND s.product = avg.product
AND s.QTR = avg.qtr
AND s.year = avg.year
LEFT JOIN AvgSales prev
ON (s.region = prev.region
AND s.product = prev.product
AND s.year - 1 = prev.year
and s.qtr=1
AND prev.qtr = 4) or
(s.region = prev.region
AND s.product = prev.product
AND s.year = prev.year
AND s.qtr - 1 = prev.qtr) ;
我能够获得该产品的当前平均值和以前的平均值，反之亦然.我不确定如何显示该季度的上一个平均值，该季度在当前季度没有任何销售. 我想要这样的输出-
I am able to get current average and previous average of that product but not vice versa. I am not sure how to show the previous average of that quarter which does not have any sale in current quarter. I want a output like this-
Region Product Year qtr month sale avg_Sale prev_avg_sale
NORTH P1 2015 1 JAN 1000 2000
NORTH P1 2015 1 FEB 2000 2000
NORTH P1 2015 1 MAR 3000 2000
NORTH P1 2015 2 APR 4000 5000 2000
NORTH P1 2015 2 MAY 5000 5000 2000
NORTH P1 2015 2 JUN 6000 5000 2000
NORTH P1 2015 3 JUL 7000 8000 5000
NORTH P1 2015 3 AUG 8000 8000 5000
NORTH P1 2015 3 SEP 9000 8000 5000
NORTH P1 2015 4 OCT 1000 2333.33 8000
NORTH P1 2015 4 NOV 2000 2333.33 8000
NORTH P1 2015 4 DEC 4000 2333.33 8000
SOUTH P2 2015 1 JAN 8000 6000
SOUTH P2 2015 1 FEB 9000 6000
SOUTH P2 2015 1 MAR 1000 6000
SOUTH P2 2015 2 APR 2000 6333.33 6000
SOUTH P2 2015 2 MAY 8000 6333.33 6000
SOUTH P2 2015 2 JUN 9000 6333.33 6000
SOUTH P2 2015 3 JUL 1000 2333.33 6333.33
SOUTH P2 2015 3 AUG 2000 2333.33 6333.33
SOUTH P2 2015 3 SEP 4000 2333.33 6333.33
SOUTH P2 2015 4 OCT 5000 2666.67 2333.33
SOUTH P2 2015 4 NOV 2000 2666.67 2333.33
SOUTH P2 2015 4 DEC 1000 2666.67 2333.33
NORTH P3 2015 1 FEB 9000 5000
NORTH P3 2015 1 FEB 1000 5000
NORTH P3 2015 2 APR 2000 2000 5000
NORTH P3 2015 3 JUL 8000 8000 2000
SOUTH P3 2015 3 AUG 9000 9000
SOUTH P3 2015 4 OCT 1000 2000 9000
SOUTH P3 2015 4 NOV 3000 2000 9000
NORTH P1 2016 1 JAN 2000 2000 2333.33
NORTH P1 2016 1 FEB 1000 2000 2333.33
NORTH P1 2016 1 MAR 3000 2000 2333.33
NORTH P2 2016 2 2000
SOUTH P2 2016 1 JAN 2000 3000 2666.67
SOUTH P2 2016 1 JAN 4000 3000 2666.67
SOUTH P2 2016 2 3000
SOUTH P1 2015 1 JAN 4000 3000
SOUTH P1 2015 1 JAN 2000 3000
SOUTH P1 2015 1 FEB 3000 3000
推荐答案
如果您要对单个有序值进行排序，则可以使用分析函数的windowing子句，因此请先创建年份和qtr的DENSE_RANK ing ，然后在您的分析函数中使用该排名:
You can use the windowing clause of an analytic function if you have a single ordered value to sort by, so first create a DENSE_RANKing of year and qtr, then use that ranking in your analytic functions:
with t1 as (
select one.*
, dense_rank() over (order by year, qtr) qord
from one
)
select product
, year
, qtr
, month
, sales
, round(avg(sales) over (partition by qord),2) qtr_avg
, round(avg(sales) over (order by qord
range between 1 preceding
and 1 preceding),2) prev_qtr_avg
from t1
以上解决方案假设样本数据集中提供了密集的季度数据，但是，如果数据沿四分之一维稀疏，则可以按照以下查询先对数据进行致密化:
The above solution assumes dense quarterly data as provided in the sample data set, if however, the data is sparse along the quarter dimension you can first densify the data as in this query:
with qtrs as (select level qtr from dual connect by level <=4)
, t1 as (
select product
, year
, qtrs.qtr
, month
, sales
, dense_rank() over (order by year, qtrs.qtr) qord
from qtrs
left outer join one partition by (year)
on one.qtr = qtrs.qtr
)
select product
, year
, qtr
, month
, sales
, round(avg(sales) over (partition by qord),2) qtr_avg
, round(avg(sales) over (order by qord
range between 1 preceding
and 1 preceding),2) prev_qtr_avg
from t1
这样可确保对于数据中表示的每一年，每个季度至少存在一行，因此QORD将枚举每个季度，并且数据中的差异将导致计算出的季度平均值出现差异.
This ensures that for every year represented in the data at least one row will exist for each quarter, and consequently QORD will enumerate every quarter, and gaps in the data will result in gaps in the calculated quarterly averages.
您还可以通过利用YEAR和QTR的数字性质来更改QORD的计算方式来达到类似的效果，如本例所示:
You can also achieve a similar effect by altering the way QORD is calculated by exploiting the numeric natures of YEAR and QTR as in this example:
with t1 as (select one.*, year*4+qtr qord from one)
select product
, year
, qtr
, month
, sales
, round(avg(sales) over (partition by qord),2) qtr_avg
, round(avg(sales) over (order by qord
range between 1 preceding
and 1 preceding),2) prev_qtr_avg
from t1
这里不需要进行致密化，但是它仍然正确地保留了prev_qtr_avg中的空白，但是它确实遗漏了致密数据包括的丢失季度的记录.
Here no densification was required, and yet it still correctly leaves gaps in the prev_qtr_avg, but it does leave out records for missing quarters which the densified data includes.
结合最后两个示例，并在您对区域的新要求中添加每个季度(如果每个不同的区域，产品和年份都需要)至少每季度返回一行数据.这两个平均值均按地区和产品划分，并根据当前或上一季度(视情况而定)进行计算:
Combining the last two examples, and adding in your new requirement for regions a at least one row of data per quarter will be returned or generated if required for every distinct region, product and year. Both averages are partitioned by region and product and calculated per current or previous quarter as the case may be:
with qtrs(qtr) as (select level from dual connect by level <= 4)
, t1 as (
select region, product, year, q.qtr, month, sales, year*4+q.qtr qord
from qtrs q
left join one partition by (region, product, year)
on q.qtr = one.qtr
)
select region
, product
, year
, qtr
, month
, sales
, round(avg(sales) over (partition by region, product, qord),2) avg_sale
, round(avg(sales) over (partition by region, product
order by qord
range between 1 preceding
and 1 preceding),2) prev_avg_sale
from t1
order by year, region, qtr, product;
这篇关于该季度的平均销售与上一季度的平均销售的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
