Форум пользователей MySQL

vivalaakam · 30.08.2011 14:41:23

CREATE TABLE  `table` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`date` DATE NOT NULL ,

`user` INT NOT NULL ,

`cash` FLOAT NOT NULL ,

`sum` FLOAT NOT NULL ,

`slip` FLOAT NOT NULL ,

`type` INT NOT NULL ,

`st` INT NOT NULL

) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci

таблица table_archive аналогична table

В таблице table хранятся записи пользователей за последние 3 месяца, в начале каждого месяца, предидущий закрывается и делается запись INSERT INTO table SET user=7 , date="2011-08-01" , slip=1234, type=1 , st=3 на основе предидущих записей, так же перебираются остальные месяца в таблице на предмет добавления новых строк и переносятся записи старше 3х месяцев в таблицу table_archive

Получаем номера месяцев в таблице по данному пользователю

SELECT DISTINCT(MONTH( DATE )) as month , LAST_DAY( DATE ) + INTERVAL 1  DAY AS next  FROM table WHERE user =7 AND month(date) <= month(now())-1 ORDER BY month ASC

Перебираем их, удаляем старые отметки о закрытии и заносим их заново:

DELETE FROM t[syntax=mysql]able WHERE date=2011-09-01 AND type=1 AND user=7

INSERT INTO table SET user=7 , date="2011-08-01" , slip=(SELECT  ROUND( IFNULL( SUM( slip ) , 0 ) - IFNULL( SUM( sum ) , 0 ) + IFNULL( SUM( cash ) , 0 ) , 2 ) AS result FROM table WHERE user =7 AND MONTH( DATE ) =7 AND TYPE IN ( 2, 3, 4, 1 ) GROUP BY MONTH( DATE ) ), type=1 , st=3

немного чистим таблицу

UPDATE table SET st=3 WHERE st=1 AND month(date) < month(now()) - 1

 

переносим старые записи в архив

INSERT INTO table__archive SELECT * FROM table WHERE user=7 AND month(date) < month(now()) - 2 AND st=3 AND user=7

DELETE FROM table WHERE id = any( SELECT id FROM table__archive WHERE MONTH( date ) < MONTH( NOW( ) ) -2 AND st=3 AND user=7

Как можно этот процесс сделать несоклько проще?

paulus · 31.08.2011 10:11:39

Я правильно понимаю, что Вы храните данные за последние три месяца в
двух таблицах? Почему не переносить в архив тогда, когда информация уже
не будет меняться?

vivalaakam · 31.08.2011 17:51:07

В первой таблице хранятся данные за последние три месяца, а во вторую таблицу переносится все, что старше 3х месяцев и данные во второй таблице уже не меняются

Отредактированно vivalaakam (31.08.2011 17:51:35)

evgeny · 01.09.2011 00:43:55

Не понятно приём тут все перечисленные запросы ... По моему по теме это только последние 2

INSERT INTO table__archive SELECT * FROM table WHERE user=7 AND month(date) < month(now()) - 2 AND st=3 AND user=7

DELETE FROM table WHERE id = any( SELECT id FROM table__archive WHERE MONTH( date ) < MONTH( NOW( ) ) -2 AND st=3 AND user=7

Второй запрос можно укоротить на

DELETE FROM table WHERE user=7 AND month(date) < month(now()) - 2 AND st=3 AND user=7

paulus · 01.09.2011 23:31:48

Кажется, задача «перенести всё, что старше трех месяцев» решается куда более
простыми запросами:

Код:

SET @ts = NOW() - INTERVAL 3 MONTH;
INSERT INTO table__archive SELECT * FROM table WHERE `date` < @ts;
DELETE FROM table WHERE `date` < @ts;

А вообще обычно просто каждый месяц делают новую табличку с новым именем

evgeny · 02.09.2011 18:12:45

paulus, Интересно а если использовать Partitioning с делением по месяцам.
Выборка из одной секции партиционирования может быть менее оптимально чем выборка из отдельной таблицы ?

paulus · 03.09.2011 00:45:47

Добавление новой таблицы — простая операция, которая никак не затрагивает
работу других запросов. Добавление же нового раздела, к сожалению, при этом
блокирует доступ ко всем остальным разделам таблицы (хотя и ненадолго, если
не нужно перебрасывать данные в другой раздел). Но если можно себе позво-
лить такой простой, то да, тоже вполне себе решение

Форум пользователей MySQL

#1 30.08.2011 14:41:23

Упростить запрос

#2 31.08.2011 10:11:39

Re: Упростить запрос

#3 31.08.2011 17:51:07

Re: Упростить запрос

#4 01.09.2011 00:43:55

Re: Упростить запрос

#5 01.09.2011 23:31:48

Re: Упростить запрос

Код:

#6 02.09.2011 18:12:45

Re: Упростить запрос

#7 03.09.2011 00:45:47

Re: Упростить запрос

Board footer