#sql #pivot #libreoffice-base
Вопрос:
Я наследую некоторые данные в ужасном формате, первоначально в виде электронной таблицы и превратившейся в таблицу SQL, и я использую SQL (база LibreOffice в качестве прототипа, а затем, вероятно, MySQL) для запроса данных.
Мои исходные данные выглядят так:
| опт1 | teach_opt1 | опт2 | teach_opt2 | опт3 | teach_opt3 | опт4 | teach_opt4 |
|---|---|---|---|---|---|---|---|
| Французский | Хорошо | История | Выдающийся | IT | Требует улучшения | Математика | Хорошо |
| История | Хорошо | Французский | Выдающийся | Математика | Хорошо | Немецкий | Хорошо |
Где опция может быть в любом из opt{1,2,3,4}, а суждение об этой опции может быть в любом из teach_opt{1,2,3,4}.
Этот запрос очень помогает, просто группируя все решения{1,2,3,4} с одинаковым именем и суммируя итоги каждого решения:
SELECT "subject", "judgement", SUM("teaching") AS total FROM ( (SELECT "opt1" AS "subject", "teach_opt1" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt1" NOT LIKE 'None' AND "opt1" NOT LIKE '' AND "teach_opt1" NOT LIKE 'Don%do it' GROUP BY "opt1", "teach_opt1") UNION ALL (SELECT "opt2" AS "subject", "teach_opt2" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt2" NOT LIKE 'None' AND "opt2" NOT LIKE '' AND "teach_opt2" NOT LIKE 'Don%do it' GROUP BY "opt2", "teach_opt2") UNION ALL (SELECT "opt3" AS "subject", "teach_opt3" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt3" NOT LIKE 'None' AND "opt3" NOT LIKE '' AND "teach_opt3" NOT LIKE 'Don%do it' GROUP BY "opt3", "teach_opt3") UNION ALL (SELECT "opt4" AS "subject", "teach_opt4" AS "judgement", COUNT(*) AS "teaching" FROM "raw" WHERE "opt4" NOT LIKE 'None' AND "opt4" NOT LIKE '' AND "teach_opt4" NOT LIKE 'Don%do it' GROUP BY "opt4", "teach_opt4") ) t GROUP BY "subject", "judgement" ORDER BY "subject", "judgement"; доходность:
| предмет | суждение | весь |
|---|---|---|
| Французский | Хорошо | 15 |
| Французский | Выдающийся | 7 |
| IT | Хорошо | 11 |
| IT | Выдающийся | 14 |
| IT | Требует улучшения | 3 |
В конечном счете я хотел бы получить одну строку на тему с именованными столбцами и итогами. К сожалению, не у каждого субъекта есть все суждения. Например, потенциально «География», «Требует улучшения» могут отсутствовать в таблице.
| предмет | Выдающийся | Хорошо | Требует улучшения |
|---|---|---|---|
| Французский | 7 | 15 | 0 |
| IT | 14 | 11 | 3 |
Я думаю, что, прочитав, что PIVOT может быть полезной функцией, но я не могу понять, куда идти, чтобы достичь этого?
Ответ №1:
Вы можете использовать условную агрегацию для сводки промежуточных результатов следующим образом.
Перенос вашего текущего запроса (без его критериев упорядочения):
select subject, max(case when judgement='Outstanding' then total else 0 end) as Outstanding, max(case when judgement='Good' then total else 0 end) as Good, max(case when judgement='Requires improvement' then total else 0 end) as RequiresImprovement from ( select subject, judgement, Sum(teaching) as total from ( (select opt1 as subject, teach_opt1 as judgement, Count(*) as teaching from raw where opt1 not like 'None' and opt1 not like '' and teach_opt1 not like 'Don%do it' group by opt1, teach_opt1) union all (select opt2 as subject, teach_opt2 as judgement, Count(*) as teaching from raw where opt2 not like 'None' and opt2 not like '' and teach_opt2 not like 'Don%do it' group by opt2, teach_opt2) union all (select opt3 as subject, teach_opt3 as judgement, Count(*) as teaching from raw where opt3 not like 'None' and opt3 not like '' and teach_opt3 not like 'Don%do it' group by opt3, teach_opt3) union all (select opt4 as subject, teach_opt4 as judgement, Count(*) as teaching from raw where opt4 not like 'None' and opt4 not like '' and teach_opt4 not like 'Don%do it' group by opt4, teach_opt4) ) t group by subject, judgement )s group by subject order by subject Комментарии:
1. Отличное решение, большое спасибо