我需要建立每周的销售汇总数据。其中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中不需要
  • ORDER BY,因为您正在获取总计而不是运行总计。
  • 始终在join子句中使用显式的,语法而不是where
  • 在列名之前使用适当的别名。

  • 编辑:根据OP的注释,要获取丢失的交易周数的最后一个非空值,请使用LAGIGNORE 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/

    10-11 20:41
    查看更多