SQL HW - day 2

task.sql

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)