SELECT запрос: по X записей из каждой категории

Тема в разделе "Базы данных", создана пользователем papa, 6 окт 2017.

Модераторы: latteo
  1. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    Я где-то находил образец выполнения такой задачи. Но хоть убейте - не соображу\нахожу щас. Просьба помочь.
    Задача: есть таблицы
    Код:
    CREATE TABLE `ZAnuCu_OnuCAHuE`(
    `id` INTEGER PRIMARY KEY,
    `HAZBAHuE` TEXT ASC,
    `MODELb` TEXT,
    `KPATHOCTb` INTEGER,
    `DOCTynHIOCTb` INTEGER,
    `KAPTuHKA` TEXT,
    `KATEGOPuia` INTEGER,
    `PAZDEL` INTEGER,
    `OnuCAHuE` TEXT,
    `CTATyC` TEXT,
    `PEDAKTuPOBAHuE` TEXT);
    
    CREATE TABLE `ZAnuCu_KATEGOPuu`(
    `id` INTEGER ASC,
    `KATEGOPuia` INTEGER ASC,
    `PAZDEL` INTEGER ASC,
    `PEDAKTuPOBAHuE` TEXT);
    
    CREATE TABLE `KATEGOPuu`(
                            `id_KATEGOPuu` INTEGER PRIMARY KEY,
                            `HAZBAHuE` TEXT,
                            `HAZBAHuE_nOLHOE` TEXT ASC,
                            `PODuTELb` INTEGER,
                            `PAZDEL` INTEGER,
                            `yPOBEHb` INTEGER,
                            `KOLu4ECTBO` INTEGER,
                            `PEDAKTuPOBAHuE` TEXT
                            );
    
    Как мне получить "список категорий и по 1 записи в каждой из них"? Мои попытки, связанные с LIMIT не увенчались успехом:
    Код:
    SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`, z.`HAZBAHuE` AS ZAnuCb 
    FROM `KATEGOPuu` k, `ZAnuCu_OnuCAHuE` z, `ZAnuCu_KATEGOPuu` z_k 
    WHERE z_k.`KATEGOPuia`=k.`id_KATEGOPuu` 
    LIMIT 0,10;
    
    Выдаёт 1 категорию и 10 записей в ней(10 раз категорию, и в каждой по 1 записи, разной). А мне надо: список категорий + колонка "ZAnuCb" в которой по 1 записи из каждой категории. Причём с сортировкой по алфавиту. Запрос вида
    Код:
    SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`, z.`HAZBAHuE` AS ZAnuCb 
    FROM `KATEGOPuu` k, `ZAnuCu_OnuCAHuE` z, `ZAnuCu_KATEGOPuu` z_k 
    WHERE z_k.`KATEGOPuia`=k.`id_KATEGOPuu`
    GROUP BY k.`id_KATEGOPuu` ORDER BY k.`yPOBEHb` ASC, k.`HAZBAHuE` ASC, z.`HAZBAHuE` ASC 
    LIMIT 0,10;
    
    Уходит в астрал. Если убираю "GROUP BY" - всё отрабатывает, но не правильно. Собственно: что я упустил? Как запрос составить?
     
  2. zabolots

    zabolots Постоялец

    Регистр.:
    11 сен 2012
    Сообщения:
    56
    Симпатии:
    21
    Это все будет в php-скрипте работать?
    Не проще вам сначала получить список категорий, а потом в скрипте для каждой категории одну (LIMIT 1) запись для каждой?
    Чтобы в астрал не уходил запрос, делайте индексы по полям сортировки и объединения.
    <-------------- добавлено через 2045 сек. -------------->
    Индексы по полям id_KATEGOPuu, KATEGOPuia, id_ZAnuCu есть?
    WHERE 0<=k.`id_KATEGOPuu` <- типо все категории - это зачем? если нужны все, то не нужно никаких условий (where ограничивает выборку, а не расширяет ее)... а у вас что, отрицательные категории?

    Вопрос: сколько у вас записей в каждой из таблиц? Такой запрос (с индексами правильными) не должен выполняться заметное время при нескольких тысячах записей. Тормоза могут быть начиная с миллиона.
     
  3. sound_smith

    sound_smith Писатель

    Регистр.:
    19 май 2014
    Сообщения:
    3
    Симпатии:
    0
    Записей много - неудивительно что тормозит. Можно посмотреть в сторону тюнинга конфига БД.
     
  4. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    Всё это в SQLite 3. Прога на C#. Щас смотрю запрос
    Код:
    SELECT k.`id_KATEGOPuu`, k.`HAZBAHuE`, k.`HAZBAHuE_nOLHOE`, k.`PODuTELb`, k.`yPOBEHb`, `KOLu4ECTBO`, k.`PEDAKTuPOBAHuE`,
    (SELECT t0.`HAZBAHuE` FROM `ZAnuCu_OnuCAHuE` t0 WHERE k.`id_KATEGOPuu` =  t_k.`KATEGOPuia` AND t_k.`id_ZAnuCu` = t0.`id_ZAnuCu` ORDER BY t0.`HAZBAHuE` ASC LIMIT 0,1) AS ZAnuCb
    FROM `KATEGOPuu` k, `ZAnuCu_KATEGOPuu` t_k
    WHERE 0<=k.`id_KATEGOPuu`            <- типо все категории
    GROUP BY k.`id_KATEGOPuu`
    ORDER BY k.`yPOBEHb` ASC, k.`HAZBAHuE` ASC;
    
    В принципе, как я понимаю, он работает. Но очень долго обрабатывает. Индексы есть. Щас провожу эксперименты по ускорению. Может кто-что посоветует?
    <-------------- добавлено через 1824 сек. -------------->
    Таблица:
    KATEGOPuu - ~1300 записей.
    ZAnuCu_OnuCAHuE ~ 800 000 записей
    ZAnuCu_KATEGOPuu ~ 1 000 000 записей

    В "ZAnuCu_OnuCAHuE" - планируется около 20 000 000 записей.

    Есть.
    <-------------- добавлено через 2122 сек. -------------->

    Например? Я рылся в инете и отключил всё, что может притормаживать. Т.е. база должна работать только на выдачу инфы. Может предложите конфиг?
     
  5. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    А мне кажется, что не понимаете. Профиль выполнения смотрели? С этого надо начинать, а не ставить куда попало индексы (вы хоть понимаете как индексы работают?) и менять как попало конфиг. Это мартышкин труд.
    Используйте "EXPLAIN" и "EXPLAIN QUERY PLAN". Да, придется поизучать и поднапрячь мозг. Если хотите, чтобы я попробовал это сделать вместе с вами, выложите полную схему БД и дамп данных, на 10 тестовых сточках в таблице оптимизировать невозможно.
     
  6. Rammstein_91

    Rammstein_91 Создатель

    Регистр.:
    30 июн 2015
    Сообщения:
    9
    Симпатии:
    0
    А прога (вопрос в большей степени про расположение бд) будет доступна онлайн или офлайн? Если онлайн, то может скорости и пропускной способности сервера не хватает?
     
  7. papa

    papa Постоялец

    Регистр.:
    28 сен 2006
    Сообщения:
    148
    Симпатии:
    3
    База данных на 2 гига(sqlite файл - дамп базы данных сайта), на ~800 000 записей, и будет только расти.
    База для проги - выкачивается из инета. И храниться в папке с прогой.
    Была изменена задача. Т.к. запрос более 30 секунд, а таблица(про информацию: какая запись - в какой категории(т.е. 1 запись может принадлежать нескольким категориям)), при 800 000 уникальных записей, составляет 20 000 000, было решено изменить задачу. Если в кратце: главная задача: показать больше количество записей. На моменте создание базы SQLite была приделана сортировка, т.е. записи сортируются на входе(запрос к скрипту, он создаёт файл базы для проги, и даёт на неё ссылку, через которую она перетекает на комп), и при считывании данных(локально) вычёркиваем "ORDER BY", т.к. это самая трудоёмкая задача при запросе. А в самой проге пишем(типо работает цикл на вывод всех записей) "Если количество записей(записанных в переменную на вывод в контрол) кратно 10000(например) - Отрисовываем записи в контроле(скажем нечто с реализованным "NotifyCollectionChangedEvent")". В результате: "перерисовка каждые секунд 15" и постепенно выводятся записи. Человек радуется.