SQL Server Query для поиска минимальной даты на основе транзитивных отношений
С этим вопросом возникает небольшая головная боль и некоторые советы и рекомендации. Я не смог найти ничего действительно связанного с моей проблемой - я нашел кое-что о транзитивных замыканиях, что не совсем то, что мне нужно, поскольку мои данные могут создать цикл / цикл, который, я думаю, вызовет рекурсивный вызов бесконечного цикла,
Скажем, у меня есть две основные таблицы с данными, отображаемыми под ними. Полное раскрытие: таблица Memberships представляет собой CTE, который уже выполнил достаточную логику для вычисления значения столбца CServiceDate. Таблица Transfers - это фактическая таблица, которая не содержит ничего, кроме связей PK и FromMembershipID и ToMembershipID.
Членство
+==========+==============+================+==============+ | PersonID | MemberShipID | ЧленствоДата | CServiceDate | +==========+==============+================+==============+ | 1 | 15 | Авг-01-2016 | Авг-27-2017 | +----------+--------------+----------------+--------------+ | 1 | 16 | Март 25-2016 | Сентябрь 01-2000 | +----------+--------------+----------------+--------------+ | 1 | 17 | Дек-06-2011 | Май-15-1995 | + ---------- + -------------- + ---------------- + ------ -------- + | 1 | 18 | Янв-12-2009 | 28 февраля 1998 г. | + ---------- + -------------- + ---------------- + ------ -------- + | 1 | 19 | Апр-08-2016 | Июль-10-1994 | + ---------- + -------------- + ---------------- + ------ -------- + | 1 | 20 | Июнь-11-2010 | Ноябрь 12-1997 | +----------+--------------+----------------+--------------+
Перечислить
+=====+=====+==================+================+ | TID | PID | FromMembershipID | ToMembershipID | +=====+=====+==================+================+ | 1 | 1 | 16 | 15 | +-----+-----+------------------+----------------+ | 2 | 1 | 18 | 17 | +-----+-----+------------------+----------------+ | 3 | 1 | 19 | 17 | +-----+-----+------------------+----------------+ | 4 | 1 | 20 | 18 | +-----+-----+------------------+----------------+ | 5 | 1 | 20 | 19 | +-----+-----+------------------+----------------+
Проблема Что мне нужно из запроса для каждой строки в CTE Memberships (то есть для каждого MembershipID), я хочу вернуть MIN CServiceDate для всех связанных MembershipID. Я назову это значение MIN ECSD (ожидаемая дата обслуживания). Расчет ECSD имеет только два условия:
- Запись о членстве считается "связанной" с текущим идентификатором MembershipID, если она каким-либо образом транзитивно связана с текущим значением MembershipID через таблицу "Передачи", просматривая столбцы FromMembershipID и ToMembershipID. Например, для MembershipID 20, если мы посмотрим на таблицу Transfers, мы увидим, что MembershipIDs 20, 19, 18 и 17 все связаны транзитивностью (кроме того: через таблицу Transfers мы видим, что MembershipID 15 и 16 связаны с друг друга, но не в [20,19,18,17])
- Из списка членства, связанного с переходной экономикой, полученного непосредственно выше, единственные членства, которые могут учитываться в списке переходных отношений при расчете ECSD, - это членство с более ранней датой членства, чем текущий идентификатор участника. Например, на основе MembershipDates для данного MembershipID и таблицы " Передачи", если рассматривать MembershipID 17, MembershipID 19 не может учитываться при расчете ECSD для MembershiID 17, поскольку MembershipID 19 имеет членскую дату (апрель-08-2016), которая НЕ ранее, чем Членские идентификаторы 17 (дек-06-2011)
Ожидаемый вывод колонки ECSD
+==========+==============+================+==============+=============+ | PersonID | MemberShipID | ЧленствоДата | CServiceDate | ECSD | +==========+==============+================+==============+=============+ | 1 | 15 | Авг-01-2016 | Авг-27-2017 | Сентябрь 01-2000 | +----------+--------------+----------------+--------------+-------------+ | 1 | 16 | Март 25-2016 | Сентябрь 01-2000 | Сентябрь 01-2000 | +----------+--------------+----------------+--------------+-------------+ | 1 | 17 | Дек-06-2011 | Май-15-1995 | Май-15-1995 | + ---------- + -------------- + ---------------- + ------ -------- + ------------- + | 1 | 18 | Янв-12-2009 | 28 февраля 1998 г. | 28 февраля 1998 г. | + ---------- + -------------- + ---------------- + ------ -------- + ------------- + | 1 | 19 | Апр-08-2016 | Июль-10-1994 | Июль-10-1994 | + ---------- + -------------- + ---------------- + ------ -------- + ------------- + | 1 | 20 | Июнь-11-2010 | Ноябрь 12-1997 | Ноябрь 12-1997 | +----------+--------------+----------------+--------------+-------------+
Пожалуйста, обратите внимание:
- MembershipID может отображаться несколько раз в столбцах FromMembershipID и ToMembershipID. Эти значения столбца не должны быть уникальными.
- Таблицы, представленные выше, представляют собой простые отношения. Членство / Передача может идти всего на 1 уровень (т. Е. На 1 передачу) до N-уровня, где переводы (если просто смотреть на From /ToMembershipID) могут создавать циклический цикл. Ниже приведен пример с более глубокими отношениями передачи.
- Это может помочь заметить, что если в столбце TOMembershipID НЕ существует идентификатора MembershipID, этот членство является самым ранним (их может быть несколько). Точно так же, если MembershipID НЕ существует в столбце FromMembershipID, это конечное и самое последнее членство.
- Могут быть блоки или наборы переводов, которые связывают некоторые членства вместе для данного человека, но не все.
Пример 2
Членство
+ ========== + ============== + ================ + ====== ======== + | лицо | членство | дата членства | CServiceDate | +==========+==============+================+==============+ | 499510 | 548426 | 2014-09-29 | 2014-09-29 | +----------+--------------+----------------+--------------+ | 499510 | 548428 | 2014-01-29 | 2014-01-29 | +----------+--------------+----------------+--------------+ | 499510 | 548425 | 2012-05-28 | 2012-05-28 | +----------+--------------+----------------+--------------+ | 499510 | 548429 | 2011-11-23 | 2011-11-23 | +----------+--------------+----------------+--------------+ | 499510 | 548427 | 2008-07-03 | 2008-07-03 | +----------+--------------+----------------+--------------+ | 499510 | 548431 | 2001-05-01 | 1976-01-01 | +----------+--------------+----------------+--------------+ | 499510 | 548430 | 1998-10-08 | 1998-10-08 | +----------+--------------+----------------+--------------+
переводы
+ + ======= ======== + ================== + ============= === + | Tid | пид | FromMembershipID | ToMembershipID | + + ======= ========+==================+================+ | 10664 | 499510 | 548430 | 548431 | +-------+--------+------------------+----------------+ | 10665 | 499510 | 548431 | 548427 | +-------+--------+------------------+----------------+ | 10666 | 499510 | 548427 | 548429 | +-------+--------+------------------+----------------+ | 10667 | 499510 | 548429 | 548425 | +-------+--------+------------------+----------------+ | 10668 | 499510 | 548425 | 548428 | +-------+--------+------------------+----------------+ | 10669 | 499510 | 548428 | 548426 | +-------+--------+------------------+----------------+ | 13085 | 499510 | 548430 | 548427 | +-------+--------+------------------+----------------+ | 13086 | 499510 | 548427 | 548425 | +-------+--------+------------------+----------------+ | 13087 | 499510 | 548425 | 548426 | +-------+--------+------------------+----------------+ | 13088 | 499510 | 548431 | 548429 | +-------+--------+------------------+----------------+ | 13089 | 499510 | 548429 | 548428 | +-------+--------+------------------+----------------+
Ожидаемый результат
+ ========== + ============== + ================ + ====== ======== + ============ + | лицо | членство | дата членства | CServiceDate | ECSD | +==========+==============+================+==============+============+ | 499510 | 548426 | 2014-09-29 | 2014-09-29 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548428 | 2014-01-29 | 2014-01-29 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548425 | 2012-05-28 | 2012-05-28 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548429 | 2011-11-23 | 2011-11-23 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548427 | 2008-07-03 | 2008-07-03 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548431 | 2001-05-01 | 1976-01-01 | 1976-01-01 | +----------+--------------+----------------+--------------+------------+ | 499510 | 548430 | 1998-10-08 | 1998-10-08 | 1998-10-08 | +----------+--------------+----------------+--------------+------------+
Спасибо!
1 ответ
Решение
Вот подход, который использует CTE для составления списка переходных отношений. Один CTE определяет рекурсивные отношения от FromMembershipID к ToMembershipID. Второй CTE делает то же самое в противоположном направлении - таким образом, вы можете объединить результаты в третий CTE, в котором перечислены все переходные отношения между членствами.
Например, в первом наборе результатов 20 => 19 и 19 => 17 и наоборот 17 => 19 и 19 => 20, поэтому вы получите:
20 19
20 17
19 17
17 19
17 20
19 20
Затем вы можете использовать этот список транзитивных отношений, чтобы получить min CServiceDate по членству.
Это дает правильный вывод для обоих наборов данных выборки. Вероятно, вы достигнете предела рекурсии в 100 уровней, если у вас будет гораздо больше данных, но вы можете это настроить. Или найти лучшее решение:) но технически это работает:
;with cte_trans_relationships (frommembershipid, tomembershipid)
as
(
select frommembershipid, tomembershipid
from @transfer
union all
select c.frommembershipid, t.tomembershipid
from cte_trans_relationships c
inner join @transfer t on c.tomembershipid = t.frommembershipid
),
cte_trans_relationships2 (tomembershipid, frommembershipid)
as
(
select tomembershipid, frommembershipid
from @transfer
union all
select c.tomembershipid, t.frommembershipid
from cte_trans_relationships c
inner join @transfer t on c.frommembershipid = t.tomembershipid
),
cte_trans_all (m1, m2)
as
(
select distinct frommembershipid m1, tomembershipid m2
from cte_trans_relationships c
union
select distinct tomembershipid, frommembershipid
from cte_trans_relationships2 c2
)
select m.personid, m.membershipid, m.membershipdate, m.cservicedate,
case when sq.cservicedate < cservicedate1 and sq.cservicedate < cservicedate2 then sq.cservicedate
when cservicedate1 < sq.cservicedate and cservicedate1 < cservicedate2 then cservicedate1
else cservicedate2 end as ECSD
from @memberships m
inner join
(
select m.personid, m.membershipid, isnull(m.cservicedate, dateadd(year, 100, getdate())) as cservicedate,
isnull(min(m1.cservicedate), dateadd(year, 100, getdate())) as cservicedate1,
isnull(min(m2.cservicedate), dateadd(year, 100, getdate())) as cservicedate2
from @memberships m
left join cte_trans_all sq_trans1 on m.membershipid = sq_trans1.m1
left join @memberships m1 on sq_trans1.m2 = m1.membershipid and m1.membershipdate < m.membershipdate
left join cte_trans_all sq_trans2 on m.membershipid = sq_trans2.m1
left join @memberships m2 on sq_trans2.m2 = m2.membershipid and m2.membershipdate < m.membershipdate
group by m.personid, m.membershipid, m.cservicedate
)sq on m.personid = sq.personid and m.membershipid = sq.membershipid
Вот примеры операторов DDL/DML для тестирования:
declare @memberships table (personid int, membershipid int, membershipdate datetime, cservicedate datetime)
insert into @memberships values (1, 15, '8/1/2016', '8/27/2017')
insert into @memberships values (1, 16, '3/25/2016', '9/1/2000')
insert into @memberships values (1, 17, '12/6/2011', '5/15/1995')
insert into @memberships values (1, 18, '1/12/2009', '2/28/1998')
insert into @memberships values (1, 19, '4/8/2016', '7/10/1994')
insert into @memberships values (1, 20, '6/11/2010', '11/12/1997')
--insert into @memberships values (499510, 548426, '9/29/2014', '9/29/2014')
--insert into @memberships values (499510, 548428, '1/29/2014', '1/29/2014')
--insert into @memberships values (499510, 548425, '5/28/2012', '5/28/2012')
--insert into @memberships values (499510, 548429, '11/23/2011', '11/23/2011')
--insert into @memberships values (499510, 548427, '7/3/2008', '7/3/2008')
--insert into @memberships values (499510, 548431, '5/1/2001', '1/1/1976')
--insert into @memberships values (499510, 548430, '10/8/1998', '10/8/1998')
declare @transfer table (tid int, pid int, frommembershipid int, tomembershipid int)
insert into @transfer values (1, 1, 16, 15)
insert into @transfer values (2, 1, 18, 17)
insert into @transfer values (3, 1, 19, 17)
insert into @transfer values (4, 1, 20, 18)
insert into @transfer values (5, 1, 20, 19)
--insert into @transfer values (10664, 499510, 548430, 548431)
--insert into @transfer values (10665, 499510, 548431, 548427)
--insert into @transfer values (10666, 499510, 548427, 548429)
--insert into @transfer values (10667, 499510, 548429, 548425)
--insert into @transfer values (10668, 499510, 548425, 548428)
--insert into @transfer values (10669, 499510, 548428, 548426)
--insert into @transfer values (13085, 499510, 548430, 548427)
--insert into @transfer values (13086, 499510, 548427, 548425)
--insert into @transfer values (13087, 499510, 548425, 548426)
--insert into @transfer values (13088, 499510, 548431, 548429)
--insert into @transfer values (13089, 499510, 548429, 548428)
Вот 2 набора результатов:
personid membershipid membershipdate cservicedate ECSD
1 15 2016-08-01 2017-08-27 2000-09-01
1 16 2016-03-25 2000-09-01 2000-09-01
1 17 2011-12-06 1995-05-15 1995-05-15
1 18 2009-01-12 1998-02-28 1998-02-28
1 19 2016-04-08 1994-07-10 1994-07-10
1 20 2010-06-11 1997-11-12 1997-11-12
personid membershipid membershipdate cservicedate ECSD
499510 548425 2012-05-28 2012-05-28 1976-01-01
499510 548426 2014-09-29 2014-09-29 1976-01-01
499510 548427 2008-07-03 2008-07-03 1976-01-01
499510 548428 2014-01-29 2014-01-29 1976-01-01
499510 548429 2011-11-23 2011-11-23 1976-01-01
499510 548430 1998-10-08 1998-10-08 1998-10-08
499510 548431 2001-05-01 1976-01-01 1976-01-01