How to compare two SELECT subqueries by using the BETWEEN operator?

Question

I'm working with Oracle Database 12c Enterprise Edition.

The table looks like this: https://i.sstatic.net/gL2L6.jpg

The table shows the stock price of different stocks with their distinct ID (WKN), their starting price (Start), their maximum price (Hoch), their low price (Tief) and their closing price (Schluss) per day by date (Datum). I now want to compute a 90-day line directly in SQL. It's calculated by determining the arithmetic mean of the closing price over the past 90 days for every day within the 90 days.

I tried to calculate this with the following SQL statement:

SELECT SUM(SCHLUSSPREIS) / 90 AS TAGESLINIE FROM KURS WHERE DATUM BETWEEN
  (SELECT DATUM FROM KURS WHERE WKN = 2 AND DATUM BETWEEN
    (SELECT MAX(DATUM) - 179 FROM KURS) AND
    (SELECT MAX(DATUM) - 90 FROM KURS) ORDER BY DATUM ASC)
  AND (SELECT DATUM FROM KURS WHERE WKN = 2 AND DATUM BETWEEN
   (SELECT MAX(DATUM) - 89 FROM KURS) AND
   (SELECT MAX(DATUM) FROM KURS) ORDER BY DATUM ASC)
;

To give you a much better overview of this confusing statement, I created the following scheme: https://i.sstatic.net/XjYXf.jpg

Both, the first subquery...

(SELECT DATUM FROM KURS WHERE WKN = 2 AND DATUM BETWEEN
  (SELECT MAX(DATUM) - 179 FROM KURS) AND
  (SELECT MAX(DATUM) - 90 FROM KURS) ORDER BY DATUM ASC)

... and the second subquery...

(SELECT DATUM FROM KURS WHERE WKN = 2 AND DATUM BETWEEN
  (SELECT MAX(DATUM) - 89 FROM KURS) AND
  (SELECT MAX(DATUM) FROM KURS) ORDER BY DATUM ASC)

... do return a list of the same amount of dates (90).

The first date in the list of the first subquery belongs to the first date in the list of the second subquery, the second date in the list of the first subquery belongs to the second date in the list of the second subquery, etc. (comp. scheme obove). I now want my SQL statement to sum up all closing prices between each of these periods (from first date (list 1) to first date (list 2), etc.) and divide it by 90 to get the arithmetic mean, until both lists are empty.

However, if I run this, I get the following error message:

ERROR at line 1:
ORA-00907: missing right parenthesis

or

ERROR at line 1:
ORA-01427: single row subquery returns more than one row

Any idea how to do something like this directly in SQL?

Any help is appreciated- Thank you! :-)

Answer 1

SUM is not the only aggregate function available in SQL. Here you might want to use AVG instead:

SELECT *
FROM (
  SELECT
     DATUM,
     AVG(SCHLUSSPREIS) OVER (ORDER BY DATUM rows BETWEEN 89 PRECEDING AND CURRENT ROW) AS TAGESLINIE
  FROM KURS
  WHERE WKN = 2
  ORDER BY DATUM DESC
) Moving_Average
WHERE ROWNUM <= 90;