Я пытаюсь создать перекрестную таблицу, которая дает мне количество типов аварий по степени тяжести травм, и данные находятся в трех таблицах (аварии, пассажиры и пешеходы). Таблицы «Жители и пешеходы» имеют связь «многие к одному» с авариями, поскольку в авариях обычно участвует более одного человека. Я создал представление, позволяющее назначать степень серьезности аварии каждому сбою_id на основе таблиц Occupants и Phodes. Теперь, когда я запускаю приведенный ниже запрос, общее количество оказывается правильным, но количество в каждой категории не всегда верно.
SELECT *
FROM crosstab(
'SELECT CONCAT(ct.crash_type_code, '' - '', ct.crash_type_desc) AS crash_type,
cs.crash_severity,
COALESCE(count(cs.crash_severity), 0) AS count
FROM (
SELECT DISTINCT crash_type_code, crash_type_desc FROM crash_type_lookup
) ct
CROSS JOIN (
SELECT DISTINCT crash_severity FROM crash_severity
) cs
LEFT JOIN crash_severity ON cs.crash_severity = crash_severity.crash_severity
AND crash_severity.crash_type = ct.crash_type_code
GROUP BY ct.crash_type_code, ct.crash_type_desc, cs.crash_severity
ORDER BY 1,2'
)
AS CrashType(crash_type text,
"01 - Fatal Injury" bigint,
"02 - Suspected Serious Injury" bigint,
"03 - Suspected Minor Injury" bigint,
"04 - Possible Injury" bigint,
"Property Damage Only" bigint);
Вернет это:
Но строка 1, категория 00 — Неизвестно, должна быть нулевой для первых двух столбцов (Смертельная травма и Подозрение на серьезную травму) и 1, 1, 24 для последних трех столбцов. Кажется, просто не учитывается тот факт, что первые два столбца имеют значение NULL, и я не могу понять, что делаю неправильно.
Вот образец данных, показывающий, как следует распределять 00 — Неизвестные типы сбоев.
mercer_crashes=# ВЫБЕРИТЕ c.crash_id, сбой_тип_код, сбой_severity ИЗ сбоев c LEFT JOIN сбой_severity cs ON c.crash_id = cs.crash_id WHERE сбой_тип_код = '00';
Другие области, вероятно, тоже неверны, я не все проверял, так как очевидно, что все работает неправильно. Любая помощь приветствуется.
Вот пример представления «crash_severity»:
@NickW Я обновил вопрос, дайте мне знать, если вам нужна дополнительная информация!
кажется, просто не учитывается тот факт, что первые два столбца равны нулю
Да, это не так.
Я не могу понять, что я делаю не так
Вы СЧИТАЕТЕ данные из измерения. Они всегда ненулевые. Вам следует СЧИТАТЬ факты.
Это должно работать правильно:
SELECT *
FROM crosstab(
'SELECT CONCAT(ct.crash_type_code, '' - '', ct.crash_type_desc) AS crash_type,
cs.crash_severity,
COALESCE(count(crash_severity.crash_severity), 0) AS count
FROM (
SELECT DISTINCT crash_type_code, crash_type_desc FROM crash_type_lookup
) ct
CROSS JOIN (
SELECT DISTINCT crash_severity FROM crash_severity
) cs
LEFT JOIN crash_severity ON cs.crash_severity = crash_severity.crash_severity
AND crash_severity.crash_type = ct.crash_type_code
GROUP BY ct.crash_type_code, ct.crash_type_desc, cs.crash_severity
ORDER BY 1,2'
)
AS CrashType(crash_type text,
"01 - Fatal Injury" bigint,
"02 - Suspected Serious Injury" bigint,
"03 - Suspected Minor Injury" bigint,
"04 - Possible Injury" bigint,
"Property Damage Only" bigint);
Привет! Пожалуйста, обновите свой вопрос, указав образцы данных для исходных таблиц и результат, которого вы хотите достичь, в виде редактируемого текста, отформатированного в виде таблиц. Пожалуйста, не используйте изображения, поскольку никто не сможет скопировать ваши данные/код.