I'm trying to find the correct sql query based on the case below.

From the table above, the AVERAGE_NAV can be found by moving average since the first date unit purchased. So, if I want to find moving average NAV for spesific data of customer_id and unit_id from table ACCOUNTBALANCE, I use this query below.

SELECT
    ACCBAL.CUSTOMER_ID,
    ACCBAL.UNITTRUST_ID,
    ACCBAL.INVACCT_ID,
    ACCBAL.NAV,
    AVG(ACCBAL.NAV)
        OVER (
            ORDER BY ACCBAL.BALANCEDATE
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
    ACCBAL.CREATED_DATE
FROM WMS_UT_ACCOUNTBAL ACCBAL
WHERE
    ACCBAL.CUSTOMER_ID=16 AND
    ACCBAL.UNITTRUST_ID=1254

you can see the picture of the query in the link below. query image

The result of the query is shown below.

My question is, What is the query to find the average_NAV with all combined different customer_id and unit_id like this table below here.

the average_NAV must be based on the query result from the same customer_id and unitrust_id start from the first purchase. you can see that for customer_id=17 and unit_id=1255 has average_NAV=30 same as NAV since it's first purchase with those spesific customer_id and unit_id was 2020-05-03. The main obstacle is how I can use where clause inside the outer SELECT because it's prohibited in oracle sql.

I have used CTE method (WITH) but still not what expected like below.

WITH ACCBAL (CUSTOMER_ID,UNITTRUST_ID,NAV, CREATED_DATE) AS
    (SELECT ACCBAL1.CUSTOMER_ID, ACCBAL1.UNITTRUST_ID,
            ACCBAL1.NAV, ACCBAL1.CREATED_DATE
     FROM WMS_UT_ACCOUNTBAL ACCBAL1),
    ACCBAL_AVERAGE_NAV (AVERAGE_NAV) AS
        (SELECT AVG(ACCBAL2.NAV) OVER
        (ORDER BY ACCBAL2.CREATED_DATE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            FROM WMS_UT_ACCOUNTBAL ACCBAL2
        WHERE ACCBAL2.UNITTRUST_ID=ACCBAL.UNITTRUST_ID
            AND ACCBAL2.CUSTOMER_ID=ACCBAL.CUSTOMER_ID)
SELECT * FROM ACCBAL,ACCBAL_AVERAGE_NAV;

The query above is not working because it gives error of invalid identidier in WHERE clause.

Anyone can help me? Truly Appreciated. Thank you

CodePudding user response：

1. You can include the PARTITION BY clause before ORDER BY.
2. Tested on db<>fiddle

SELECT
    ACCBAL.CUSTOMER_ID,
    ACCBAL.UNITTRUST_ID,
    -- ACCBAL.INVACCT_ID,
    ACCBAL.NAV,
    ACCBAL.CREATED_DATE,
    AVG(ACCBAL.NAV)
        OVER (
            PARTITION BY ACCBAL.CUSTOMER_ID, ACCBAL.UNITTRUST_ID 
            ORDER BY ACCBAL.CREATED_DATE
        )
FROM WMS_UT_ACCOUNTBAL ACCBAL;