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

fTR

Дата 13.5.2010, 16:39 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 5
Регистрация: 28.10.2007

Репутация: нет
Всего: нет

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

Объявления

Код


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 есть? Буду очень благодарен.

Zloxa

Дата 14.5.2010, 11:59 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(fTR @ 13.5.2010, 16:39

)

цена находится в таблице комбинаций

Цитата(fTR @ 13.5.2010, 16:39

)

на каждое объявление может былть несколько комбинаций, которые удовлетворяют запрос

если одно объявление имеет несколько цен, какая из цен должна использоваться в ранжировании?
я предположил что максимальная

Код


SELECT ad.* , tmp.cid AS combination_id, tmp.ccid AS combination_cid, tmp.price as price
FROM (
    SELECT co.advertisement_id AS id, co.id AS cid, co.cid AS ccid, co.price AS price
            ,row_number() over (partition by co.advertisement_id order by co.price desc) as rn
    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
where tmp.rn = 1
ORDER by price DESC LIMIT 10

Полагаю этот запрос врядли будет работать быстрее.
Зато будет работать правильно.

Это сообщение отредактировал(а) Zloxa - 14.5.2010, 20:58

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »