SQL Server Query для поиска минимальной даты в транзитивных отношениях с возможными циклическими циклами

Question

SQL Server Query для поиска минимальной даты в транзитивных отношениях с возможными циклическими циклами

С этим вопросом возникает небольшая головная боль и некоторые советы и рекомендации. Я не смог найти ничего действительно связанного с моей проблемой - я нашел кое-что о транзитивных замыканиях, что не совсем то, что мне нужно, поскольку мои данные могут создать цикл / цикл, который, я думаю, вызовет рекурсивный вызов бесконечного цикла,

Скажем, у меня есть две основные таблицы с данными, отображаемыми под ними. Полное раскрытие: членство в таблице 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 | FromMembershipID | ToMembershipID |
+=====+==================+================+
|   1 |               16 |             15 |
+-----+------------------+----------------+
|   2 |               18 |             17 |
+-----+------------------+----------------+
|   3 |               19 |             17 |
+-----+------------------+----------------+
|   4 |               20 |             18 |
+-----+------------------+----------------+
|   5 |               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. Эти значения столбца не должны быть уникальными.
(добавить после редактирования) Я хочу применить это решение к более крупным наборам данных, где объемы передачи более 2-х уровней. Пожалуйста, смотрите ниже пример:

Пример 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 |
+----------+--------------+----------------+--------------+------------+

Обратите внимание: я добавил столбец pid в таблицу переводов. Забыл включить это в первый раз.

Спасибо!

1

sql sql-server min transitivity

Источник

user2494730 07 авг '17 в 20:51

1 ответ

Решение

Другие вопросы по тегам sql sql-server min transitivity

user6167855 07 авг '17 в 21:33 2017-08-07 21:33 · Accepted Answer · 2017-08-07 21:33

Я думаю, что это точный результат... хотя он отличался от ожидаемого. Смотрите мои комментарии выше.

Поскольку первая таблица представляет собой CTE, вы можете оставить его в CTE и продолжить работу с этим CTE или сохранить эти результаты во временной таблице, что может быть полезным, поскольку мы будем запрашивать ее несколько раз.

declare @Memberships table (PersonID int, MemberShipID int, MembershipDate varchar(16), CServiceDate varchar(16))
insert into @Memberships
values
(1,15,'Aug-01-2016','Aug-27-2017'),
(1,16,'Mar-25-2016','Sep-01-2000'),
(1,17,'Dec-06-2011','May-15-1995'),
(1,18,'Jan-12-2009','Feb-28-1998'),
(1,19,'Apr-08-2016','Jul-10-1994'),
(1,20,'Jun-11-2010','Nov-12-1997') 

declare @Transfer table (TID int, FromMembershipID int, ToMembershipID int)
insert into @Transfer
values
(1,16,15),
(2,18,17),
(3,19,17),
(4,20,18),
(5,20,19)

;with cte as(
select
    m.PersonID
    ,m.MemberShipID
    ,m.MembershipDate
    ,m.CServiceDate
    ,case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end RelatedMemberShips
from
    @Memberships m
    left join
    @Transfer t on
    t.FromMembershipID = m.MemberShipID or t.ToMembershipID = m.MemberShipID)

select distinct
    cte.PersonID
    ,cte.MemberShipID
    ,cte.MembershipDate
    ,cte.CServiceDate
    --,cte.RelatedMemberShips
    --,m.MembershipDate
    --,m.CServiceDate
    ,case when min(convert(date, replace(m.CServiceDate, '-', ' '), 0)) over (partition by cte.MemberShipID) < convert(date, replace(cte.CServiceDate, '-', ' '), 0) then min(convert(date, replace(m.CServiceDate, '-', ' '), 0)) over (partition by cte.MemberShipID)  else convert(date, replace(cte.CServiceDate, '-', ' '), 0) end
from 
    cte
    left join 
        @Memberships m on m.MemberShipID = cte.RelatedMemberShips
        and convert(date, replace(m.MembershipDate, '-', ' '), 0) <= convert(date, replace(cte.MembershipDate, '-', ' '), 0)

Или, вы можете написать это в строке и пропустить CTE все вместе...

select distinct
    m.PersonID
    ,m.MemberShipID
    ,m.MembershipDate
    ,m.CServiceDate
    --,case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end RelatedMemberShips
    ,case when min(convert(date, replace(m2.CServiceDate, '-', ' '), 0)) over (partition by m.MemberShipID) < convert(date, replace(m.CServiceDate, '-', ' '), 0) then min(convert(date, replace(m2.CServiceDate, '-', ' '), 0)) over (partition by m.MemberShipID)  else convert(date, replace(m.CServiceDate, '-', ' '), 0) end
from
    @Memberships m
    left join
    @Transfer t on
    t.FromMembershipID = m.MemberShipID or t.ToMembershipID = m.MemberShipID
    left join 
        @Memberships m2 on m2.MemberShipID = case when t.FromMembershipID <> m.MemberShipID then t.FromMembershipID else t.ToMembershipID end
        and convert(date, replace(m2.MembershipDate, '-', ' '), 0) <= convert(date, replace(m.MembershipDate, '-', ' '), 0)