Я предполагаю, что Пользователь мог присоединиться к группе задолго до расчетного периода и не мог изменить статус в течение расчетного периода. Это требует, чтобы вся ваша таблица была просканирована, чтобы построить таблицу членства, которая выглядит следующим образом:
create table membership (
UserId int not null,
GroupId int not null,
start datetime not null,
end datetime not null,
count int not null,
primary key (UserId, GroupId, end )
);
Как только это будет правильно заполнено, ответ, который вы хотите, легко получить:
set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);
# sum( datediff( e, s ) + 1 ) -- +1 needed to include last day in billing
select UserId,
GroupId,
sum(datediff( if(end > @em, @em, end),
if(start<@sm, @sm, start) ) + 1 ) as n
from membership
where start <= @em and end >= @sm
group by UserId, GroupId
having n >= 15;
Сканирование нужно выполнять курсором (что будет не быстро). Нам нужно отсортировать вашу входную таблицу по ActionDate и Action, чтобы события «присоединения» появлялись перед событиями «выход». Поле подсчета предназначено для того, чтобы помочь справиться с патологическими случаями, когда членство прекращается в один день, затем возобновляется в тот же день, снова заканчивается в тот же день, снова начинается в тот же день и т. д. В этих случаях , мы увеличиваем счетчик для каждого начального события и уменьшаем его для каждого конечного события. Мы закроем членство только тогда, когда конечное событие обнулит счет. В конце заполнения таблицы членства вы можете запросить значение count: закрытые членства должны иметь count = 0, открытые членства (еще не закрытые) должны иметь count = 1. Любые записи со счетчиком за пределами 0 и 1 должны быть тщательно изучены. - это указывало бы на ошибку где-то.
Курсорный запрос:
select UserID as _UserID, GroupID as _GroupID, Date(ActionDate) adate, Action from tbl
order by UserId, GroupId, Date(ActionDate), Action desc;
«Описание действия» должно разорвать связи, чтобы начальные события появлялись перед конечными событиями, если кто-то присоединится к группе и покинет ее в один и тот же день. ActionDate необходимо преобразовать из даты и времени в дату, потому что нас интересуют единицы дней.
Действия внутри курсора будут следующими:
if (Action = 1) then
insert into membership
set start=ActionDate, end='2037-12-31', UserId=_UserId, GroupId=_GroupId, count=1
on duplicate key update set count = count + 1;
elsif (Action == -1)
update membership
set end= if( count=1, Actiondate, end),
count = count - 1
where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
end if
Я не дал вам точный синтаксис требуемого определения курсора (вы можете найти его в руководстве по MySQL), потому что полный код скроет идею. На самом деле, может быть быстрее выполнить логику курсора в вашем приложении - возможно, даже создать детали членства в приложении.
РЕДАКТИРОВАТЬ: Вот фактический код:
create table tbl (
UserId int not null,
GroupId int not null,
Action int not null,
ActionDate datetime not null
);
create table membership (
UserId int not null,
GroupId int not null,
start datetime not null,
end datetime not null,
count int not null,
primary key (UserId, GroupId, end )
);
drop procedure if exists popbill;
delimiter //
CREATE PROCEDURE popbill()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE _UserId, _GroupId, _Action int;
DECLARE _adate date;
DECLARE cur1 CURSOR FOR
select UserID, GroupID, Date(ActionDate) adate, Action
from tbl order by UserId, GroupId, Date(ActionDate), Action desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
truncate table membership;
OPEN cur1;
REPEAT
FETCH cur1 INTO _UserId, _GroupId, _adate, _Action;
IF NOT done THEN
IF _Action = 1 THEN
INSERT INTO membership
set start=_adate, end='2037-12-31',
UserId=_UserId, GroupId=_GroupId, count=1
on duplicate key update count = count + 1;
ELSE
update membership
set end= if( count=1, _adate, end),
count = count - 1
where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END
//
delimiter ;
Вот некоторые тестовые данные:
insert into tbl values (1, 10, 1, '2009-01-01' );
insert into tbl values (1, 10, -1, '2009-01-02' );
insert into tbl values (1, 10, 1, '2009-02-03' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-05' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-06' );
insert into tbl values (1, 10, -1, '2009-02-06' );
insert into tbl values (2, 10, 1, '2009-02-20' );
insert into tbl values (2, 10, -1, '2009-05-30');
insert into tbl values (3, 10, 1, '2009-01-01' );
insert into tbl values (4, 10, 1, '2009-01-31' );
insert into tbl values (4, 10, -1, '2009-05-31' );
Вот код, который запускается, и результаты:
call popbill;
select * from membership;
+--------+---------+---------------------+---------------------+-------+
| UserId | GroupId | start | end | count |
+--------+---------+---------------------+---------------------+-------+
| 1 | 10 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 0 |
| 1 | 10 | 2009-02-03 00:00:00 | 2009-02-05 00:00:00 | 0 |
| 1 | 10 | 2009-02-06 00:00:00 | 2009-02-06 00:00:00 | 0 |
| 2 | 10 | 2009-02-20 00:00:00 | 2009-05-30 00:00:00 | 0 |
| 3 | 10 | 2009-01-01 00:00:00 | 2037-12-31 00:00:00 | 1 |
| 4 | 10 | 2009-01-31 00:00:00 | 2009-05-31 00:00:00 | 0 |
+--------+---------+---------------------+---------------------+-------+
6 rows in set (0.00 sec)
Затем проверьте, сколько расчетных дней появилось в феврале 09:
set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);
select UserId,
GroupId,
sum(datediff( if(end > @em, @em, end),
if(start<@sm, @sm, start) ) + 1 ) as n
from membership
where start <= @em and end >= @sm
group by UserId, GroupId;
+--------+---------+------+
| UserId | GroupId | n |
+--------+---------+------+
| 1 | 10 | 4 |
| 2 | 10 | 9 |
| 3 | 10 | 28 |
| 4 | 10 | 28 |
+--------+---------+------+
4 rows in set (0.00 sec)
Это можно сделать, чтобы просто сканировать таблицу на наличие изменений с момента последнего запуска:
- удалите оператор «усекать членство».
- создать контрольную таблицу, содержащую последнюю обработанную отметку времени
- рассчитать последнюю метку времени, которую вы хотите включить в этот запуск (я бы предположил, что max(ActionDate) не подходит, потому что могут быть некоторые неупорядоченные поступления с более ранними метками времени. Хороший выбор — «00:00:00» сегодня утром или "00:00:00" первого дня месяца).
- измените запрос курсора, чтобы он включал только записи таблицы между датой последнего запуска (из контрольной таблицы) и вычисленной последней датой.
- наконец, обновите контрольную таблицу с расчетной последней датой.
Если вы это сделаете, хорошей идеей также будет передать флаг, который позволит вам перестроить с нуля, т.е. сбросить контрольную таблицу до начала времени и усечь таблицу членства перед запуском обычной процедуры.
person
Martin
schedule
26.02.2010
groupuserlogгде action = 1 и actiondate ›= '2010-02-01' и actiondate ‹ date_add('2010-02-01',INTERVAL 15 DAY) и userid не в (выберите userid из groupuserlog где action = -1 и actiondate ‹ '2010-03-01' и actiondate › date_add('2010-02-01', ИНТЕРВАЛ 15 ДНЕЙ)) - person Gotts schedule 26.02.2010