Оптимизация запроса? Как правильнее? или какие еще будут идеи?
Добрый день, подскажите...
Есть таблица, в которую каждый месяц сливаются данные: 1.Договор 2.Абонент 3.Период 4.Деньги --> Table allVp
(1582293 записи)и таблица периодов(71 запись). Данные за 5 лет, каждый новый месяц , добавляются новые.
Нужно сделать запрос, который будет вытягивать все договора. На каждом договоре все его абоненты и по каждому абоненту деньги за каждый период. И если у какого-нибудь абонента за какой-нибудь период нет начислений вывести NULL или 0 не важно.
Какие камни: на одном договоре, может быть около 200 абонентов. На каждом из этих абонентов может быть несколько начислений за один период.
Несколько вариантов:
Таблица периодов ->billing_period
########################################
CREATE TABLE `billing_period` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`period_name` mediumint(9) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `period_name_idx_idx` (`period_name`),
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;
############################################
1. Вариант. Я сделала еще одну таблицу, селектом из основной
CREATE TABLE `tm_summ_period` SELECT contract,subs,period,sum(nach) from `allVp` where contract!='' group by contract,subs,period
#######################################
CREATE TABLE `tm_summ_period` (
`contract` varchar(50) NOT NULL,
`subs` varchar(60) NOT NULL,
`period` mediumint(9) NOT NULL,
`nach` decimal(18,4) NOT NULL,
KEY `allInd_idx` (`contract`,`subs`,`period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Т.е. сгруппировала все договора с их абонами и сумму начислений по абонам за каждый период. tm_summ_period (1436609 записей)
теперь мне надо показать за какие периоды нет начислений
создала еще одну таблицу из селекта:
CREATE TABLE `tm_allVp` SELECT distinct
a.contract,
a.subs,
b.period_name
from `allVp` a,`billing_period` b where a.contract !='';
#########################################
CREATE TABLE `tm_allVp` (
`contract` varchar(50) NOT NULL,
`subs` varchar(60) NOT NULL,
`period_name` mediumint(9) NOT NULL,
KEY `arr_idx` (`contract`,`subs`,`period_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#########################################
Т.е вывела все возможные договора , абоненты и периоды на них.
Далее все это дело соединяем.
select b.contract,b.subs AS subs,group_concat(IFNULL(d.nach,"")order by b.period_name SEPARATOR ";" ) AS rash
from `tm_allVp` b
left join `tm_summ_period` d ON (d.contract = b.contract AND d.subs = b.subs AND d.period = b.period_name )
group by b.contract,b.subs
limit 20000000
0.093 сек
хотелось бы, как-то избавиться от таблиц и сделать все одним запросом.
Вариант 2. Менее удачный.
Прям делать запрос к основной таблице:
select g.contract,g.subs AS subs,group_concat(IFNULL(total,"") order by g.period_name DESC SEPARATOR ";" ) AS rash
from (SELECT contract,subs,period, sum(nach) AS total FROM `allVp` where contract !='' group by contract,subs,period) d
right join (select distinct
a.contract,
a.subs,
b.period
from `allVp` a,`billing_period` b where a.contract !='') g ON (d.contract = g.contract AND d.subs = g.subs AND d.period = g.period_name )
group by g.contract,g.subs
Ждем, ждем... Запрос ошибку не выдает, но IDL падает.
Так вот, как можно бы все таки сделать, чтобы не прибегать к созданию 2 таблиц?.
SELECT l.contract,l.subs,p.period_name,SUM(nach)
FROM (SELECT contract,subs FROM allVp GROUP BY contract,subs) l
CROSS JOIN billing_period p
LEFT JOIN allVp v ON l.subs=v.subs AND l.contract=v.contract AND p.id=v.period
GROUP BY l.contr,l.subs,p.period_name;
Здесь есть допущение, что у одного subs только один контракт, и что subs уникален. И вообще-то подзапрос для получения списка sub нужно менять на таблицу, где эти subs лежат, если мы говорим о базе в 3НФ. Выбирать все из allVp неверно.
нет, один subs может быть на разных контрактах. Я первый вариант описывала, что делаю отдельную таблицу из селекта, которая состоит из контракта, абонента и всех периодов
Индексы есть? Как минимум на v.period, p.period_name, а можно и на contract и subs еще. После индексов попробуйте, если долго - давайте explain посмотрим.
miksir: мне немного непонятно, зачем здесь FROM (SELECT contract,subs FROM allVp GROUP BY contract,subs) l .... В своем примере я группировку делала, так как использую group_concat, для того, чтобы все начисления по абонентам выводились через ";"
Вам нужно по всем парам контракт+юзер показать платежи за весь список периодов (вне зависимости - платили они в этом периоде или нет), так?
Так как у вас нет почему-то отдельной таблицы с контрактами/юзерами, то приходится получить их список из allVp - это и делает вложенный селект.
Потом декартово произведение на периоды, что бы получить все периоды по каждому юзеру. Ну а потом по каждой строчке такой - поиск записей и суммирование.
Насчет индекса - попробуйте все же убить составной индекс и построить три отдельных индекса.
miksir: да,по поводу начисления все верно, отдельной таблицы нет, т.к все данные заливаются из excel файла, сделать отдельную таблицу просто нереально.. надо вложить SUM(nach) AS money в подзапрос, так как надо сделать GROUP_CONCAT(money), по поводу индексов, отдельно индексы я делала, тогда даже обычный GROUP BY отрабатывает криво
miksir:
SELECT l.contract,l.subs,group_concat(total)
FROM (SELECT contract,subs,period,sum(nach) AS total FROM allVp GROUP BY contract,subs,period) l
CROSS JOIN billing_period p
что-то типо того, но это сводиться к моему запросу, который работает 10 лет
Простой