Модераторы: LSD
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Хитровыдуманный запрос для таблицы с иерархией 
:(
    Опции темы
polin11
Дата 12.4.2019, 11:55 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Использую СУБД postgresql.
Есть иерархическая таблица с каталогами данных (в примере корнями каталогов являются Корень1, Корень2 у них поле parent и root
заполнено null)
Рабочий пример
https://www.db-fiddle.com/f/2hbRo3qUfhwACNHVBxy3A9/0
 
Код

CREATE TABLE rec (
  id INTEGER,
  full_code VARCHAR (255),
  code VARCHAR (255),
  parent INTEGER,
  root INTEGER,
  is_node bool
) ;--DEFAULT CHARSET=utf8 ;

INSERT INTO rec
  (id, full_code, code, parent, root, is_node)
VALUES
  (1, 'Корень1','Корень1', null, null, true),
  (2, 'Корень1.2017-12-31', '2017-12-31', 1, 1, false),
  (3, 'Корень1.2016-12-31', '2016-12-31', 1, 1, false),
  (4, 'Корень1.2015-12-31', '2015-12-31', 1, 1, false),
  (5, 'Корень1.111', '111', 1, 1, null),
  (6, 'Корень1.111', '111', 4, 4, null),
  (7, 'Корень1.111', '111', 3, 3, null),
  (8, 'Корень1.111', '111', 2, 2, null),
  (9, 'Корень1.222', '222', 2, 2, null),
  (10, 'Корень1.333', '333', 3, 3, null),
  (11, 'Корень2','Корень2', null, null, true)

  
В каждом каталоге есть папки с датой, в этой папке лежат записи актуальные до указанной даты,
у таких папок поле is_node имеет значение false. 

Нужно написать запрос, который по значению full_code и дате, вернет запись актуальную на указанную дату.
Если будет передан полный код корня каталога или полный код записи с папкой, то нужно вернуть 
актуальную папку с датой.
Например:
1) если full_code = 'Корень1.111' Дата = '2017-02-02', то должно вернуться
  (8, 'Корень1.111', '111', 2, 2, null)
2) если full_code = 'Корень1.111' Дата = '2019-02-02', то должно вернуться из корня запись
    (5, 'Корень1.111', '111', 1, 1, null)
3) если full_code = 'Корень1.111' Дата = '2012-02-02', то должно ничего не вернут, так как
есть папка с датой 2015-12-31 в ней нет записи с кодом 'Корень1.111'
4) если full_code = 'Корень1' Дата = '2017-02-02', то должно вернуться
    (2, 'Корень1.2017-12-31', '2017-12-31', 1, 1, false)
5) если full_code = 'Корень1' Дата = '2019-02-02', то должно вернуться
     (1, 'Корень1','Корень1', null, null, true)
6) если full_code = 'Корень1.2017-12-31' Дата = '2019-02-02', то должно вернуться
  (1, 'Корень1','Корень1', null, null, true)
  
Написал запрос, логика такая узнаем значение id корня каталога, затем строим 
список из корня и папок с датами, сравниваем с переданной датой, берем актуальную запись с датой,
проверяем есть ли там искомый полный код.
Но запрос получился слишком сложный:
Код

WITH ROOT AS(
select (
   case WHEN r1.root is NULL THEN  r1.id  
 WHEN r1.is_node is False THEN  r1.root  
 else  (select case WHEN r2.root is NULL THEN  r2.id else r2.root end   from catalog r2 where r2.id = r1.root LIMIT 1) end ) as root
from catalog r1 
where r1.full_code = 'Корень1.111'
limit 1),
T AS(
select id
from catalog
where (parent = (select * from root) and is_node is false  
AND code >= '2017-02-02'::text) OR id= (select * from root)  ORDER BY parent NULLS LAST, code
LIMIT 1 ),
IDS AS(
select ( select * from T ) as id
from catalog
where  (parent is null or is_node is false) and full_code = 'Корень1.111'
union
select id
from catalog
where root = (select * from t) and full_code = 'Корень1.111' and (parent is not null and is_node is not false)
limit 1)
SELECT *
FROM catalog
where id in (select * from ids)


Вопросы такие:
1) Как упростить запрос?
2) Главный вопрос, как написать запрос, если передан массив полных кодов, и нужно получить массив актуальных записей. 
Даже нет идей, как решить этот вопрос??? 

PM MAIL   Вверх
Akina
Дата 12.4.2019, 13:20 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 453



Я верно понимаю, что:

1) Схема - строго двухуровневая?
2) "папки с датой, в этой папке лежат записи актуальные до указанной даты, у таких папок поле is_node имеет значение false" - это всегда второй уровень?



--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
polin11
Дата 12.4.2019, 13:37 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Цитата(Akina @ 12.4.2019,  13:20)
Я верно понимаю, что:

1) Схема - строго двухуровневая?
2) "папки с датой, в этой папке лежат записи актуальные до указанной даты, у таких папок поле is_node имеет значение false" - это всегда второй уровень?

1) Нет, в папках с датами может быть сколько угодно записей с любым уровнем вложенности. Также в самом корне кроме папок с датами могут быть другие записи (как бы актуальные на текущую дату) 
2) У папок с датами всегда уровень 2, родителем таких папок является корень каталога
PM MAIL   Вверх
Akina
Дата 12.4.2019, 13:51 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 453



Тогда:

1) JOIN корней с папками-датами. Получаем для каждого корня набор имеющихся дат.
2) JOIN записи с требуемым full_code к своему корню (по full_code LIKE root.code || '%') и отбор записей по не-превышению даты.
3) Получение самой "свежей" записи с помощью FIRST_RECORD() 




--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
  
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | PostgreSQL | Следующая тема »


 




[ Время генерации скрипта: 0.1155 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.