В ходе доработки телефонного тарификатора столкнулся со следующей задачей.
У нас есть хранящиеся в MYSQL логи с телефонной станции, которые обсчитываются отдельным скриптом и хранят стоимость всех совершенных звонков для каждого телефона. Их можно посмотреть через специальную страничку, написанную на php, которая сводит все данные по месяцу в одну таблицу.
Сейчас требуется добавить возможность привязывать каждый телефонный номер (
in_number) к определенному департаменту, причем со временем номера могут переходить из одного департамента в другой (
не обязательно в начале месяца). Соответственно статистика в новой версии должна выводиться уже в таблице по департаментам в таком порядке:
- Название департамента
- Сумма затрат на каждый номер в этом месяце
- Сумма затрат на департамент
Собственно говоря, интересует
как наиболее оптимально написать запрос по выборке данных для этой таблицы? возможно ли это просчитывать в реалтайме или же надо будет подготавливать данные для неё заранее, напр. каждый день ночью?
Упрощенный вариант базы можете посмотреть ниже:
/* Таблица с внутренними номерами */
CREATE TABLE IF NOT EXISTS `in_numbers` (
`in_number` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`in_number`) ,
UNIQUE INDEX `in_number_UNIQUE` (`in_number` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица со статистикой звонков */
CREATE TABLE IF NOT EXISTS `outgoing` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`d_time` TIMESTAMP NULL DEFAULT NULL ,
`in_number` INT(3) UNSIGNED NOT NULL ,
`t_call` TIME NULL DEFAULT NULL ,
`c_number` BIGINT(20) UNSIGNED NULL DEFAULT NULL ,
`code` BIGINT(20) UNSIGNED NULL DEFAULT NULL ,
`c_price` FLOAT(11,3) NULL DEFAULT NULL
PRIMARY KEY (`id`) ,
CONSTRAINT `outgoing_in_number`
FOREIGN KEY (`in_number` )
REFERENCES `in_numbers` (`in_number` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица существующих департаментов */
CREATE TABLE IF NOT EXISTS `departments` (
`department_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`department` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`department_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
/* Таблица истории привязок номеров к департаментам */
CREATE TABLE IF NOT EXISTS `assigments` (
`assigment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`in_number` INT(3) UNSIGNED NOT NULL ,
`department_id` INT(10) UNSIGNED NOT NULL ,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`assigment_id`) ,
INDEX `assigment_dep_id` (`department_id` ASC) ,
INDEX `assigment_number` (`in_number` ASC) ,
CONSTRAINT `assigment_dep_id`
FOREIGN KEY (`department_id` )
REFERENCES `departments` (`department_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `assigment_number`
FOREIGN KEY (`in_number` )
REFERENCES `in_numbers` (`in_number` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
* This source code was highlighted with Source Code Highlighter.
