sql - 使用sql的最新记录

Question

我需要为销售建立每周汇总数据。其中 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;

我想避免程序方法或按子句连接。如果有任何帮助，我将不胜感激。

Answer 1

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 中不需要 ORDER BY，因为您得到的是总数而不是运行总数。

• 始终在 where 子句中使用显式 join 语法而不是 , 。
• 在列名前使用适当的别名。

编辑:根据 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