Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Выборка без дубликатов: ограничение в Postgres для GROUP BY и DISTINCT

Posted Май 13th, 2010 by ftrrtf
in

Есть три таблицы:

Объявления

CREATE TABLE advertisements (
    id integer NOT NULL,
    campaign_id integer,
    title character varying(40) NOT NULL,
    text character varying(100) NOT NULL,
    email character varying(255),
    website character varying(255),
    address character varying(255),
    phone character varying(14)
);

Комбинации (привязка направлений к объявлениям, другая служебная информация)

CREATE TABLE combinations (
    id integer NOT NULL,
    advertisement_id integer,
    direction_id integer,
    business_type_id integer,
    cid character varying(255),
    money double precision DEFAULT 0,
    price double precision DEFAULT 0,
    "limit" double precision,
    daily_sum double precision DEFAULT 0 NOT NULL
);

Направления для каждого из объявлений

CREATE TABLE advertisement_paths (
    id bigint NOT NULL,
    advertisement_id integer,
    direction_id integer
);

Необходимо выбрать несколько объявлений, которые попадают под определенные условия (по направлению, пройшли модерацию, етц), отсортированы по цене за клик (цена находится в таблице комбинаций) и чтобы были исключены повторы объявлений (так как на каждое объявление может былть несколько комбинаций, которые удовлетворяют запрос - то повторы встречаются в выборке).
Обязательно в результате должны быть отобраны несколько объявлений со всеми полями из таблицы advertisements и полями id, cid из таблицы combinations, чтобы знать по каким именно комбинация произошло совпадение.

Более-менее приемлемый вариант по производительности, НО имеем кучу дублирующихся объявлений.

SELECT ad.*, co.id AS cid, co.cid AS ccid, co.price AS price
FROM combinations AS co
LEFT JOIN advertisement_paths AS ap ON ap.advertisement_id = co.advertisement_id
LEFT JOIN advertisements AS ad ON ad.id = co.advertisement_id
WHERE co.STATUS = 'show'
        AND co.advertisement_status = 'show'
        AND co.campaign_status = 'active'
        AND co.money >= co.price
        AND (co.LIMIT IS NULL OR co.LIMIT >= (co.daily_sum+co.price))
        AND (
                (ap.direction_id IN (1, 2, 3, 4) OR co.direction_id IS NULL)  
                AND (co.business_type_id = 1 OR co.business_type_id IS NULL)
        )
ORDER BY price DESC LIMIT 10

Вариант с подзапросом и урезанием дубликатов через DISTINCT очень сильно проседает в производительности начиная уже от 100 000 строк (а будет не одна сотня тысяч).

SELECT ad.* , tmp.cid AS combination_id, tmp.ccid AS combination_cid, tmp.price AS price
FROM (
    SELECT DISTINCT ON (co.advertisement_id)
            co.advertisement_id AS id, co.id AS cid, co.cid AS ccid, co.price AS price
    FROM combinations AS co
    LEFT JOIN advertisement_paths AS ap ON ap.advertisement_id = co.advertisement_id
    WHERE co.STATUS = 'show'
            AND co.advertisement_status = 'show'
            AND co.campaign_status = 'active'
            AND co.money >= co.price
            AND (co.LIMIT IS NULL OR co.LIMIT >= (co.daily_sum+co.price))
            AND (
                    (ap.direction_id IN (1, 2, 3, 4) OR co.direction_id IS NULL)
                    AND (co.business_type_id = 1 OR co.business_type_id IS NULL)
            )
    ) AS tmp
LEFT JOIN advertisements AS ad ON ad.id = tmp.id
ORDER BY price DESC LIMIT 10

ORDER by price DESC LIMIT 10 во внутренний запрос (там, где DISTINCT) добавить не получится, так как там нужна сортировка по полю из DISTINCT.

А если во внутреннем запросе усекать дубликаты через GROUP BY - упираемся в ограничение на присутствие в SELECT'e только полей, которые непосредственно участвуют в группировке, или обернутые в агрегатные функции (а ведь поля combinations.id , combinations.cid как то надо вытягивать).

Может еще какие варианты выборки без дубликатов для Postgres есть? Буду очень благодарен.

‹ непонятные ошибки Выгрузка базы в приложение для моделироание данных ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Если параметры по которым

On Май 13th, 2010 Alarm says:

Если параметры по которым будет делатся выборка предопределены, тогда можно попробовать вариант с представлением, соответсвенно под каждую выборку свое представление

CREATE OR REPLACE VIEW adv_com AS
SELECT a.id aid, c.money, c.price, c."limit", c.daily_sum  FROM advertisements a JOIN combinations c ON a.id=advertisement_id;
SELECT DISTINCT p.id, v.aid, v.money, v.price, v."limit", v.daily_sum FROM advertisement_paths p JOIN adv_com v ON advertisement_id=v.aid

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

К сожалению, мне кажется, что

On Май 14th, 2010 admin says:

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

Цитата:
может стоит внести изменения в логику программы или интерфейс.

Вот это по-моему действительно стоит сделать.

Путей решения вижу несколько, но для меня пока что и данные таблицы и данные и ожидаемые результаты - что-то абстрактное, поэтому советы могу дать только самого общего плана:

1. Подумайте над изменением структуры БД, чтобы в ваших запросах не нужен был DISTINCT. Возможно придётся создать 1 или несколько доп таблиц, но это слихвой окупится быстротой запросов.
2. Создать таблицы быстрого доступа к данным, т.е. некие таблицы, куда информация будет выгружаться по определённым критериям, которые востребованы в типовых случаях. Например, таблица где будут только объявления прошедшие модерацию, что соответственно значительно упростит построение и отработку последующих запросов. Данные в этим таблицы могут выгружаться как в фоновом режиме, например по cron'у, так и с помощью триггеров, повешенных на основную таблицу. Т.е. например, сменился статус у объявления на прошедшее модерацию - сработал триггер, который это объявление добавил в таблицу объявлений прошедших модерацию.

И ещё. Я могу ошибаться, но мне сдаётся, что вы готовите всё это для какого-то веб-проекта, потому что именно там в поиске требуются такие параметры, которые приводят к выводу большого количества данных. В приложениях обычно критерии запросов намного жёсче и как следствие количество объявлений на выходе будет не более 100. Для 100 объявлений ненакладно делать DISTINCT. Для веб-проектов вам просто необходимо кэширование результатов поиска, если потом не ходите завалить ваш хостинг по производительности.

Не хватает данных

On Май 14th, 2010 eugeni says:

Не хватает данных о связях таблиц (внешних ключах).
Структура таблиц и запрос противоречат друг другу.
Поэтому далее мои измышления будут из того, как я это понял : )

Первичной является таблица Объявлений с primary key id.
Каждому объявлению соответствуют некие множественные свойства: комбинации, направления. Т.е. одному объявлению м.б. сопоставлено несколько цен и путей (напр., в зависимости от direction, bussines_type и т.п.) - связь таблиц один ко многим*.

Тогда задача будет: по комбинациям отобрать 10 различных "самых дорогих" объявлений (advertisement_id), удовлетворяющих неким условиям и "вытащить" их реквизиты из advertisements, правильно?
Примерный запрос:

WITH top_price AS (
    SELECT DISTINCT ON (advertisement_id)
            advertisement_id, id, cid, price
    FROM combinations AS co
    WHERE STATUS = 'show'
            AND advertisement_status = 'show'
            AND campaign_status = 'active'
            AND money >= price
            AND (LIMIT IS NULL OR LIMIT >= (daily_sum+price))
            AND ((direction_id IN (1, 2, 3, 4) OR direction_id IS NULL)
                     AND (business_type_id = 1 OR business_type_id IS NULL)
                   )
    ORDER BY price LIMIT 10
    )
SELECT ad.* , tp.id AS combination_id, tp.cid AS combination_cid, tp.price AS price
FROM top_price tp
INNER JOIN advertisements AS ad ON (ad.id = tp.advertisement_id)

WITH использован только для удобства чтения.
Идея такая: 1. запихнуть LIMIT в подзапрос.
В вашем запросе чтобы отобрать DISTINCT нужно отсортировать всю многотысячную таблицу Комбинаций, объединить ее еще с двумя, снова отсортировать по цене чтобы потом взять 10 строк. Если таблица большая, при сортировке не помещается в памяти - будут частые обращения к диску, отсюда сильные тормоза.
LIMIT в подзапросе исключает сортировку всей таблицы. Оптимизатор фактически сортирует только 10 строк: при выборе очередной строки, удовлетворяющей условиям, она сравнивается с отобранными ранее, если меньше - отбрасывается, если попадает в диапазон - отбрасывается последняя из набора, а найденная вставляется в набор.
Далее уже только 10 строк объединяются с таблицей объявлений.
2. Вам же не нужны объявления без атрибутов (или атрибуты без объявлений)? Поэтому INNER JOIN. По моему опыту работает гораздо быстрее LEFT'a.

Попробуйте поэкспериментировать в этом направлении, у меня нет достаточного объема базы для проверки.
Отпишитесь, что получиться : )

* Здесь смущает то, что в запросе сделано наоборот Комбинации left join Объявления - якобы комбинация м.б. связана с несколькими или ни с одним объявлением?! И почему Направления объединяются с Комбинациями - если это не опечатка, можно Комбинации непосредственно фильтровать по direction_id, а Направления из запроса выкинуть.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023