Как определить, зарегистрировано ли лицо в течение конкретных дат в SQL

У меня есть следующая таблица:

+----------+------------+------------+
| MemberID | StartDate | EndDate |
+----------+------------+------------+
| 10 | 2015-01-01 | 9999-12-31 |
| 10 | 2015-06-15 | 9999-12-31 |
| 20 | 2015-01-01 | 2015-04-06 |
| 20 | 2015-04-07 | 9999-12-31 |
| 30 | 2015-05-06 | 9999-12-31 |
| 40 | 2015-01-01 | 2015-03-01 |
| 50 | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+

Основываясь на этой таблице, я хочу найти Членов, которые НЕПРЕРЫВНО относятся к 1 января 2015 года - 31 августа 2015 года.

Бизнес-правила:

  • Даже если у члена есть разрыв в один день в его членстве, он дисквалифицирован.
  • Тот же идентификатор участника может иметь разные записи в таблице (пример MemberID 10)
  • В демонстрационной таблице члены 10, 20, 50 должны быть включены в таблицу результатов, поскольку они имеют право на протяжении всего периода.
3 ответа

Подход здесь:

1. Найдите непрерывные даты для члена, т.е. если EndDate является 2015-04-06 И StartDate следующей строки 2015-04-07, объедините их в одну строку. Это достигается следующим утверждением:

SELECT 
 MemberID, MIN(StartDate) StartDate, MAX(EndDate) EndDate
 FROM
 (SELECT 
 dt.*,
 IF(@previd = dt.memberid
 && DATE_ADD(@prevdate, INTERVAL 1 DAY) <= dt.StartDate, @groupid, @groupid:=@groupid + 1) GroupId,
 @previd:=dt.memberid,
 @prevdate:=dt.EndDate
 FROM
 data_table dt, (SELECT @previd:=NULL, @prevdate:=NULL, @groupid:=0) a
 ORDER BY MemberID , StartDate) memberdategrouptable
 GROUP BY GroupId

Вы можете рассматривать как итерацию результатов, упорядоченных по MemberID, StartDate и if(found_different_member || (member_same_as_previous_row && previous_end_date_does_not_match_to_current_start_date) assign_new_group_id.

Это приведет к следующим результатам:

Первый результат

+----------+------------+------------+---------+----------------------+-----------------------+
 | MemberID | StartDate | EndDate | GroupId | @previd:=dt.memberid | @prevdate:=dt.EndDate |
 +----------+------------+------------+---------+----------------------+-----------------------+
 | 10 | 2015-01-01 | 9999-12-31 | 1 | 10 | 9999-12-31 |
 | 10 | 2015-06-15 | 9999-12-31 | 2 | 10 | 9999-12-31 |
 | 20 | 2015-01-01 | 2015-04-06 | 3 | 20 | 2015-04-06 |
 | 20 | 2015-04-07 | 9999-12-31 | 3 | 20 | 9999-12-31 |
 | 30 | 2015-05-06 | 9999-12-31 | 4 | 30 | 9999-12-31 |
 | 40 | 2015-01-01 | 2015-03-01 | 5 | 40 | 2015-03-01 |
 | 50 | 2015-01-01 | 2015-08-31 | 6 | 50 | 2015-08-31 |
 +----------+------------+------------+---------+----------------------+-----------------------+

Группировать в непрерывную дату

+----------+------------+------------+
| MemberID | StartDate | EndDate |
+----------+------------+------------+
| 10 | 2015-01-01 | 9999-12-31 |
| 10 | 2015-06-15 | 9999-12-31 |
| 20 | 2015-01-01 | 9999-12-31 |
| 30 | 2015-05-06 | 9999-12-31 |
| 40 | 2015-01-01 | 2015-03-01 |
| 50 | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+

2. Список элементов, соответствующих критериям даты

SELECT 
 *
FROM
 (SELECT 
 MemberID, MIN(StartDate) StartDate, MAX(EndDate) EndDate
 FROM
 (SELECT 
 dt.*,
 IF(@previd = dt.memberid
 && DATE_ADD(@prevdate, INTERVAL 1 DAY) <= dt.StartDate, @groupid, @groupid:=@groupid + 1) GroupId,
 @previd:=dt.memberid,
 @prevdate:=dt.EndDate
 FROM
 data_table dt, (SELECT @previd:=NULL, @prevdate:=NULL, @groupid:=0) a
 ORDER BY MemberID , StartDate) memberdategrouptable
 GROUP BY GroupId) memberdaterange 
WHERE
 StartDate <= '2015-01-01'
 AND EndDate >= '2015-08-31'

Reuslt

+----------+------------+------------+
| MemberID | StartDate | EndDate |
+----------+------------+------------+
| 10 | 2015-01-01 | 9999-12-31 |
| 20 | 2015-01-01 | 9999-12-31 |
| 50 | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+


Это проблема пробелов и островов, которая довольно болезненна в MySQL. Идея состоит в том, чтобы назначить идентификатор группы для каждой строки. Идентификатор группы - это совокупная сумма начала периода. И период начинается, когда нет предыдущей записи.

Итак, следующее следует присвоить группе, считая, что StartDate всегда на следующий день после предыдущего EndDate (это условие может быть ослаблено, но несколько упрощает кодирование):

select t.*,
 (@grp := if(@m = MemberId and @e = date_sub(StartDate, interval 1 day), @grp,
 if(@m := MemberId, if(@e := EndDate, @grp + 1, @grp + 1), @grp + 1
 )
 )
 ) as grp
from (select t.*,
 (select 1
 from table t2
 where t2.MemberId = t.MemberId and
 t2.EndDate = date_sub(t.StartDate, interval 1 day)
 ) as IsPeriodStart
 from table t
 ) t cross join
 (select @m := 0, @e := 0, @grp := 0) params
order by MemberId, StartDate;

Тогда ответ на ваш вопрос - это просто агрегация с предложением having:

select MemberId
from (select t.*,
 (@grp := if(@m = MemberId and @e = date_sub(StartDate, interval 1 day), @grp,
 if(@m := MemberId, if(@e := EndDate, @grp + 1, @grp + 1), @grp + 1
 )
 )
 ) as grp
 from (select t.*,
 (select 1
 from table t2
 where t2.MemberId = t.MemberId and
 t2.EndDate = date_sub(t.StartDate, interval 1 day)
 ) as IsPeriodStart
 from table t
 ) t cross join
 (select @m := 0, @e := 0, @grp := 0) params
 order by MemberId, StartDate
 ) t
group by MemberId, grp
having min(StartDate) <= '2015-01-01' and max(EndDate) >- '2015-08-31';

EDIT:

Я понимаю, что это можно значительно упростить. Вам не нужны переменные. Кто-то является непрерывным в течение периода, если флаг IsPeriodStart никогда не был истинным в течение этого времени:

select MemberId
from (select t.*,
 (select 1
 from table t2
 where t2.MemberId = t.MemberId and
 t2.EndDate = date_sub(t.StartDate, interval 1 day)
 ) as IsPeriodStart
 from table t
 where EndDate >= '2015-01-01' and StartDate <= '2015-08-31'
 ) t 
group by MemberId
having max(case when IsPeriodStart then StartDate end) = min(StartDate) and
 min(StartDate) <= '2015-01-01' and
 max(EndDate) >= '2015-08-31';

Три условия в предложении having выполняют эту работу. Последние два должны быть очевидными - период покрывается. Первый говорит, что единственным началом периода в диапазоне является первая запись; следовательно, нет пробелов.


Другой способ: если у вас есть таблица Member, добавьте столбец Eligible.

  • Сначала установите все из них (допустимо) на true
  • Затем установите выбранные значения в false с чем-то вроде этого

.

update Member set Eligible = false
 where Member.MemberId = 
 (select A.MemberId from tbl A
 left outer join tbl B on A.MemberId = B.MemberId
 where A.EndDate <> '9999-12-31'
 and A.EndDate +1 < B.StartDate)

Обратите внимание на его строгий mysql, поскольку я не знаю типы данных ваших полей.

licensed under cc by-sa 3.0 with attribution.