Выбрать строки в первой таблице по условию частоты во второй

Question

[xjunkiex]

есть postgresql надо вытащить сопоставленные по условию данные из двух таблиц:
loy_transaction (со столбцами id, purchase_number, cash_number, shift_number, shop_number, sale_time, transaction_time, operation_type, status, discountvalue)
и
loy_bonus_transactions ( id, bonus_account_type, bonus_amount, bonus_period_finish, bonus_period_start, discount_card, advert_act_guid, transaction_id )
жирным шрифтом столбцы с одинаковыми значениями по ним можно сопоставить строки.

требуется такой запрос который который будет выводить номер карты (discount_card) если в одной и той–же смене (shift_number) карта участвовала более 3х раз.

Я остановился вот на таком варианте:

но, судя по всему, выдает все, так как строки с одной и той–же сменой не привязаны к транзакциям с начислениями из таблицы loy_bonus_transactions

Answer 1

[Maxim Kalenich]

Наверное как-то так

SELECT lbt.`discount_card`, COUNT(1) as num
FROM `loy_transaction` as lt
LEFT JOIN `loy_bonus_transactions` as lbt
ON lt.`id` = lbt.`transaction_id`
GROUP BY lt.`shift_number`, lbt.`discount_card`
HAVING COUNT(1) > 3;

Comments

[xjunkiex]

Отлично, правда некоторые строки пустые с огромными значениями, не могу понять как они туда попадают, есть мысли?
скриншот:
itmages.ru/image/view/1660772/68a7f084

[Maxim Kalenich]

Всё очень просто!

Поясню селект на словах, думаю станет понятно.
Мы выгребаем дискаут_кард и подсчитываем их вхождение из всех записей по склейке баз транзакций <+ бонус_транзакций (LEFT JOIN).

Это значит, что в финальной выборке могут быть записи без указания параметров из базы бонус_транзакций, при этом поля будут забиты null-ом.
Т.е. мы отдали приоритет целостности, где её гарантом выступает список всех проводок а не бонусов.

Если на первое место ставится именно регистрация бонуса, то нужно либо поменть порядок баз (FROM `loy_bonus_transactions` as lbt LEFT JOIN `loy_transaction` as lt ON lbt.`transaction_id` = lt.`id`) и тогда критерием целостности будет проводка бонуса, либо сделать INNER JOIN и тогда получить выборку где в обязательном порядке есть все поля.

INNER JOIN — делается медленнее.

Ну и в довесок ссылка на читшит dsin.files.wordpress.com/2013/03/sqljoins_cheatshe...

[whats]

@Maxja ниже я написал запрос который делает то что он хочет, просто ТС думать не хочет.
В вашем запросе не понятно что такое COUNT(1) as num. Зачем оно ?
да и с чего вдруг обычный join Делается дольше чем left ?

[xjunkiex]

@whats count(1) as num это количество срабатывания карты в рамках одной смены, очень наглядный столбец, позволяет заметить нереальные значения.

[whats]

@xjunkiex понял, не привычно видеть единичку в скобках, обычно пишут звездочку или имя столбца. на своей базе делал обычный Джоин и лефт джоин. Скорость не изменилась в таблицах по 3 миллиона строк.

[xjunkiex]

господин-товарищ @whats , подскажите, каким образом можно поставить фильтр по интервалу времени (timestamp_without_timezone)
например, требуется вывести все значения у которых столбец sale_time лежит в интервале май 2014
я делаю следующее, но ничего не получается.
SELECT *
FROM loy_transaction
WHERE
sale_time = '2014-05-01 00:00:00.000'::TIMESTAMP + interval '1 month 1 day 1 minute'

Answer 2

[xjunkiex]

img.leprosorium.com/2193396 мой запрос - фулсайз

Answer 3

[whats]

Select 
    * 
FROM 
    loy_transaction lt 
JOIN 
    loy_bonus_transactions lbt 
ON 
    lt.id = lbt.transaction_id 
group by 
    lbt.shift_number , lbt.discount_card 
HAVING count(*) > 3

Comments

[xjunkiex]

ERROR: column "lt.id" must appear in the GROUP BY clause or be used in an aggregate function

и, кстати, не lbt.shift_number а lt.shift_number

[whats]

Ну голову то нужно включить ? Или тупо готовым воспользоваться ? При группировке не работает селект по звездочке.

[xjunkiex]

я только начинаю знакомиться с sql

[whats]

@xjunkiex тогда прими к сведению что при группировке выводятся колонки которые перечисляются после параметра group by или те которые находятся в агрегатных функциях.