SQL加入以比较今年与去年的销售商店和产品的销售

Question

我正在尝试比较今年与商店和产品的最后销售。

我的SQL背后的想法是创建24个月滚动数据的基础表，并在交易日期-1年进行连接。我的数据按日期，商店和产品汇总。

，这有点复杂。

我的代码如下。我的问题是，当我进行左加入时，今年和去年的数字不符合。例如，去年2月19日的销售额应等于今年的2月18日，但我没有得到这个结果。

我的猜测是，去年有某些商店和产品今年无法使用，但我不知道如何解决。我尝试了一个完整的加入，但数字也关闭了。感谢任何反馈！

-- extract sales by day, store, product
select business_date, store_code, product_code, 
sum(sales) as sales
into #temp1
from sales_table
where business_date >= date_trunc('month', dateadd('month', -24, sysdate))
group by business_date, store_code, product_code;

-- compare this year against last year
select ty.*, ly.sales as sales_ly
into #temp2
from #temp1 ty left join #temp1 ly
on ty.product_code = ly.product_code
and ty.store_code = ly.store_code 
and trunc(dateadd(year, -1, ty.business_date)) = ly.business_date;
-- check
select to_char(business_date, 'yyyymm'), sum(sales) ty, sum(sale_ly) as ly
from #temp2
group by to_char(business_date,'yyyymm')
order by 1;

Answer 1

您有加入- trunc(dateadd(year, -1, ty.business_date)) = ly.business_date;

您正在尝试将今年特定日期的销售与去年(22/02/2019 with 22/02/2018)

进行比较

在您的表中，您是否有这两天的数据，表中的汇总示例数据可能有助于编写查询。

查询 -

with  sales_tab as (
select '2019/02/22' as date1, 123 as store, 456 as prod, 20 sales from dual
union
select '2019/02/23' as date1, 123 as store, 456 as prod, 30 as sales from dual
union
select '2019/02/24' as date1, 123 as store, 456 as prod, 40 sales from dual
union
select '2018/02/22' as date1, 123 as store, 456 as prod, 60 sales from dual
union
select '2018/02/23' as date1, 123 as store, 456 as prod, 70 as sales from dual
union
select '2018/02/25' as date1, 123 as store, 456 as prod, 80 sales from dual)
select 
t1.date1, t2.date1, t1.store, t1.prod, t1.sales this_year, t2.sales prev_year
from sales_tab t1 left outer join sales_tab t2 on 
(t1.store=t2.store 
and t1.prod=t2.prod
and cast(substr(t1.date1,1,4) as int)-1=cast(substr(t2.date1, 1,4) as int)
and substr(t1.date1,6,2)=substr(t2.date1,6,2)
and substr(t1.date1,9,2)=substr(t2.date1,9,2)
);