Как посчитать количество групп в таблице?

Question

[Александр]

Есть таблица в которой лежат ФИО-дата рождения людей:

CREATE TABLE PEOPLES
(
	[ID] INTEGER  NOT NULL,
	[FIO] NVARCHAR(160) NULL,
	[BIRTHDAY] DATETIME NULL,
	CONSTRAINT [PK_Peoples] PRIMARY KEY  ([ID])
)

Ее тестовое наполнение:

INSERT INTO PEOPLES (ID,FIO,BIRTHDAY) VALUES 
(1,'Фёдоров Бенедикт Лукьевич','1980-01-01'),
(2,'Кириллов Май Дмитрьевич','1970-01-01'),
(3,'Колесников Борис Игнатьевич','2000-01-01'),
(4,'Королёв Ярослав Демьянович','2010-01-01'),
(5,'Алексеева Инесса Мартыновна','1970-01-01'),
(6,'Сидорова Изабелла Дамировна','2000-01-01'),
(7,'Беляева Лея Васильевна','2010-01-01'),
(8,'Стрелкова Магдалина Лаврентьевна','2020-01-01')

Так же имеется таблица с ролями этих людей в родственных связях (муж, жена, сын, дочь и т.д.)

CREATE TABLE SPR_ROLE
(
	[ID] INTEGER  NOT NULL,
	[ROLE_CODE] NVARCHAR(160) NULL,
	CONSTRAINT [PK_SprRole] PRIMARY KEY  ([ID])
)

Ее наполнение:

INSERT INTO SPR_ROLE (ID,ROLE_CODE) VALUES 
(1,'Муж'),
(2,'Жена'),
(3,'Сын'),
(4,'Дочь')

И есть таблица, в которой указано кто кому кем приходится:

CREATE TABLE PEOPLE_LINKS
(
	[ID] INTEGER  NOT NULL,
	[A_ID1] INTEGER NULL,
	[A_ID2] INTEGER NULL,
	[A_ROLE] INTEGER NULL,
	CONSTRAINT [PK_PeopleLinks] PRIMARY KEY  ([ID]),
	FOREIGN KEY ([A_ID1]) REFERENCES [PEOPLES] ([ID]),
	FOREIGN KEY ([A_ID2]) REFERENCES [PEOPLES] ([ID]),
	FOREIGN KEY ([A_ROLE]) REFERENCES [SPR_ROLE] ([ID])
)

И ее наполнение:

INSERT INTO PEOPLE_LINKS (ID,A_ID1,A_ID2,A_ROLE) VALUES 
(1,1,5,2),
(2,5,1,1),
(3,5,4,3),
(4,5,7,4),
(5,1,4,3),
(6,2,6,4)

Если читать ее напрямую получается что, у человека с ID=1 жена это человек с ID=5 и так далее.
Вопрос в том, чтобы разделить всю таблицу PEOPLE_LINKS на группы (семьи), и посчитать в каждой семье количество несовершеннолетних. Логично, что нужно заводить сущность "семья" вида:

CREATE TABLE FAMILYS
(
	[ID] INTEGER  NOT NULL,
	[MEMBER_ID] INTEGER NULL,
	[FAMILY_ID] INTEGER NULL,
	CONSTRAINT [PK_Familys] PRIMARY KEY  ([ID]),
	FOREIGN KEY ([MEMBER_ID]) REFERENCES [PEOPLES] ([ID])
)

Но я не понимаю как ее заполнить на основании таблицы PEOPLE_LINKS

Comments

[mayton2019]

В подобного рода базах обычно пишут темпоральную информацию. Например Беляева была заумжем за всеми мужчинами в этой таблице просто в разное время. Тоесть информация обладает началом действия и длительностью.

Кроме того люди могут менять фамилии. Значит для одного человека может быть несколько записей.

Справочник SPR_ROLE явно неполный. Этих статусов может быть много крат больше. Приёмный сын. Биологический сын. Отчим. Мачеха. В базах соц-сетей могут быть прочие забавные статусы типа там "любовник", "в отношениях". И эти статусы тоже могут быть темпоральны.

[Александр]

mayton2019, Да, такое есть конечно же. Это тестовый вариант от которого отталкиваюсь. Дело не в темпоральности - мне в принципе понять с какой стороны подступиться. Основная загвоздка в том, что в PEOPLE_LINKS есть запись вида ФИО ID=1 имеет жену ID=5 и ФИО ID=5 имеет мужа ID=1, так же ID=5 имеет сыновей-дочерей 4 и 7 и так далее. Решать в лоб - брать всех младше 18 лет, делать INNER с PEOPLE_LINKS по A_ID2, GROUP BY по A_ID1 и считать COUNT(PEOPLE_LINKS.ID) - не корректно, так как у второго члена семьи так же могут быть родственные связи, и таким образом получим что имеем на выходе не одну семью, а две, потому я и указал, что логично приводить их к сущности FAMILYS и проставлять условный номер семьи, но как их именно разбить на семьи - в этом и вопрос.

[Akina]

Ну тут собственно обычный рекурсивный CTE, который собирает всю семью в одну кучу.

Принцип. Сперва всех нумеруем. Далее связанные записи обрабатываем попарно (условия - родственная связь и неравные текущие номера), и обоим присваиваем минимальный из двух номер. Когда рекурсия закончится, вся куча будет поделена на группы, объединённые номером, минимальным из начальных для членов группы.

Хотя, наверное, проще будет на CTE, а UPDATE в цикле по месту.

[Александр]

Akina, вроде обхода дерева по одной таблице в случае CTE? А с UPDATE - закинуть уникальные PEOPLES.ID в таблицу, брать первый MEMBER_ID с пустым значением FAMILY_ID, присвоить ему порядковый номер, искать его в PEOPLE_LINKS.A_ID1 и присваивать всем кто нашелся по SELECT A_ID2 FROM PEOPLE_LINKS WHERE A_ID1 = MEMBER_ID тот же FAMILY_ID, вроде того?

[Akina]

Александр,

вроде обхода дерева по одной таблице в случае CTE?

Ну если искать аналогию - то тут скорее заливка областей подходит.

Прочитайте то, что я пишу, ещё раз. UPDATE по месту делает на 100% то, что я описываю для CTE - но при этом не надо отсеивать нагенерённые в CTE промежуточные значения.

Дерево тут не используется совсем никак. Хотя граф тут есть - таблица родственных связей является списком смежности.