Использование значений текущей строки (по алиасу) внутри аналитической функции Oracle SQL

#sql #база_данных #oracle

Есть некая табличка:

CREATE TABLE TEST_TABLE (
  COL1 INTEGER,
  COL2 INTEGER
);


И есть некий неработающий запрос с аналитической функцией:

SELECT
    TEST_TABLE.Col1,
    TEST_TABLE.Col2 AS SUPER_Col2,
    SUM(Col1 - SUPER_Col2) OVER ()
FROM TEST_TABLE;


Проблема заключается в том, что значение SUPER_Col2 из текущей строки не доступно
внутри SUM().

Безусловно существует куча вариантов как сделать это задание, но просто хотелось
бы узнать, можно ли без танцев с бубнами использовать значение текущей строки внутри
аналитической функции, как в указанном запросе?

Пример таблицы:

Col1    Col2
4       1
5       2
6       3


Результат запроса:

Col1    SUPER_Col2    Sum
1       1             ((4 - 1) + (5 - 1) + (6 - 1))= 12
2       2             ((4 - 2) + (5 - 2) + (6 - 2))= 9
3       3             ((4 - 3) + (5 - 3) + (6 - 3))= 6


То есть SUM для каждого значения Col1 вычитает текущее значение Col2
    

Ответы

Ответ 1

Проблема заключается в том, что значение SUPER_Col2 из текущей строки не доступно
внутри  SUM()


SUPER_Col2 это алиас и он не доступен в листе текущего выражения SELECT. Так нельзя
делать: 

select 1 a, 2 b, sum (a+b) over () sum from dual;



  ORA-00904: "B": invalid identifier


Используйте имя колонки. И судя по желаемому результату, её не нужно использовать
в аналитической функции:

create table t1 as select rownum+3 col1, rownum col2 from xmlTable ('1 to 3');

select 
    col1, col2, sum(col1) over () - (col2 * count(1) over ()) sum
from t1;

      COL1       COL2        SUM
---------- ---------- ----------
         4          1         12
         5          2          9
         6          3          6




Вне рамок ответа, так как из комментария.


  ... нельзя будет применить для операций сравнения. Вроде такого:

SELECT 
    TEST_TABLE.Col1, TEST_TABLE.Col2 AS SUPER_Col2, 
    SUM((CAST((Col1 > SUPER_Col2) AS INT)) OVER () 
FROM TEST_TABLE;



Логические выражения с использованием операторов сравнения могут быть использованы
напрямую в выражениях, таких как:  WHERE, HAVING, ON, а так же в операторе CASE. В
SQL нет типа BOOLEAN и результат логических выражений не может быть преобразован оператором
CAST в какой-то другой тип данных.
Посчитать, сколько строк, где значение первой колонки больше второй, можно так:

select 
    col1, col2, sum (case when col1 > col2 then 1 else 0 end) over () res
FROM t1;

      COL1       COL2        RES
---------- ---------- ----------
         4          1          3
         5          2          3
         6          3          3