我需要建立每周的销售汇总数据。其中TRNST_QTY
是截止周的最新可用数量,而PRCHS_QTY
是特定周的购买数量总和。我有日期维度,其中存在日期键和相应的星期键。
如果有任何一周没有交易发生,则应采用上周的TRNST_QTY
,而PRCHS_QTY
将为0
表1(销售表):
DT_KEY ITEM TRNST_QTY PRCHS_QTY
20160515 4098 2 1
20160516 4098 10 1
20160601 4098 4 0
20160603 4098 8 0
20160611 4098 6 4
20160616 4098 4 0
20160622 4098 8 0
20160623 4098 2 0
20160714 4098 11 3
要求的输出(每周总计):
WK_DT_KEY ITEM TRNST_QTY PRCHS_QTY
20160521 4098 10 2
20160607 4098 8 0
20160614 4098 6 4
20160621 4098 4 0
20160630 4098 2 0
20160707 4098 2 0
20160714 4098 11 3
日期和星期映射:
DT_KEY WK_DT_KEY
20160515 20160521
20160516 20160521
20160517 20160521
20160518 20160521
20160519 20160521
20160520 20160521
20160521 20160521
20160601 20160607
20160602 20160607
20160603 20160607
20160604 20160607
等等
到目前为止,我可以在下面查询,但仅给出最近一周的信息
select * from
(
SELECT WK_DT_KEY
,ITEM
,sum(PRCHS_QTY) OVER (
PARTITION BY WK_DT_KEY
,ITEM
ORDER BY WK_DT_KEY
,ITEM
) AS PRCHS_QTY
,ROW_NUMBER() OVER (
PARTITION BY ITEM order by a.dt_key desc
) AS RNK
FROM fct_itm a
,DIM_DT DIM_DT
WHERE a.dt_key <= '20170207'
AND DIM_DT.DT_key = a.dt_key
)
where RNK = 1;
我想避免程序性方法或connect by子句。
我将不胜感激。
最佳答案
SELECT DIM_DT.WK_DT_KEY,
A.ITEM,
SUM(A.PRCHS_QTY) OVER (PARTITION BY DIM_DT.WK_DT_KEY,A.ITEM) AS PRCHS_QTY,
FIRST_VALUE(A.TRNST_QTY) OVER (PARTITION BY A.ITEM,DIM_DT.WK_DT_KEY
ORDER BY A.DT_KEY DESC) AS TRNS_QTY
FROM FCT_ITM A
JOIN DIM_DT DIM_DT ON DIM_DT.DT_KEY = A.DT_KEY
WHERE A.DT_KEY <= '20170207'
笔记:
FIRST_VALUE
函数获取每周最新的trnst_qty。 SUM
中不需要join
子句中使用显式的,
语法而不是where
。 编辑:根据OP的注释,要获取丢失的交易周数的最后一个非空值,请使用
LAG
和IGNORE NULLS
选项。SELECT
WK_DT_KEY,
ITEM,
CASE WHEN TRNS_QTY IS NULL THEN LAG(TRNS_QTY IGNORE NULLS) OVER(PARTITITON BY ITEM ORDER BY WK_DT_KEY)
ELSE TRNS_QTY END AS TRNS_QTY,
PRCHS_QTY
FROM
(SELECT DISTINCT D.WK_DT_KEY,
D.ITEM,
SUM(COALESCE(A.PRCHS_QTY,0)) OVER (PARTITION BY D.WK_DT_KEY,D.ITEM) AS PRCHS_QTY,
FIRST_VALUE(A.TRNST_QTY) OVER (PARTITION BY D.ITEM,D.WK_DT_KEY
ORDER BY A.DT_KEY DESC) AS TRNS_QTY
FROM
(SELECT DT.WK_DT_KEY,DT.DT_KEY,F.ITEM
FROM DIM_DT DT
CROSS JOIN (SELECT DISTINCT ITEM FROM FCT_ITM) F) D --cross join items with all dates and week combinations
LEFT JOIN FCT_ITM A ON D.DT_KEY = A.DT_KEY AND D.ITEM=A.ITEM
AND A.DT_KEY <= '20170207'
) X
关于sql - 使用sql的最新记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42144486/