并行日期销售 SQL 视图 [英] Parallel Date Sales SQL View
问题描述
我有一个我自己似乎无法解决的挑战，现在需要帮助！
I have a challenge which I can't seem to resolve on my own and now need help!
我需要通过 SQL 显示平行年份日期的销售额，我的意思是如果今天 (20/08/2015) 客户 A 购买了价值 500 的产品，我想知道客户 A 在同一天花了多少钱去年(所以 20/08/2014).
I have a requirement to show parallel year date sales via SQL and by that I mean if today (20/08/2015) Customer A has purchased products worth 500, I want to know how much Customer A spent on the same day last year (so 20/08/2014).
这是一个 SQL fiddle，我已经在其中构建了所有内容(我认为那将是对你们来说最简单).我有 3 个维度(DimProduct、DimDate 和 DimCustomer)、一个事实表 (FactSales) 和一个我在上面构建的视图 (VW_ParallelSales).我还在右手边留下了一个关于我想要实现的问题的查询.如果您运行查询，您将看到对于 Antonio，20140820 上的 SaleAmount 为 3500，如果您查看表的最底部，您可以看到在事实表 20150820 中还有一条 Antonio 的记录为 6500.所以基本上，我想要的是在 ParallelSales 列(目前显示为 NULL)下在 20140820(这是 20150820 的平行年份日期)出售的 3500.
Here's a SQL fiddle where I've built everything (I reckoned that would be easiest for you guys). I have 3 dimensions (DimProduct, DimDate and DimCustomer), a fact table (FactSales) and a view (VW_ParallelSales) which I've built on top. I have also left a query on the right hand side with what I'm trying to achieve. If you run the query you will see that for Antonio, the SaleAmount on 20140820 was 3500 and if you look at the very bottom of the table, you can see there's one more record for Antonio in the fact table on 20150820 for 6500. So esentially, what I want is to have that 3500 which was sold on 20140820 (which is the parallel year date of 20150820) under the column ParallelSales (which at the moment is showing as NULL).
如果我不在视图中包含 ProductKey 而只有 CustomerKey (看这个小提琴).但是，一旦我添加了产品密钥，因为过去没有发生过 CustomerKey-ProductKey 的完全匹配，所以我得到了 ParallelSales 的 NULLS(或者至少我认为原因是这样).
It all works like a charm if I don't include the ProductKey in the view and have just the CustomerKey (see this fiddle). However, as soon as I add the Product Key, because there is no exact match of CustomerKey-ProductKey that has happened in the past, I'm getting NULLS for ParallelSales (or at least that's what I think the reason is).
然后我想要做的是使用视图并加入 DimCustomer 和 DimProduct 并以两种方式运行查询，即:
What I want to be able to do is then use the view and join on both DimCustomer and DimProduct and run queries both ways, i.e.:
问题 1:客户 A 今天与去年相比今天花费了多少?问题 2:与去年相比，我们今天销售了多少产品 A?
Query 1: How much did Customer A spend today vs today last year? Query 2: How much of Product A did we sell today vs today last year?
目前，我需要有 2 个视图 - 一个连接 CustomerKey 视图中的两个子查询，另一个 - ProductKey(显然是日期).
At the moment, as is, I need to have 2 views for that - one that joins the two sub-queries in the view on CustomerKey and the other one - on ProductKey (and obviously the dates).
我知道有很多问题要问，但我确实需要让它发挥作用，非常感谢您的帮助！谢谢:)
I know it's a lot to ask but I do need to get this to work and would appreciate your help immensely! Thanks :)
推荐答案
针对不同年份的客户销售.
For customer sales in diferent years.
SELECT DimCustomer.CustomerName,
VW_Current.Saledate,
VW_Current.ParallelDate,
VW_Current.CurrentSales,
VW_Previous.CurrentSales as ParallelSale
FROM DimCustomer
INNER JOIN VW_ParallelSales VW_Current
ON DimCustomer.CustomerKey = VW_Current.CustomerKey
LEFT JOIN VW_ParallelSales VW_Previous
ON VW_Current.ParallelDate = VW_Previous.Saledate
AND DimCustomer.CustomerKey = VW_Previous.CustomerKey
ORDER BY 1, 2
对于产品密钥
With sales as (
SELECT
DimProduct.ProductKey,
DimProduct.ProductName,
VW_ParallelSales.Saledate,
VW_ParallelSales.ParallelDate,
VW_ParallelSales.CurrentSales,
VW_ParallelSales.ParallelSales
FROM DimProduct INNER JOIN VW_ParallelSales ON DimProduct.ProductKey =
VW_ParallelSales.ProductKey
)
SELECT
s_recent.ProductName,
s_recent.Saledate ThisYear,
s_old.Saledate PreviousYear,
s_recent.CurrentSales CurrentSales,
s_old.CurrentSales ParallelSales
FROM
SALES s_recent
left outer join SALES s_old
on s_recent.saledate = s_old.saledate + 10000
and s_recent.ProductKey = s_old.ProductKey
这篇关于并行日期销售 SQL 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
