利用Oracle Analytics改善复杂的SQL查询

我正在处理这个特别复杂的问题（至少对我来说，如果您是数学极客，请不要对我进行评判！）。

从本质上讲，我有两件事可以记录当前的库存水平，以及库存进出商店的所有库存清单。我正在尝试将这两个部分结合起来，使我能够在去年的任何时候了解特定对象的库存水平。

SQL的第一部分结合了特定商店中所有对象去年的所有库存移动和当前库存水平：

SELECT OBJINCDE, 
       STOREINCDE, 
       TRUNC(STKMVTDTE) AS MOVEMENT_DATE, 
       --This CASE statement tells me if the stock was moved in or out
       CASE WHEN STKMVTINCDE IN (1, 2, 3, 5, 6, 8, 9, 11)  THEN 1 ELSE -1 END AS MOVEMENT

    FROM H_STK

    WHERE TRUNC(STKMVTDTE, 'MM') >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12)  --in the last year
    AND STOREINCDE = 615  --A particular store

UNION ALL

--This statement looks at current levels and combines it with movements as a movement in on the date that the statement was run

SELECT OBJINCDE, 
       STOREINCDE, 
       TRUNC(SYSDATE) AS MOVEMENT_DATE, 
       STKQTY AS MOVEMENT                             

    FROM P_OBJSTORE                                

    WHERE (STKBRKQTY > 0 OR STKMAXQTY > 0)  --This just only picks objects that have a maximum or minimum listed don't judge the stock system either, I can't change that
    AND STOREINCDE = 615

因此，这实际上返回了所有库存移动及其日期的列表，然后我在此语句中使用该列表：

SELECT TO_CHAR(y.EACH_DAY, 'DD/MM/YYYY') AS EACH_DAY,
       x.OBJINCDE AS OBJINCDE,
       NVL(x.MOVEMENT, 0) AS MOVEMENT,
       SUM(NVL(x.MOVEMENT, 0)) OVER ( ORDER BY y.EACH_DAY DESC) AS STOCK_LEVEL  --Oracle analytics to put together a running total

FROM (SELECT OBJINCDE, MOVEMENT_DATE, SUM(MOVEMENT) AS MOVEMENT             
          FROM W_MIN_MAX_MOVEMENTS  
          WHERE OBJINCDE = 14419  --This is my selection of a particular object
          GROUP BY OBJINCDE, MOVEMENT_DATE
          HAVING SUM(MOVEMENT) <> 0
          ORDER BY MOVEMENT_DATE) x,
     (SELECT TRUNC(SYSDATE) - 365 + LEVEL AS EACH_DAY  --Just brings in each day for the last 365 days
          FROM DUAL
          WHERE ROWNUM <= 365
          CONNECT BY LEVEL = ROWNUM) y

WHERE x.MOVEMENT_DATE (+) = y.EACH_DAY

ORDER BY y.EACH_DAY DESC

因此，在那之后，我遇到了一些似乎无法解决的问题。

第一个-在第二个语句中，它返回365天的列表，当天所选对象的移动以及历史库存水平，我似乎无法使对象ID出现在每一行中。

第二-我很希望能够运行此程序，以便每个对象在对应的库存水平下都有365天的移动量。我认为这将需要比我目前所拥有的对Oracle Analytics（分析）有更好的理解。

任何帮助将不胜感激。