Перекрестная таблица SQL не показывает правильные значения

Я пытаюсь создать перекрестную таблицу, которая дает мне количество типов аварий по степени тяжести травм, и данные находятся в трех таблицах (аварии, пассажиры и пешеходы). Таблицы «Жители и пешеходы» имеют связь «многие к одному» с авариями, поскольку в авариях обычно участвует более одного человека. Я создал представление, позволяющее назначать степень серьезности аварии каждому сбою_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);

Вернет это:

тип_крушения 01 – Смертельная травма 02 – Подозрение на серьёзную травму 03 – Подозрение на незначительную травму 04 – Возможная травма Только материальный ущерб 00 – неизвестно 1 1 24 01 – в том же направлении (задняя часть) 9 40 534 2784 10678 02 – в том же направлении (смахивание в сторону) 5 12 137 525 5725 03 – Прямой угол 10 41 540 1785 г. 4825 04 — Противоположное направление (лобовое, угловое) 8 19 131 322 645 05 – Противоположное направление (смахивание в сторону) 3 3 30 60 474 06 – удар припаркованного автомобиля 4 20 115 263 5763 07 – Поворот налево/Разворот 5 7 125 249 686 08 – Поддержка 6 46 1559 г. 09 – Посягательство 12 17 154 10 - Перевернут 2 8 87 62 86 11 - Фиксированный объект 33 95 585 880 4810 12 - Животное 36 57 2247 13 - Пешеход 55 46 205 292 92 14 - Педациклист 6 17 121 108 52 15 - Нефиксированный объект 1 5 20 18 511 16 - Вагон - Транспортное средство 2 99 - Другое 2 3 36 20 288

Но строка 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 2019110319-29497 00 03 – Подозрение на незначительную травму 2017111117-004544 00 04 – Возможная травма 2017110317-36014 00 Только материальный ущерб 2017110717-38624-AC 00 Только материальный ущерб 2017111117-006380 00 Только материальный ущерб 2017111117-006610 00 Только материальный ущерб 2017111117001399 00 Только материальный ущерб 2017111117001803 00 Только материальный ущерб 2017111417-19645 00 Только материальный ущерб 2017111417-25979 00 Только материальный ущерб 201811022018-01727 00 Только материальный ущерб 2018110318-28221 00 Только материальный ущерб 2018111118004932 00 Только материальный ущерб 2018111118004944 00 Только материальный ущерб 2018111118012298 00 Только материальный ущерб 2018111118014205 00 Только материальный ущерб 2018111418-17386 00 Только материальный ущерб 2020110120-9355 00 Только материальный ущерб 2020110320-25261 00 Только материальный ущерб 2020110320-30145 00 Только материальный ущерб 2020111120003327 00 Только материальный ущерб 2020111120003940 00 Только материальный ущерб 2020111420-24013 00 Только материальный ущерб 2021110321-078180 00 Только материальный ущерб 2021111121006276 00 Только материальный ущерб 2021111421-13746 00 Только материальный ущерб

Другие области, вероятно, тоже неверны, я не все проверял, так как очевидно, что все работает неправильно. Любая помощь приветствуется.

Вот пример представления «crash_severity»:

сбой_ид crash_severity 2017110114-360 Только материальный ущерб 2017110117-10026 Только материальный ущерб 2017110117-10075 Только материальный ущерб 2017110117-10209 04 – Возможная травма 2017110117-1022 Только материальный ущерб 2017110117-10231 Только материальный ущерб 2017110117-10253 03 – Подозрение на незначительную травму 2017110117-10266 Только материальный ущерб 2017110117-1029 04 – Возможная травма 2017110117-10318 Только материальный ущерб 2017110117-10329 03 – Подозрение на незначительную травму 2017110117-10363 Только материальный ущерб 2017110117-10386 Только материальный ущерб 2017110117-10392 04 – Возможная травма 2017110117-1040 Только материальный ущерб 2017110117-10451 04 – Возможная травма 2017110117-10462 Только материальный ущерб 2017110117-10507 Только материальный ущерб 2017110117-10545 Только материальный ущерб 2017110117-10632 Только материальный ущерб 2017110117-1067 04 – Возможная травма 2017110117-10678 Только материальный ущерб 2017110117-10714 Только материальный ущерб 2017110117-10715 Только материальный ущерб 2017110117-10717 04 – Возможная травма 2017110117-10756 Только материальный ущерб

Привет! Пожалуйста, обновите свой вопрос, указав образцы данных для исходных таблиц и результат, которого вы хотите достичь, в виде редактируемого текста, отформатированного в виде таблиц. Пожалуйста, не используйте изображения, поскольку никто не сможет скопировать ваши данные/код.

NickW 07.06.2024 20:54

@NickW Я обновил вопрос, дайте мне знать, если вам нужна дополнительная информация!

Chris 08.06.2024 19:10
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
50
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

кажется, просто не учитывается тот факт, что первые два столбца равны нулю

Да, это не так.

Я не могу понять, что я делаю не так

Вы СЧИТАЕТЕ данные из измерения. Они всегда ненулевые. Вам следует СЧИТАТЬ факты.

Это должно работать правильно:

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);

Другие вопросы по теме