계층 적 데이터를 생성하기위한 복잡한 재귀 SQL

debugcn 에 게시 Dev

파운드

COVID-19 확산에 대한 매장 방문자의 영향을 평가하려고합니다.

다음은 간단한 시나리오입니다.

VisitorA가 매장에 들어와 직원 1 @ 시간 = 0을 만납니다.
VisitorA는 시간 = 1에서 Employee2를 만납니다.
VisitorB가 매장에 들어와 직원 1 @ 시간 = 1을 만납니다.
VisitorB는 시간 = 2에서 Employee3을 만납니다.
VisitorA가 가게를 떠납니다.

모든 방문자 데이터를 수집하고 시간이 지남에 따라 만난 사람을 수집하면 데이터 세트는 다음과 같습니다.

표 visitorByEmployee:

| VisitorID | EmployeeID | Contact           |
+-----------+------------+-------------------+
| 100       |   X123     | 3/11/2020 1:00    |
| 100       |   X124     | 3/11/2020 1:10    |
| 101       |   X123     | 3/12/2020 1:11    |
| 101       |   X125     | 3/11/2020 1:20    |
| 102       |   X126     | 3/12/2020 10:00   |
| 102       |   X124     | 3/12/2020 10:00   |
| 103       |   X123     | 3/12/2020 11:00   |
| 104       |   X124     | 3/12/2020 12:00   |
| 104       |   X126     | 3/12/2020 12:00   |
| 105       |   X126     | 3/12/2020 12:00   |

결국 다음과 같이 표현할 수있는이 데이터의 계층 구조를 구축하고 싶습니다.

각 트리는 방문자가 바이러스 확산에 미치는 영향을 나타냅니다.

100
  --> X123
    --> 101
      --> X125
    --> 103
  --> X124
    --> 104

102
  --> X126
    --> 104
    --> 105
  --> X124
    --> 104
      --> X126

나는 먼저 루트 노드 (이전 방문자 및 / 또는 그들이 본 직원의 영향을받지 않은 루트 방문자)를 찾아서 이것을 시도했습니다. 이것들은 100과 102였습니다.

SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Contact) AS SeenOrder
INTO 
    #SeenOrder
FROM
    visitorByEmployee

SELECT *
INTO #RootVisitors
FROM #SeenOrder
WHERE SeenOrder = 1

에서 #RootVisitors와 #SeenOrder, 나는 나에게 충격의 계층 구조를 말해 어쩌면 이런 일이 발생할 수있는 테이블을 구축하려는 :

| InitVisitorID | HLevel     | EmployeeID        |   VisitorID |
+---------------+------------+-------------------+-------------+
| 100           |   0        |  X123             |     100     |
| 100           |   0        |  X124             |     100     |
| 100           |   1        |  X123             |     101     |
| 100           |   1        |  X123             |     103     |
| 100           |   1        |  X124             |     104     |
| 100           |   2        |  X125             |     101     |
| 102           |   0        |  X126             |     102     |
| 102           |   0        |  X124             |     102     |
| 102           |   1        |  X126             |     104     |
| 102           |   1        |  X126             |     105     |
| 102           |   1        |  X124             |     104     |
| 102           |   2        |  X126             |     104     |

재귀 CTE를 사용하여 수행 할 수있는 작업입니까? 나는 이것을 시도했지만 방문자에서 직원으로, 방문자에서 직원으로 계층이 이동하기 때문에 재귀 CTE를 만드는 데 어려움을 겪고 있습니다.

업데이트 여기에 내가 작업하고있는 재귀 CTE가 있습니다. 아직 작동하지 않지만 접근 방식은 내가 공유하는 것입니다.

; WITH exposure_tree AS (
/* == Anchor with the root visitors == */
/* == You can think of this: The Employees who were exposed by the Visior == */
SELECT re.VisitorID InitVisitor,
    1 as Level, 
    CASE WHEN 1%2=1 THEN 'Visitor' ELSE 'Employee' END ExposerType,
    re.VisitorID Exposer,
    re.EmployeeID Exposee,
    re.SeenOrder,
    re.InitialContact
FROM #SeenOrder re
WHERE re.SeenOrder = 1

/* == Recursive Part #1 ==
Get the visitors who were exposed next by the exposed employees
*/
UNION ALL

SELECT et.VisitorID InitVisitor,
    Level + 1,
    CASE WHEN (Level+1)%2=1 THEN 'Visitor' ELSE 'Employee' END ExposerType,
    re.EmployeeID,
    re.VisitorID, -- These are switched from the anchor.
    re.SeenOrder,
    re.InitialContact
FROM #SeenOrder re
JOIN exposure_tree et ON et.Exposee = re.EmployeeID AND re.SeenOrder > 1 AND re.InitialContact > et.InitialContact

UNION ALL

/* == Recursive Part #2 ==
Get the next employees who were exposed the second level exposed visitors
*/
SELECT et.VisitorID InitVisitor,
    Level + 2,
    CASE WHEN (Level+2)%2=1 THEN 'Visitor' ELSE 'Employee' END ExposerType,
    re.VisitorID,
    re.EmployeeID,
    re.SeenOrder,
    re.InitialContact
FROM #ROOT_EXPOSURES re
JOIN exposure_tree et ON re.VisitorID = et.Exposer and re.SeenOrder > 1 AND re.InitialContact > et.InitialContact
)
select top 1000 * from exposure_tree ORDER BY InitVisitor, Level

임 팔러

이러한 테이블을 사용하여 재귀 적 CTE를 작성할 수 있습니다. 그러나 코딩은 까다로워집니다.

여기 CTE가 있습니다. 원하는 것을 정확하게 얻으려면 아마도 그것을 조정해야 할 것입니다. 간단하게 열 이름을 변경했습니다.

with
c as (
  select 'v' as type, vid as id, contact, 0 as lvl, cast(concat('/', vid, '/') as varchar(255)) as path
  from (select *, row_number() over(partition by vid order by contact) as rn from v) x where rn = 1
 union all
  select
   case when type = 'v' then 'e' else 'v' end, -- type
   case when type = 'v' then v.eid else v.vid end, -- id
   v.contact,
   c.lvl + 1,
   cast(concat(c.path, case when type = 'v' then v.eid else v.vid end, '/') as varchar(255))
  from c 
  join v on c.lvl <= 10 and v.contact >= c.contact and (c.type = 'v' and v.vid = c.id or c.type = 'e' and v.eid = c.id)
        and c.path not like concat('%', case when type = 'v' then v.eid else v.vid end, '%')
)
select * from c order by path

결과:

type  id    contact                lvl  path                   
----  ----  ---------------------  ---  -----------------------
v     100   2020-03-11 01:00:00.0    0  /100/                  
e     X123  2020-03-11 01:00:00.0    1  /100/X123/             
v     101   2020-03-12 01:11:00.0    2  /100/X123/101/         
v     103   2020-03-12 11:00:00.0    2  /100/X123/103/         
e     X124  2020-03-11 01:10:00.0    1  /100/X124/             
v     102   2020-03-12 10:00:00.0    2  /100/X124/102/         
e     X126  2020-03-12 10:00:00.0    3  /100/X124/102/X126/    
v     104   2020-03-12 12:00:00.0    4  /100/X124/102/X126/104/
v     105   2020-03-12 12:00:00.0    4  /100/X124/102/X126/105/
v     104   2020-03-12 12:00:00.0    2  /100/X124/104/         
e     X126  2020-03-12 12:00:00.0    3  /100/X124/104/X126/    
v     105   2020-03-12 12:00:00.0    4  /100/X124/104/X126/105/
v     101   2020-03-11 01:20:00.0    0  /101/                  
e     X123  2020-03-12 01:11:00.0    1  /101/X123/             
v     103   2020-03-12 11:00:00.0    2  /101/X123/103/         
e     X125  2020-03-11 01:20:00.0    1  /101/X125/             
v     102   2020-03-12 10:00:00.0    0  /102/                  
e     X124  2020-03-12 10:00:00.0    1  /102/X124/             
v     104   2020-03-12 12:00:00.0    2  /102/X124/104/         
e     X126  2020-03-12 12:00:00.0    3  /102/X124/104/X126/    
v     105   2020-03-12 12:00:00.0    4  /102/X124/104/X126/105/
e     X126  2020-03-12 10:00:00.0    1  /102/X126/             
v     104   2020-03-12 12:00:00.0    2  /102/X126/104/         
e     X124  2020-03-12 12:00:00.0    3  /102/X126/104/X124/    
v     105   2020-03-12 12:00:00.0    2  /102/X126/105/         
v     103   2020-03-12 11:00:00.0    0  /103/                  
e     X123  2020-03-12 11:00:00.0    1  /103/X123/             
v     104   2020-03-12 12:00:00.0    0  /104/                  
e     X124  2020-03-12 12:00:00.0    1  /104/X124/             
e     X126  2020-03-12 12:00:00.0    1  /104/X126/             
v     105   2020-03-12 12:00:00.0    2  /104/X126/105/         
v     105   2020-03-12 12:00:00.0    0  /105/                  
e     X126  2020-03-12 12:00:00.0    1  /105/X126/             
v     104   2020-03-12 12:00:00.0    2  /105/X126/104/         
e     X124  2020-03-12 12:00:00.0    3  /105/X126/104/X124/

참고로, 실행하기 위해 SQL Fiddle을 만들어야하는 경우 테스트에 사용한 데이터 스크립트는 다음과 같습니다.

create table v (
  vid varchar(6),
  eid varchar(6),
  contact datetime
);

insert into v (vid, eid, contact) values ('100', 'X123', '2020-03-11 01:00:00');
insert into v (vid, eid, contact) values ('100', 'X124', '2020-03-11 01:10:00');
insert into v (vid, eid, contact) values ('101', 'X123', '2020-03-12 01:11:00');
insert into v (vid, eid, contact) values ('101', 'X125', '2020-03-11 01:20:00');
insert into v (vid, eid, contact) values ('102', 'X126', '2020-03-12 10:00:00');
insert into v (vid, eid, contact) values ('102', 'X124', '2020-03-12 10:00:00');
insert into v (vid, eid, contact) values ('103', 'X123', '2020-03-12 11:00:00');
insert into v (vid, eid, contact) values ('104', 'X124', '2020-03-12 12:00:00');
insert into v (vid, eid, contact) values ('104', 'X126', '2020-03-12 12:00:00');
insert into v (vid, eid, contact) values ('105', 'X126', '2020-03-12 12:00:00');

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-04-2

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

계층 적 데이터를 생성하기위한 복잡한 재귀 SQL

계층 적 데이터를 생성하기위한 복잡한 재귀 SQL

SQL 복잡한 재귀 CTE

키 기반 계층 적 데이터의 Javascript 재귀 병합

Case 문 T-SQL 내부의 복잡한 계층 적 탐색

복잡한 계층 구조에 대한 재귀

복잡한 다단계 계층 SQL

Oracle의 복잡한 계층 적 쿼리

WPF의 복잡한 계층 적 제어

재귀 계층 구조-Linq를 사용한 재귀 쿼리

완전한 계층 구조를 얻기위한 재귀

재귀를위한 공간 복잡성

계층 적 데이터를위한 관계형 데이터베이스 설계?

최대 합이있는 하위 목록을 찾기위한 생성 적 재귀

미로를 생성하기위한 재귀 역 추적기 구현

대량의 계층 적 데이터를 표시하기위한 UI

SQL 재귀 계층

NIB를 사용하여 복잡한 VC 계층 생성

계층 적 JSON을위한 T-SQL

Excel에서 하위 데이터의 존재를 기반으로 계층 적 데이터 합계

계층 적 데이터를 저장하는 데 가장 적합한 / 실행 가능한 C # 데이터 구조

복잡한 SQL 쿼리가 여러 쿼리를 수행하는 것이 더 기계적으로 효율적일 정도로 복잡해 집니까?

복잡한 계층 적 모델 JAGS에서 수렴 확인

복잡한 제네릭 유형을 재귀 적으로 적용

SQL : 재귀 적 CTE를위한 실행 모델

SQL Server 2014에서 계층 적 데이터를 표시하는 방법

클래스 / 객체를 사용하지 않고 재귀 적으로 트리 계층 구조 생성

재귀 적 순열 프린터의 시간 복잡성

데이터 프레임의 복잡한 재구성, 레코드 편집 추적

계층 적 JSON / JS 데이터를 반복하는 가장 좋은 방법

동적 계층 적 클래스 관계를위한 데코레이터 디자인 파악