Ручной поворот без использования поворота в Oracle SQL
У меня есть требование извлечь данные из таблицы, которая выглядит примерно так:
SQL> SELECT * FROM Weekly_test;
Данные, которые будут представлены, должны быть сведены вручную, и если вы заметите, что в данных отсутствует обзорная неделя 3.
Я использовал приведенный ниже sql, чтобы повернуть его:
SELECT CASE
WHEN rev_week = 1 THEN
question
END q1,
CASE
WHEN rev_week = 1 THEN
answer
END a1,
CASE
WHEN rev_week = 2 THEN
question
END q2,
CASE
WHEN rev_week = 2 THEN
answer
END a2,
CASE
WHEN rev_week = 3 THEN
question
END q3,
CASE
WHEN rev_week = 3 THEN
answer
END a3,
CASE
WHEN rev_week = 4 THEN
question
END q4,
CASE
WHEN rev_week = 4 THEN
answer
END a4
FROM weekly_test;
Результат аналогичен приведенному выше, но я хочу отобразить для третьей недели проверки значение «Не завершено», что в настоящее время невозможно, поскольку данные не существуют.
Итак, вопрос в том, как я могу отображать все недели с 1 по 4 с вопросами и ответами, даже если данные за эту неделю не существуют?
Ответы 3
Вы можете использовать PARTITION RIGHT OUTER JOIN:
with data(rev_week, question, answer) as (
select 1, 1, 'pass' union all
select 1, 2, 'pass' union all
select 1, 3, 'not done' union all
select 2, 1, 'pass' union all
select 2, 2, 'fail' union all
select 2, 3, 'fail' union all
select 4, 1, 'not done' union all
select 4, 2, 'fail' union all
select 4, 3, 'pass' -- union all
),
weeks(rev_week) as (
select level from dual
connect by level <= 4
)
select * from (
select v.rev_week, d.question, nvl(d.answer, 'not completed') as answer
from data d
partition by (d.question)
right outer join weeks v on v.rev_week = d.rev_week
)
order by rev_week, question
;
1 1 pass
1 2 pass
1 3 not done
2 1 pass
2 2 fail
2 3 fail
3 1 not completed
3 2 not completed
3 3 not completed
4 1 not done
4 2 fail
4 3 pass
А затем используйте любую технику PIVOT, которую вы предпочитаете.
Во-первых, чтобы свернуть данные для правильной сводки, вам понадобится GROUP BY на элементе без поворота, который будет определять ваши строки (я предлагаю question, поскольку вы, похоже, хотите иметь отдельную строку для каждого вопроса), и вы необходимо добавить агрегирование (MAX()) вокруг поворотных условно представленных столбцов (здесь, answer).
Для условности вы, конечно, можете использовать CASE, как и делаете, но в таких ситуациях из-за копирования и повторения вы можете найти DECODE более кратким и занимающим гораздо меньше места.
Наконец, вы можете обработать пропущенные значения с помощью функции NVL, которая заменяет NULL чем-то другим. Сложите все это вместе, и это может быть довольно коротко и мило:
SELECT question,
NVL(MAX(DECODE(rev_week,1,answer)),'Not Completed') a1,
NVL(MAX(DECODE(rev_week,2,answer)),'Not Completed') a2,
NVL(MAX(DECODE(rev_week,3,answer)),'Not Completed') a3,
NVL(MAX(DECODE(rev_week,4,answer)),'Not Completed') a4
FROM weekly_test
GROUP BY question
ORDER BY question
Один из способов получить все необходимые строки - использовать рекурсивный cte для заполнения пробелов в столбце REV_WEEK и перекрестное соединение каждую неделю со всеми отдельными вопросами, заполняющими пустые ответы с надписью «Не завершено»...
WITH -- S a m p l e D a t a :
tbl (REV_WEEK, QUESTION, ANSWER) AS
( Select 1, 1, 'pass' From Dual Union All
Select 1, 2, 'pass' From Dual Union All
Select 1, 3, 'not done' From Dual Union All
Select 2, 1, 'pass' From Dual Union All
Select 2, 2, 'fail' From Dual Union All
Select 2, 3, 'fail' From Dual Union All
Select 4, 1, 'not done' From Dual Union All
Select 4, 2, 'fail' From Dual Union All
Select 4, 3, 'pass' From Dual
),
-- Generate number of weeks
-- One option is using recursive query creating 4 rows of data
wks (REV_WEEK) AS
( Select 1 From Dual Union All
Select REV_WEEK + 1 From wks
Where REV_WEEK <= 3
)
-- S Q L :
-- Cross Join distinct questions and populate empty answers
Select w.REV_WEEK, q.QUESTION, Nvl(t.ANSWER, 'Not Completed') "ANSWER"
From wks w
Cross Join (Select Distinct QUESTION From tbl) q
Left Join tbl t ON( Nvl(t.REV_WEEK, w.REV_WEEK) = w.REV_WEEK And
t.QUESTION = Nvl(q.QUESTION, t.QUESTION)
)
Group By w.REV_WEEK, q.QUESTION, t.ANSWER
Order By w.REV_WEEK, q.QUESTION
/* R e s u l t :
REV_WEEK QUESTION ANSWER
---------- ---------- -------------
1 1 pass
1 2 pass
1 3 not done
2 1 pass
2 2 fail
2 3 fail
3 1 Not Completed
3 2 Not Completed
3 3 Not Completed
4 1 not done
4 2 fail
4 3 pass */
Теперь вы можете делать любые повороты, которые вам нравятся.
Один (менее используемый, но довольно быстрый) способ сделать это без PIVOT — использовать предложение Oracle MODEL:
-- S Q L :
-- Declare above SQL as another cte - all_data - and use it for modeling
-- S Q L :
Select Max(Q1) "Q1", Max(A1) "A1", Max(Q2) "Q2", Max(A2) "A2", Max(Q3) "Q3", Max(A3) "A3", Max(Q4) "Q4", Max(A4) "A4"
From ( Select QUESTION, Q1, A1, Q2, A2, Q3, A3, Q4, A4
From ( Select REV_WEEK, QUESTION, ANSWER
From all_data
)
MODEL Partition By ( REV_WEEK )
Dimension By ( QUESTION )
Measures ( ANSWER,
0 "Q1", 'Dummy Answer To Any Question' "A1",
0 "Q2", 'Dummy Answer To Any Question' "A2",
0 "Q3", 'Dummy Answer To Any Question' "A3",
0 "Q4", 'Dummy Answer To Any Question' "A4"
)
RULES ( Q1[ANY] = Case When CV(REV_WEEK) = 1 Then CV(QUESTION) End,
A1[ANY] = Case When CV(REV_WEEK) = 1 Then ANSWER[CV()] End,
Q2[ANY] = Case When CV(REV_WEEK) = 2 Then CV(QUESTION) End,
A2[ANY] = Case When CV(REV_WEEK) = 2 Then ANSWER[CV()] End,
Q3[ANY] = Case When CV(REV_WEEK) = 3 Then CV(QUESTION) End,
A3[ANY] = Case When CV(REV_WEEK) = 3 Then ANSWER[CV()] End,
Q4[ANY] = Case When CV(REV_WEEK) = 4 Then CV(QUESTION) End,
A4[ANY] = Case When CV(REV_WEEK) = 4 Then ANSWER[CV()] End
)
)
Group By QUESTION
/* R e s u l t :
Q1 A1 Q2 A2 Q3 A3 Q4 A4
---------- ---------------------------- ---------- ---------------------------- ---------- ---------------------------- ---------- ----------------------------
1 pass 1 pass 1 Not Completed 1 not done
2 pass 2 fail 2 Not Completed 2 fail
3 not done 3 fail 3 Not Completed 3 pass */
ИЛИ
могу ли я предложить избегать повторения вопросов (1, 2, 3)...
-- S Q L :
Select QUESTION, Max(A1) "A1",Max(A2) "A2", Max(A3) "A3", Max(A4) "A4"
From ( Select QUESTION, A1, A2, A3, A4
From ( Select REV_WEEK, QUESTION, ANSWER
From all_data
)
MODEL Partition By ( REV_WEEK )
Dimension By ( QUESTION )
Measures ( ANSWER,
0 "Q1", 'Dummy Answer To Any Question' "A1",
0 "Q2", 'Dummy Answer To Any Question' "A2",
0 "Q3", 'Dummy Answer To Any Question' "A3",
0 "Q4", 'Dummy Answer To Any Question' "A4"
)
RULES ( A1[ANY] = Case When CV(REV_WEEK) = 1 Then ANSWER[CV()] End,
A2[ANY] = Case When CV(REV_WEEK) = 2 Then ANSWER[CV()] End,
A3[ANY] = Case When CV(REV_WEEK) = 3 Then ANSWER[CV()] End,
A4[ANY] = Case When CV(REV_WEEK) = 4 Then ANSWER[CV()] End
)
)
Group By QUESTION
/* R e s u l t :
QUESTION A1 A2 A3 A4
---------- ---------------------------- ---------------------------- ---------------------------- ----------------------------
1 pass pass Not Completed not done
2 pass fail Not Completed fail
3 not done fail Not Completed pass */