Блог

вот в чем проблема:

У меня есть таблица транспортных средств в бд (поля этой таблицы не так важны), важно то, что у каждого транспортного средства есть идентификатор model_id, который ссылается на таблицу vehicle_models.

Таблица моделей транспортных средств содержит поля id, класс, модель, серия, см3 л. с., created_at и updated_at. Мне нужно определить возраст запасов с точки зрения того, сколько транспортных средств определенного класса моделей имеется на складе по заданным критериям. Критерии: 0-30 дней, 31-60 дней, 61-90 дней… 360 дней… Я не знаю, достаточно ли это ясно, но позвольте мне попытаться объяснить еще лучше: для каждого дневного диапазона мне нужно найти количество автомобилей с данным классом моделей. Есть и другие критерии, но это не важно для того, что я пытаюсь выяснить. Чтобы помочь вам лучше понять проблему, я включу скриншот того, как должна выглядеть структура:

Я использую MySQL 8.

Запрос, который я написал,:

 SELECT DISTINCT vm.class,
    IFNULL(t1.count, 0) as t1c,
    IFNULL(t2.count, 0) as t2c,
    IFNULL(t3.count, 0) as t3c,
    IFNULL(t4.count, 0) as t4c,
    IFNULL(t5.count, 0) as t5c,
    IFNULL(t6.count, 0) as t6c,
    IFNULL(t7.count, 0) as t7c
FROM vehicle_models vm
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 0 AND 30
        GROUP BY vm.class
    ) t1 ON t1.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 31 AND 60
        GROUP BY vm.class
    ) t2 ON t2.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 61 AND 90
        GROUP BY vm.class
    ) t3 ON t3.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 91 AND 120
        GROUP BY vm.class
    ) t4 ON t4.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 121 AND 240
        GROUP BY vm.class
    ) t5 ON t5.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) BETWEEN 241 AND 360
        GROUP BY vm.class
    ) t6 ON t6.class = vm.class
    LEFT JOIN (
        SELECT 
            vm.class as class,
            count(*) as count
        FROM a3s186jg7ffmm0q8.vehicles v
            JOIN vehicle_models vm 
            ON vm.id = v.model_id
        WHERE 
            DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) > 360
        GROUP BY vm.class
    ) t7 ON t7.class = vm.class
ORDER BY vm.class;
 

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

Я предполагаю, что вы представляете отчет о старении запасов (о том, как долго эта машина стоит на стоянке дилера, прежде чем кто-то ее купит). Вы можете указать возрастные диапазоны в своем выборе верхнего уровня, а не помещать каждый из них в отдельный подзапрос. Это сделает ваш запрос быстрее (подзапросы имеют определенную стоимость) и короче / проще для чтения.

Попробуйте что-то вроде этого вложенного запроса. Внутренний запрос возвращает одну строку для каждого транспортного средства с его старым номером. Внешний запрос объединяет их.

 SELECT class,
       COUNT(*) total,
       SUM(age BETWEEN 0 AND 30) t1c,
       SUM(age BETWEEN 31 AND 60) t2c,
       SUM(age BETWEEN 61 AND 90) t3c,
       ... etc ...
  FROM (
      SELECT vm.class, 
             DATEDIFF(IFNULL(v.retail_date, now()), v.wholesale_date) age
        FROM a3s186jg7ffmm0q8.vehicles v
        JOIN vehicle_models vm  ON vm.id = v.model_id
       ) subq
 GROUP BY class
 ORDER BY class;
 

Этот трюк SUM() работает в MySQL, потому что выражения типа age BETWEEN 0 AND 30 имеют значение 1 при значении true и 0 при значении false.