Почему запрос объединяет не те строки?

Question

[Oleg Seledets]

Здравствуйте, помогите исправить ошибку в запросе:
Есть таблица:

SELECT 
    reservtable.sudovlad,
    reservtable.summa,
    reservtable.years,
    ((reservtable2.summa2 - reservtable.summa) / reservtable.summa * 100) AS percent,
    reservtable2.summa2,
    reservtable2.years2
FROM
    (SELECT 
        sudovlad,
            SUM(gruz_pass) AS summa,
            EXTRACT(YEAR FROM data_vih_p_ot) AS years
    FROM
        ships
    WHERE
        sudovlad IN ('Русфлот, ООО', 'АрктикТранс ОИЛ, ООО','pere')
    GROUP BY EXTRACT(YEAR FROM data_vih_p_ot)) reservtable,
    (SELECT 
        sudovlad,
            SUM(gruz_pass) AS summa2,
            EXTRACT(YEAR FROM data_vih_p_ot) AS years2
    FROM
        ships
    WHERE
        sudovlad IN ('Русфлот, ООО', 'АрктикТранс ОИЛ, ООО','pere')
    GROUP BY EXTRACT(YEAR FROM data_vih_p_ot)) reservtable2
WHERE
    reservtable.years = 2018
        OR reservtable2.years2 = 2017

Выводит результат

Необходимо, чтобы результат был вида:
Русфлот, ООО' 14 2018 -22,2 18 2017
АрктикТранс ОИЛ 28 2018 100 0 2017
pere 69 2018 475 12 2017

У какого-либо судовладельца есть данные за 2018 и 2017 год перевозки, бывает такое, что перевозчик в 2017 или в 2018 году не участвовал в перевозках, поэтому данные только за один год.
Но необходимо получить сводную таблицу в которой:

1. Судовладелец
   
2. Количество перевезенного груза
   
3. 2018 год
   
4. Процент как разница
   
5. Количество перевезенного груза
   
6. 2017 год
   

Если данных нет, например за 2017 год, чтоб те столбцы имели 0 или ничего вообще.

Answer 1

[di]

Лучше Join'ы для таких запросов использовать

SELECT 
    ships.sudovlad,
    s1.gruz_pass as summa,
    EXTRACT(YEAR FROM s1.data_vih_p_ot) as years,
    ((s2.gruz_pass - s1.gruz_pass) / s1.gruz_pass * 100) AS percent,
    s2.gruz_pass as summa2,
    EXTRACT(YEAR FROM s2.data_vih_p_ot) AS years2
FROM ships
LEFT JOIN ships as s1
ON ships.sudovlad = s1.sudovlad AND EXTRACT(YEAR FROM s1.data_vih_p_ot)=2017 AND s1.sudovlad IN ('Русфлот, ООО', 'АрктикТранс ОИЛ, ООО','pere')
LEFT JOIN ships as s2
ON ships.sudovlad = s2.sudovlad AND EXTRACT(YEAR FROM s2.data_vih_p_ot)=2018 AND s2.sudovlad IN ('Русфлот, ООО', 'АрктикТранс ОИЛ, ООО','pere')
GROUP BY sudovlad

Comments

[Oleg Seledets]

Спасибо, только под выборку попадают все судовладельцы которые есть вообще, и сумма не считается в грузах((

[di]

Oleg Seledets, Вообщем смотри в чем моя логика.
Выберем сумму перевозок за 2017 год для всех компаний например

SELECT sudovlad,SUM(gruz_pass) as summa ,EXTRACT(YEAR FROM data_vih_p_ot) as year FROM ships WHERE EXTRACT(YEAR FROM data_vih_p_ot) = 2017 GROUP BY sudovlad

И почти так же за 2018

SELECT sudovlad,SUM(gruz_pass) as summa ,EXTRACT(YEAR FROM data_vih_p_ot) as year FROM ships WHERE EXTRACT(YEAR FROM data_vih_p_ot) = 2018 GROUP BY sudovlad

А теперь делаем Left Join для таблицы ships, тоесть объединяем ее с 2 таблицами из 2 предыдущих запросов. Получается что то вроде того.

SELECT 
    ships.sudovlad,
    s1.summa,
    s1.year as years,
    ((s2.summa - s1.summa) / s1.summa * 100) AS percent,
    s2.summa as summa2,
    s2.year AS years2
FROM ships
LEFT JOIN (
    SELECT sudovlad,SUM(gruz_pass) as summa ,EXTRACT(YEAR FROM data_vih_p_ot) as year 
    FROM ships WHERE EXTRACT(YEAR FROM data_vih_p_ot) = 2017 GROUP BY sudovlad) as s1
ON s1.sudovlad = ships.sudovlad
LEFT JOIN (
    SELECT sudovlad,SUM(gruz_pass) as summa ,EXTRACT(YEAR FROM data_vih_p_ot) as year 
    FROM ships WHERE EXTRACT(YEAR FROM data_vih_p_ot) = 2018 GROUP BY sudovlad) as s2
ON s2.sudovlad = ships.sudovlad
WHERE ships.sudovlad IN ('Русфлот, ООО', 'АрктикТранс ОИЛ, ООО','pere')
GROUP BY sudovlad

Я сам не гуру. Так что в плане оптимизации это может быть не лучший запрос, но вроде рабочий.

[Oleg Seledets]

Алексей Долгов, Благодарочка, еще и за объяснения, а то я с концами запутался в этом запросе))

[Oleg Seledets]

Переписал полностью как надо)

UPD

SELECT 
    ships.sudovlad AS 'Судовладелец',
    IF(s1.summa IS NULL, 0, s1.summa) AS 'Грузы',
    IF(s2.psumma IS NULL, 0, s2.psumma) AS 'Пассажиры',
    IF(((s2.summa - s1.summa) / s1.summa * 100) IS NULL,
        0,
        ROUND(((s2.summa - s1.summa) / s1.summa * 100),
                2)) AS 'Грузы%',
    IF(((s2.psumma - s1.psumma) / s1.psumma * 100) IS NULL,
        0,
        ROUND(((s2.psumma - s1.psumma) / s1.psumma * 100),
                2)) AS 'Пассажиры%',
    IF(s2.summa IS NULL, 0, s2.summa) AS 'Грузы',
    IF(s2.psumma IS NULL, 0, s2.psumma) AS 'Пассажиры'
FROM
    ships
        LEFT JOIN
    (SELECT 
        sudovlad,
            SUM(gruz_pass) AS summa,
            SUM(tonn_ball) AS psumma,
            EXTRACT(YEAR FROM data_vih_p_ot) AS year
    FROM
        ships
    WHERE
        EXTRACT(YEAR FROM data_vih_p_ot) = 2018
    GROUP BY sudovlad) AS s1 ON s1.sudovlad = ships.sudovlad
        LEFT JOIN
    (SELECT 
        sudovlad,
            SUM(gruz_pass) AS summa,
            SUM(tonn_ball) AS psumma,
            EXTRACT(YEAR FROM data_vih_p_ot) AS year
    FROM
        ships
    WHERE
        EXTRACT(YEAR FROM data_vih_p_ot) = 2017
    GROUP BY sudovlad) AS s2 ON s2.sudovlad = ships.sudovlad
WHERE
    ships.sudovlad IN ('Русфлот, ООО' , 'АрктикТранс ОИЛ, ООО',
        'pere')
GROUP BY Судовладелец