그래서 저는이 SQL 쿼리를 알아 내려는 노력에 완전히 당황했습니다.
나무와 같은 구조로 존재하는 제품 범주 표가 있습니다. 단순화하기 위해 A, B, C의 세 가지 최상위 범주가 있다고 가정 해 보겠습니다. 그 위에 루트 인 범주가 하나 더 있습니다 ( 'All'). 이 카테고리에는 제품을 지정할 수 없습니다. 제품에 할당 할 수없는 카테고리를 구분하기 위해 '콘크리트'가 아닌 '추상'유형이 있습니다.
각 카테고리는 하위 카테고리의 수와 깊이를 가질 수 있습니다. 나는 현재 직계 부모 (인접 목록)에 대한 부모 ID와 함께 이것을 저장하고 있습니다.
Categories
Category Parent Type
All None Abstract
A All Concrete
B All Concrete
C All Concrete
D A Concrete
E D Concrete
F B Concrete
G F Concrete
H C Concrete
I C Concrete
카테고리 필드가있는 다른 제품 테이블이 있습니다. 이 표에 나타나는 유일한 범주는 최상위 범주입니다. 즉. A, B 또는 C 중 하나입니다.
Products
Part Number Category
XXXX-XXXX A
XXXX-YYYY A
XXXX-ZZZZ B
YYYY-XXXX C
두 테이블을 조인하는 쿼리를 만들어 Category가 자식 범주로 바뀌는 행을 만들고 싶습니다. 즉. 의사 코드 관점에서 기본적으로 카테고리에 조인 = 제공된 카테고리는 카테고리의 하위 항목이거나 동일합니다.
그래서 다음과 같습니다.
select * from products
inner join categories
on products.category = descendent of category
결과는 다음과 같습니다.
Part Number Category
XXXX-XXXX E (E's top level concrete parent is A)
XXXX-YYYY E (E's top level concrete parent is A)
YYYY-XXXX H (H's top level concrete parent is C)
YYYY-XXXX I (I's top level concrete parent is C)
나는 최상위 수준까지 모든 구체적인 유형을 검색하는 다음과 같습니다.
with recursive
concrete_parents as (
select category, parent, type
from categories
where category in ('E', 'H', 'I')
UNION ALL
select t2.category, t2.parent, t2.type
from categories as t2
inner join concrete_parents t1
on t1.parent = t2.category
where t2.type = 'Concrete'
)
select distinct * from concrete_parents
order by parent;
이것을 메인 테이블의 내부 조인과 결합하는 방법을 알아낼 수 없습니까?
내가 고려중인 또 다른 대안은 Postgres ltree를 사용하는 것이지만 익숙하지 않습니다.
이견있는 사람?
... 최상위 수준의 구체적인 카테고리를 동적으로 캡처하는 것이 좋습니다.
당신이 말한 이후로 그것은 가능해 보입니다.
이 표 (
Products)에 나타나는 유일한 범주 는 최상위 범주입니다 . A, B 또는 C.
따라서 이러한 최상위 범주는 최종에서 JOIN자동으로 필터링 됩니다. 그리고 그것들 (그리고 그것들 만) parent = 'All'당신의 샘플 데이터에 따라 있기 때문에 , 우리는 한 수준의 재귀를 줄이고 조금 더 빠르게 만들 수 있습니다.
WITH RECURSIVE parent_cat AS (
SELECT category AS original, category, parent -- no need for type
FROM categories c
WHERE category in ('A', 'D', 'H', 'I')
UNION ALL
SELECT pc.original, c.category, c.parent
FROM parent_cat pc
JOIN categories c ON c.category = pc.parent
WHERE pc.parent <> 'All' -- stop at top level, save 1 recursion
)
SELECT p.part_number, pc.category, pc.original
FROM parent_cat pc
JOIN products p USING (category)
WHERE pc.parent = 'All' -- redundant, but a bit faster
ORDER BY pc.original;
또한 type = 'Concrete'다른 유형은 pc.parent = 'All'이미 조인에 의해 필터링되기 때문에로 필터링 할 필요가 없습니다 .
db <> 여기에 바이올린
BTW, 성능이 중요하고 범주가 너무 많이 변경되지 않는 경우 쿼리 MATERIALIZED VIEW에서 rCTE parent_cat를 교체하는 것을 고려하고 적절한 체제를 구현하여 최신 상태로 유지하십시오.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다