Skip to content

Instantly share code, notes, and snippets.

@Ivana-
Created April 13, 2021 15:03
Show Gist options
  • Save Ivana-/477bdc56c2141b3259b058352c7522d7 to your computer and use it in GitHub Desktop.
Save Ivana-/477bdc56c2141b3259b058352c7522d7 to your computer and use it in GitHub Desktop.
SQL HW - day 2
create table organization (
id int,
parent int,
name text
);
insert into organization (id, parent, name)
values (1, null, 'ГКБ 1')
,(2, null, 'ГКБ 2')
,(3, 1, 'Детское отделение')
,(4, 3, 'Правое крыло')
,(5, 4, 'Кабинет педиатра')
,(6, 2, 'Хирургия')
,(7, 6, 'Кабинет 1')
,(8, 6, 'Кабинет 2')
,(9, 6, 'Кабинет 3');
select * from organization order by id;
id | parent | name
----+--------+-----------------------------------
1 | | ГКБ 1
2 | | ГКБ 2
3 | 1 | Детское отделение
4 | 3 | Правое крыло
5 | 4 | Кабинет педиатра
6 | 2 | Хирургия
7 | 6 | Кабинет 1
8 | 6 | Кабинет 2
9 | 6 | Кабинет 3
(9 rows)
WITH RECURSIVE r AS (
SELECT id, coalesce(parent, id) as parent, name
FROM organization
WHERE parent IS NULL
UNION
SELECT r.id, organization.id, r.name
FROM r JOIN organization ON organization.parent = r.parent
)
SELECT id, name, count(*) as cnt FROM r GROUP BY id, name Order BY cnt desc LIMIT 1;
id | name | cnt
----+----------+-----
2 | ГКБ 2 | 5
(1 row)
ALTER TABLE organization ADD COLUMN pth int[];
UPDATE organization
SET pth = r.pth
FROM
(WITH RECURSIVE r AS (
SELECT id,
CASE WHEN parent IS NOT NULL THEN ARRAY[parent]::integer[] ELSE ARRAY[]::integer[] END as pth,
parent IS NULL as final
FROM organization
UNION
SELECT r.id,
CASE WHEN organization.parent IS NULL THEN r.pth ELSE organization.parent || r.pth END,
organization.parent IS NULL as final
FROM r JOIN organization ON organization.id = r.pth[1] and NOT final)
SELECT id, pth FROM r WHERE r.final) as r
WHERE organization.id = r.id;
select * from organization order by id;
id | parent | name | pth
----+--------+-----------------------------------+---------
1 | | ГКБ 1 | {}
2 | | ГКБ 2 | {}
3 | 1 | Детское отделение | {1}
4 | 3 | Правое крыло | {1,3}
5 | 4 | Кабинет педиатра | {1,3,4}
6 | 2 | Хирургия | {2}
7 | 6 | Кабинет 1 | {2,6}
8 | 6 | Кабинет 2 | {2,6}
9 | 6 | Кабинет 3 | {2,6}
(9 rows)
SELECT * FROM organization WHERE 2 = ANY(pth);
id | parent | name | pth
----+--------+------------------+-------
6 | 2 | Хирургия | {2}
7 | 6 | Кабинет 1 | {2,6}
8 | 6 | Кабинет 2 | {2,6}
9 | 6 | Кабинет 3 | {2,6}
(4 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment