ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Хитровыдуманный запрос для таблицы с иерархией
2 сообщений из 2, страница 1 из 1
Хитровыдуманный запрос для таблицы с иерархией
    #39800419
polin11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
polin11
Гость
Использую СУБД postgresql.
Есть иерархическая таблица с каталогами данных (в примере корнями каталогов являются Корень1, Корень2 у них поле parent и root
заполнено null)
Рабочий пример
https://www.db-fiddle.com/f/omQcctZX4wMvgyXymmNsKL/0

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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', то должно ничего не вернут, так как
4) есть папка с датой 2015-12-31 в ней нет записи с кодом 'Корень1.111'
5) если full_code = 'Корень1' Дата = '2017-02-02', то должно вернуться
(2, 'Корень1.2017-12-31', '2017-12-31', 1, 1, false)
6) если full_code = 'Корень1' Дата = '2019-02-02', то должно вернуться
(1, 'Корень1','Корень1', null, null, true)
7) если full_code = 'Корень1.2017-12-31' Дата = '2019-02-02', то должно вернуться
(1, 'Корень1','Корень1', null, null, true)

Написал запрос, логика такая узнаем значение id корня каталога, затем строим
список из корня и папкок с датами, сравниваем с переданной датой, берем актуальную запись с датой,
проверяем есть ли там искомый полный код.
Но запрос получился слишком сложный:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
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.2017-12-31'
limit 1),
T AS(
select id
from catalog
where (parent = (select * from root) and is_node is false  
AND code >= '2019-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.2017-12-31'
union
select id
from catalog
where root = (select * from t) and full_code = 'Корень1.2017-12-31' and (parent is not null and is_node is not false)
limit 1)
SELECT *
FROM catalog
where id in (select * from ids)



Вопросы такие:
1) Как упростить запрос?
2) Главный вопрос, как написать запрос, если передан массив полных кодов, и нужно получить массив актуальных записей.
Даже нет идей, как решить этот вопрос???
...
Рейтинг: 0 / 0
12.04.2019, 11:38
| Ответить | Цитировать | Написать  
Хитровыдуманный запрос для таблицы с иерархией
    #39801956
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
а использовать LTREE ?
...
Рейтинг: 0 / 0
16.04.2019, 10:59
| Ответить | Цитировать | Написать  
2 сообщений из 2, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Хитровыдуманный запрос для таблицы с иерархией
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=42&tid=1995244]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 17ms
total:  151ms
созд. / загр.: 151ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]