isset89
@isset89
Врёшь, тебе нравится

Оптимизация запроса? Как правильнее? или какие еще будут идеи?

Добрый день, подскажите...
Есть таблица, в которую каждый месяц сливаются данные: 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;

id period_name
1 201001
2 201002
3 201003
4 201004
... ..........
75 201601

############################################
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;

contract subs period nach
6871 245433 201201 0.0000
6871 245434 201104 17460.0000
6871 245434 201105 18600.0000
6871 245436 201110 18600.0000
6871 245436 201111 0.0000
8491 049909 201511 478.0000
8491 049909 201512 1547.0000
8491 049913 201501 0.0000
8491 049913 201502 0.0000
#################################################

Т.е. сгруппировала все договора с их абонами и сумму начислений по абонам за каждый период. 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;

contract subs period
6871 245433 201001
6871 245433 201002
....... .......... ...........
6871 245433 201601
6871 245434 201001
6871 245434 201002
....... ............ ..........
6871 245434 201601

#########################################
Т.е вывела все возможные договора , абоненты и периоды на них.
Далее все это дело соединяем.
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 сек

contract subs rash
6871 245433 ;;;;0.0000;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
6871 245434 ;;;;;;;;;;;;;;;17460.0000;18600.0000;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

хотелось бы, как-то избавиться от таблиц и сделать все одним запросом.
Вариант 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 таблиц?.
  • Вопрос задан
  • 190 просмотров
Пригласить эксперта
Ответы на вопрос 1
@miksir
IT
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 неверно.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы