Дерево с рекурсией и по умолчанию

Использование Постгреса.

У меня есть pricelists

CREATE TABLE pricelists(
  id SERIAL PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES pricelists
);

и еще одна таблица, prices, ссылающаяся на нее

CREATE TABLE prices(
  pricelist_id INTEGER REFERENCES pricelists,
  name TEXT,
  value INTEGER NOT NULL,
  PRIMARY KEY (pricelist_id, name)
);
  • В родительском прайс-листе id=1 может быть 10 цен.
  • Прейскурант id=2 как дочерний элемент родительского 1 может иметь 5 цен, которые переопределяют родительские 1 цены с тем же названием цены.
  • Дочерний прайс-лист id=3, как дочерний прайс-лист 2, может иметь 2 цены, которые переопределяют дочерние 2 цены с тем же названием цены.

Таким образом, когда я запрашиваю цены для детей 3, я хочу получить

  • все цены ребенка 3 и
  • те цены его родителя (дочернего элемента 2), которых нет в дочернем элементе 3 и
  • все родительские 1 цены, которые не существуют до сих пор.

Схема может быть изменена для повышения эффективности.

Пример:

If

SELECT pl.id AS id, pl.parent_id AS parent, p.name AS price_name, value
FROM pricelists pl
JOIN prices p ON pl.id = p.pricelist_id;

дает

| id       |      parent   |  price_name |     value   |  
|----------|:-------------:|------------:|------------:|  
| 1        |  1            | bb          |     10      |  
| 1        |  1            | cc          |     10      |  
| 2        |  1            | aa          |     20      |  
| 2        |  1            | bb          |     20      |  
| 3        |  2            | aa          |     30      |

то я ищу способ получить pricelist_id = 3 цены, которые дали бы мне

| id       |      parent   |  price_name |     value   |  
|----------|:-------------:|------------:|------------:|  
| 1        |  1            | cc          |     10      |  
| 2        |  1            | bb          |     20      |  
| 3        |  2            | aa          |     30      |

postgresql sql common-table-expression recursive-query greatest-n-per-group
person ZAky    schedule 14.04.2015    source источник
comment
Пожалуйста, как всегда, предоставьте свою версию Postgres. SELECT version(). И покажите, что вы пробовали, даже если это не работает.   -  person Erwin Brandstetter    schedule 14.04.2015
comment
Должен ли прейскурант 1 в этом примере действительно иметь себя в качестве родителя, а не NULL?   -  person das-g    schedule 14.04.2015

Ответы (1)


arrow_upward
1
arrow_downward 

WITH RECURSIVE cte AS (
   SELECT id, name, parent_id, 1 AS lvl
   FROM   pricelists
   WHERE  id = 3  -- provide your id here

   UNION ALL
   SELECT pl.id, pl.name, pl.parent_id, c.lvl + 1
   FROM   cte  c
   JOIN   pricelists pl ON pl.id = c.parent_id
   )
SELECT DISTINCT ON (p.price_name)
       c.id, c.parent_id, p.price_name, p.value
FROM   cte c
JOIN   prices p ON p.pricelist_id = c.id
ORDER  BY p.price_name, c.lvl;  -- lower lvl beats higher level
person Erwin Brandstetter    schedule 14.04.2015
comment
Этот запрос не завершится, если есть прайс-листы, которые имеют самих себя в качестве родителей, как в примере, приведенном в вопросе. Хотя я предполагаю, это может быть либо ошибка в данных примера, либо быть охвачены Схемой можно изменить, чтобы быть эффективным. фраза. - person das-g; 14.04.2015
comment
@дас-г. Это правильно. Базовая таблица должна иметь ограничение CHECK (id <> parent_id). а корень должен быть parent_id это NULL. - person Erwin Brandstetter; 14.04.2015
comment
Ах да, это должно быть частью требований последовательности. Я предложил отредактировать вопрос с этим CHECK, хотя этого недостаточно, чтобы избежать петель в ориентированном графе прайс-листа в целом. - person das-g; 14.04.2015
comment
@ das-g: ограничение CHECK исключает только простые циклы быстрого доступа. все виды циклов нарушили бы разумную модель данных. - person Erwin Brandstetter; 14.04.2015
comment
Это то, что я хотел отметить в своем предыдущем комментарии. Я просто сказал это слишком запутанно, я думаю. :-П - person das-g; 15.04.2015
comment
CHECK (id ‹› parent_id) предотвращает ситуацию id==parent в одной записи. Но могу ли я также предотвратить родительский цикл, когда ребенок случайно установлен как родитель своего родителя? - person ZAky; 16.04.2015
comment
@ZAky: не с базовыми инструментами. Вам нужно будет написать триггерную функцию, которая повторяет родословную и собирает идентификаторы по пути, чтобы обнаружить бесконечный цикл. Вот реализация: stackoverflow.com/questions/26671612/ - person Erwin Brandstetter; 16.04.2015