我有一个表，其中有各种属性，如地区产品，年，季度，月，销售。我必须计算具有相同区域的每个产品的avg_qtr销售，并显示它们以前的avg_qtr销售。我读过关于滞后，但在这里是不可能使用的，因为它是不固定的多少行之后，它将被重复。我的表结构是这样的
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
我编写了计算当前季度的查询，并显示当前季度的前一个平均值
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) ;
我能够得到该产品的当前平均值和以前的平均值，但反之则不行。我不知道该如何显示那个季度的平均值，因为这个季度没有任何销售。我想要这样的输出-
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
按最新要求编辑。
你的问题是你试图通过试图操纵QTR和YEAR来获得previous_avg。我使用RANK函数，按照我想要的数据排序。在连接中，我确保平均区域=前一个区域，并且忽略年份，因为上一季度可以是上一年的Q4，而不是平均年度Q1;这样更干净。
--Build the test table
IF OBJECT_ID('SALES','U') IS NOT NULL
DROP TABLE SALES
CREATE TABLE SALES
(
Region VARCHAR(255)
, Product VARCHAR(10)
, [Year] INT
, QTR INT
, [Month] VARCHAR(19)
, Sales DECIMAL(19,4)
);
INSERT SALES
VALUES
('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);
--CTE TO CAPTURE AVG SALES BY REGION, PRODCUT, YEAR, QTR; OMIT PRODUCT IF YOU WANT STRAIGHT UP QUARTER AVG, REGARDLESS OF PRODCUCT
WITH cteAvgSales AS
(
SELECT Region, Product, [Year], QTR, AVG(Sales) current_avg
, RANK() OVER(ORDER BY Region, Product, [Year], QTR) AS RNK
FROM SALES
GROUP BY Region, Product, [Year], QTR
)
SELECT s.Region, s.Product, s.[Year] AS [year], s.QTR AS [quarter], s.[Month], s.Sales, a.current_avg, p.current_avg AS previous_avg
FROM SALES s
INNER JOIN cteAvgSales a ON a.Region = s.Region
AND a.Product = s.Product
AND a.[Year] = s.[Year]
AND a.QTR = s.QTR
LEFT JOIN cteAvgSales p ON p.Region = a.Region
AND p.Product = s.Product
AND p.RNK=a.RNK-1
ORDER BY s.Region, s.Product, s.[Year], s.QTR
如果您有一个排序值，则可以使用分析函数的窗口子句，因此首先创建年和季度的DENSE_RANK，然后在分析函数中使用该排名:
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
上述解决方案假设样本数据集中提供了密集的季度数据，但是，如果数据沿着四分之一维度是稀疏的，您可以首先像下面的查询那样对数据进行密集化:
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将枚举每个季度，并且数据中的空白将导致计算的季度平均值的空白。
您还可以通过利用YEAR和QTR的数字特性来改变QORD的计算方式，从而达到类似的效果，如下例所示:
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中留下空白，但它确实遗漏了致密化数据包含的缺失季度的记录。
结合最后两个示例，并添加对区域的新要求，如果每个不同的区域、产品和年份需要，则每个季度将返回或生成至少一行数据。这两个平均值都按地区和产品划分，并根据具体情况按当前或上一季度计算:
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;
您需要连接一些子选择语句，以获得上一季度的平均值。您还需要对两个语句进行联合，因为对于季度2,3,4，您可以简单地在连接语句中减去上一季度的平均值，但是当它是第一季度时，您需要减去一年并设置上一季度= 4。这个语句应该适用于你所描述的情况。
--handles when the current quarter being viewed is 2,3,or 4 because those would still be in the same year when looking at the previous quarter
select t1.product,
t1.year,
t1.month,
t1.sales ,
t1.qtr,
round(avg(t1.sales) over (partition by t1.qtr,t1.year),2) as av,
t2.prev_av
from one t1
left join ( select
product,
year,
month,
sales ,
qtr,
round(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t2
on t1.year = t2.year
and (t1.qtr - 1) = t2.qtr
where t1.qtr in (2,3,4)
union
--handles the 1st quarter of the year when you need to grab the 4th quarter of the previous year for the previous avg
select t3.product,
t3.year,
t3.month,
t3.sales ,
t3.qtr,
round(avg(t3.sales) over (partition by t3.qtr,t3.year),2) as av,
t4.prev_av
from one t3
left join ( select
product,
year,
month,
sales,
qtr,
round(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t4
on (t3.year - 1) = t4.year
and t4.qtr = 4
where t3.qtr = 1;