?

Log in

No account? Create an account
Паранойя, отпущенная на свободу
Записки штурмраухермастера
Какой-то у вас джойн левый.... 
28th-Oct-2015 11:25 am
dead horse
Что-то я туплю.

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

cdr( src_phone varchar, duration int, calldate timestamp);

phone_agreement ( phone varchar, agr_id int );

bills( agr_id int, value numeric, yearmonth int, number varchar );

и пытаюсь я родить селект:

для всех телефонов, приведенных в phone_agreement, у которых есть счета за указанный месяц, выбрать - номер счета, сумму счета, общее время звонков.

И что-то я ни фига не понимаю уже.

Если я делаю выборку с указанием месяца счета и интервала для CDR, то я получаю явно не все счета.

А если убираю интервал - то явно не то время.

Кто может просветить?
Comments 
28th-Oct-2015 08:41 am (UTC)
Явно не все счета, у которых было ненулевое время звонков в указанный месяц?

В общем, я б начал с того, что делал с таблицей CDR левый внешний join.
А может и с bills тоже. Поскольку первое, что приходит в голову, это то, что разговор в cdr датирован январем, а счет в bills за него - февралем.

Далее,подозрительно то, что дата в cdr timestamp, а в bills - int. Может там при преобразовании даты в интервал какая-то кривизна получается?
28th-Oct-2015 09:14 am (UTC)
"Номер счета" - это bills.number, я так понимаю?
Тогда:

select bills.number, bills.value, cdr.duration from bills
left join phone_agreement on (phone_agreement.agr_id = bills.agr_id)
left join cdr on (cdr.src_phone = phone_agreement.phone)
where bills.yearmonth='месяц'

Проверить, что выводятся все счета, и дальше просуммировать как sum(bills.value) и sum(cdr.duration).

upd: Тьфу, гоню, надо же еще cdr профильтровать по дате. Добавить and where cdr.calldate between 'начало месяца' and 'конец месяца'.
Но вообще набор таблиц какой-то порнографичный, прямо скажем.

Edited at 2015-10-28 09:20 am (UTC)
28th-Oct-2015 10:05 am (UTC)
>Добавить and where cdr.calldate between 'начало месяца' and 'конец месяца'.

Нет, это превратит outer join cdr в inner.

SELECT bills.number, bills.value,
       sum(cdr.duration)
   FROM bills 
   LEFT JOIN phone_agreement
      ON phone_agreement.agr_id = bills.agr_id 
   LEFT JOIN cdr
      ON cdr.src_phone = phone_agreement.phone 
         AND cdr.calldate >=  'годмесяц-01-01'
         AND cdr.calldate <   'следгодмесяц-01-01'
   WHERE bills.yearmonth='месяц'
   GROUP BY bills.number


PS Но один телефон на соглашэнии -- это порнография, плюс я выдал как ты просил, но если есть два счёта на телефон за месяц -- то все звонки продублируются.
28th-Oct-2015 09:16 am (UTC)
Я бы чего сделал, может быть не самое оптимальное, но наглядное

SELECT b.number as [номер счета], sum(b.value) as [сумма], sum(x.duration) as [общее время звонков]
FROM bills b
INNER JOIN
(SELECT p.agr_id, PSEUDOCAST c.calldate as int) yearmonth, SUM(c.duration) as duration from phone_agreement p INNER JOIN cdr C ON p.phone = c.src_phone )
x on b.agr_id = x.agr_id and b.yearmonth = x.yearmonth

Где вместо PSEUDOCAST надо вставить преобразование из timestamp в int по твоим правилам. Предыдущий оратор прав, проблема именно в нем, скорее всего.
28th-Oct-2015 10:47 am (UTC)
И все счета, у которых не было звонков, улетят в /dev/null.
Плюс Вы GROUP BY забыли, поскольку агрегаты без GROUP BY неработают.
28th-Oct-2015 12:06 pm (UTC)
Ну если строго без вложенных селектов, то в лоб как то так?

select agr.phone,SUM(ISNULL(bil2.value,0)),SUM(ISNULL(cdr.duration,0))
from phone_agreement agr on agr.agr_id=bil.agr_id
left join cdr on cdr.src_phone = phone_agreement.phone
left join bills bil2 on bil2.agr_id=bil1.agr_id
where bil2.yearmonth = нужный месяц
and cdr.calldate >= 'год-месяц-01' and cdr.calldate < 'год_след.месяц-01'
group by agr.phone

ЗЫ: Тут вся проблема в параметрах обработки NULL, ЕМНИП. Чтобы каждый раз не бегать их проверять, NULL приходится учитывать отдельно

Edited at 2015-10-28 12:09 pm (UTC)
28th-Oct-2015 12:27 pm (UTC)
И где тут номер счёта?

Ну и да, такое WHERE делает cdr из OUTER JOIN фактически INNER JOIN, так что записей, у которых нет звонков оно не выберет. Смысла в bill1/bill2 я не понял, но фиг с ним, тут всё равно какие-то опечатки, например, в определении bill1.
28th-Oct-2015 12:43 pm (UTC)
Ок, вы правы, cdr подгадит. Обычно я не напрягаю мозг и делаю вложенный запрос

select agr.phone,SUM(ISNULL(bil2.value,0)),cdrcur.dur
from phone_agreement agr
left join (select src_phone,SUM(duration) as dur from cdr where calldate >= 'год-месяц-01' and calldate < 'год_след.месяц-01' group by src_phone) cdrcur on cdrcur.src_phone = phone_agreement.phone
inner join bills bil2 on bil2.agr_id=agr.agr_id
where bil2.yearmonth = нужный месяц
group by agr.phone,cdrcur.dur

Ограничение месяца счета задано по условию задачи " у которых есть счета за указанный месяц" - его можно не трогать


ЗЫ: bil1 остался от версии, в которой я думал, что длительность и сумму надо дать за ВСЕ месяца, для тех, у кого есть счет в данном.

Edited at 2015-10-28 01:04 pm (UTC)
28th-Oct-2015 01:02 pm (UTC)
Блин, старею и тупею ))) TRUE-SQL вид вот так же будет?

select agr.phone,SUM(ISNULL(bil2.value,0)),SUM(ISNULL(cdr.duration,0))
from phone_agreement agr
inner join bills bil2 on bil2.agr_id=agr.agr_id
left join cdr on cdr.src_phone = phone_agreement.phone
where bil2.yearmonth = нужный месяц
and ((cdr.calldate >= 'год-месяц-01' and cdr.calldate < 'год_след.месяц-01') or cdr.src_phone is null)
group by agr.phone

Edited at 2015-10-28 01:03 pm (UTC)
28th-Oct-2015 01:12 pm (UTC)
Ужэ почти, но номера счёта всё-таки нет, и посмотрите таки описание ISNULL, его возвращаемое значение и количество параметров.
28th-Oct-2015 12:28 pm (UTC)
Ах да, такого рода ISNULL обычно называется COALESCE. ISNULL делает несколько другое.
28th-Oct-2015 12:45 pm (UTC)
COALESCE же вроде ищет первый не NULL? А нам как раз надо NULL не потерять
28th-Oct-2015 01:12 pm (UTC)
Боюсь, мне трудно что-то обсуждать в рамках такой модэли.
28th-Oct-2015 06:54 pm (UTC)
А не может быть так, чтобы месяц cdr.timestamp не совпадал бы с месяцем bills.yearmonth? Какие-нибудь предоплаты-постоплаты, и счет в одном месяце выставляется за звонки в другом?
28th-Oct-2015 07:08 pm (UTC)
Я бы начал с тупого создания VIEW из bills, в которое выбрал бы все agr_id, у которых есть счета за указанный месяц.
А потом крутил бы с этим VIEW все остальное как с таблицей, не заморачиваясь с теми позициями, которые не релевантны.
This page was loaded Dec 15th 2017, 6:46 am GMT.