CREATE TABLE TempForTest (
id INT,
Cus INT,
Nm VARCHAR(10),
Sm NUMBER,
Tp Number
);
INSERT INTO TempForTest VALUES (1, 1, 'Ivan', 100, 0);
INSERT INTO TempForTest VALUES (2, 1, 'Ivan', 150, 0);
INSERT INTO TempForTest VALUES (3, 1, 'Ivan', 400, 1);
INSERT INTO TempForTest VALUES (4, 2, 'Petr', 100, 0);
INSERT INTO TempForTest VALUES (5, 2, 'Petr', 350, 0);
INSERT INTO TempForTest VALUES (6, 2, 'Petr', 5, 0);
INSERT INTO TempForTest VALUES (7, 2, 'Petr', 15, 1);
INSERT INTO TempForTest VALUES (8, 2, 'Petr', 35, 1);
В результате хочу получить вот что:
Cus Nm Sm0 Sm1 Raz
1 Ivan 250 400 -150
2 Petr 455 50 405
Есть 2 варианта запроса:
Первый вариант.
SELECT Cus, Nm, SUM(TP0) AS Sm0, SUM(TP1) AS Sm1, SUM(TP0-TP1) AS Raz FROM (
SELECT Cus, Nm, TP0, TP1 FROM (
SELECT Cus, Nm,
SUM(DECODE(Tp, 0, Sm, 0)) OVER (PARTITION BY Cus, Tp) AS TP0
,SUM(DECODE(Tp, 1, Sm, 0)) OVER (PARTITION BY Cus, Tp) AS TP1
FROM TempForTest
GROUP BY Cus, Nm, Tp, Sm )
GROUP BY Cus, Nm, TP0, TP1)
GROUP BY Cus, Nm
Второй вариант:
WITH
TP0 AS (
SELECT Cus, Nm, SUM(Sm) AS SM
FROM TempForTest
WHERE Tp = 0
GROUP BY Cus, Nm),
TP1 AS (
SELECT Cus, Nm, SUM(Sm) AS SM
FROM TempForTest
WHERE Tp = 1
GROUP BY Cus, Nm)
SELECT TP0.Cus, TP0.Nm, TP0.SM AS SM0, TP1.SM AS SM1, TP0.SM - TP1.SM AS RAZ
FROM TP0
JOIN TP1 ON TP1.Cus = TP0.Cus
В действительности таблица TempForTest собирается из 3-4 других через. JOINы и имеет более 5К записей.
На действующей базе работает 2 вариант. За исключением что вместо временных таблиц заданы курсоры. И сопоставление курсоров по Cus с TempForTest идет в Pl/SQL блоке.
При этом что таблицы с вычислениями, что основная таблица имеют пратически идентичные отборы по ряду других параметров, в том числе и через JOIN.
Первый вариант избавит от PL/SQL блока. Избавит от курсоров. Избавит от выдергиваний одних и тех же "пачек" таблиц с одинаковыми условиями запросов.
Но не уверен что держать в памяти результат запроса и проводить с ним вычисления заворачивая в селекты хорошая идея.
Так какой вариант лучше?
Забыл добавить, функция PIVOT не поддерживается
Средний
