显示上一季度平均销售与当前季度平均销售 [英] show previous quarter avg sale with current quarter avg sale
问题描述
我有一个表格，其中有各种属性，如地区产品，年份，季节，月份，销售。我必须计算具有相同区域的每个产品的avg_qtr销售并显示他们之前的avg_qtr销售。我已经阅读了关于滞后但是这里不可能使用，因为它将在重复多少行之后不固定。我的桌子结构是这样的
地区产品年度QT月销售额
NORTH P1 2015 1 JAN 1000
NORTH P1 2015年2月1日
NORTH P1 2015 1月3月3日
NORTH P1 2015 2 APR 4000
NORTH P1 2015 2 MAY 5000
NORTH P1 2015 2月6月$
NORTH P1 2015 3月7日
NORTH P1 2015年8月3日
NORTH P1 2015 3 SEP 9000
NORTH P1 2015 4 OCT 1000
NORTH P1 2015 4 DEC 4000
NORTH P1 2015年4月4日
NORTH P3 2015 1 FEB 1000
NORTH P3 2015 1 FEB 9000
NORTH P3 2015 2 APR 2000
NORTH P3 2015 3月8日
NORTH P1 2016 1月3月3000
NORTH P1 2016 1 FEB 1000
NORTH P1 2016 1 2000年1月
SOUTH P1 2015年1月1日
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月9000
SOUTH P2 2015年5月2日8000
SOUTH P2 2015年2月2日
SOUTH P2 2015 3 SEP 4000
SOUTH P2 2015年3月3日
SOUTH P2 2015 3月1000
SOUTH P2 2015 4 NOV 2000
SOUTH P2 2015 4 DEC 1000
SOUTH P2 2015 4 OCT 5000
SOUTH P3 2015年9月3日
SOUTH P3 2015 4月1000日
SOUTH P3 2015 4 NOV 3000
SOUTH P2 2016年1月1日
SOUTH P2 2016 1 JAN 4000
我写了查询计算当前qtr并显示previo我们一个平均当前一个
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
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) ;
我能够获得该产品的当前平均值和之前的平均值，但反之亦然。我不知道如何显示该季度以前没有任何销售的季度。
我想要这样的输出 -
地区产品年份qtr month sale avg_Sale prev_avg_sale
NORTH P1 2015 1 JAN 1000 2000
NORTH P1 2015年1月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 8月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 600 0
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年8月3日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 8000 9000
SOUTH P3 2015 4 OCT 1000 2000 9000
SOUTH P3 2015 4 NOV 3000 2000 9000
NORTH P1 2016 2000年1月1日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
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
推荐答案
试试这个：
Try this:
SELECT
(SELECT AVG(Sales) FROM product WHERE year = @prevYear AND @QTR=@prevQtr) AS PrevQtr,
(SELECT AVG(Sales) FROM product WHERE year = @currYear AND @QTR=@currQtr) AS CurrQtr
替换 @prevYear ， @prevQtr ， @curYYear 和 @curYYear 及相应的值。
另一种选择是使用 Pivot [ ^ ] table。
Replace @prevYear, @prevQtr, @currYear and @currYear with corresponding values.
Another option is to use Pivot[^] table.
这里有两个问题需要解决。
You have two problems that needs to be solved here.
- 你的表定义很糟糕。
你有三种不同的时态类型，不能正确排序。 - 你有差距顺序。
所以我们来修复它们。
首先我们需要修复时态类型
So lets fix them.
first we need to fix the temporal types
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
这很容易，填补空白更糟糕。
首先我们找到使用的第一个和最后一个日期，然后我们创建一个序列。
That was easy, filling the gaps is worse.
First we find the first and last dates used and then we create a sequence out of that.
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1现在我们创建一个包含所有月份的虚拟表并加入固定表
Now we create a dummytable with all months and join with the fixed table
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
,datesequence AS (
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT DISTINCT
region
,product
FROM one
)
,dummytable AS (
SELECT region,product,saledate,0 sales
FROM products p,datesequence ds
)
SELECT d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM dummytable d
left OUTER JOIN fixeddate f
ON d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate
现在我们可以使用适当的布局查询表：
Now we can query a table with a proper layout:
with fixeddate as (
SELECT region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM one
)
,daterange AS (
SELECT Min(Trunc(saledate,'Q')) mindate
,Add_Months(Max(Trunc(SaleDate,'Q')),2) maxdate
FROM FixedDate
)
,datesequence AS (
SELECT Add_Months(mindate,LEVEL - 1) saledate
FROM daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT DISTINCT
region
,product
FROM one
)
,dummytable AS (
SELECT region,product,saledate,0 sales
FROM products p,datesequence ds
)
,fixedtableone AS (
SELECT d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM dummytable d
left OUTER JOIN fixeddate f
ON d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate
)
,withavg AS (
SELECT region
,product
,saledate
,sales
,Round(Avg(sales) OVER (PARTITION BY region,product,To_Char(saledate,'YYYY-Q') )) qtr_avg
FROM fixedtableone
)
SELECT region
,product
,To_Char(saledate,'YYYY') year
,To_Char(saledate,'Q') qtr
,To_Char(saledate,'MON') MONTH
,sales
,qtr_avg
,Lag(qtr_avg,3,0) OVER (PARTITION BY region,product ORDER BY saledate) prv_qtr_avg
FROM withavg
您可以在这里测试查询[ ^ ]。
使用适当的表格你只需要查询的最后两部分，它也可以更好地扩展。
You can test the query here[^].
With a proper table layout you would have only needed the last two parts of the query and it would have scaled a lot better too.
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, year,qtr),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屋！
