如何为下表编写postgres查询？

Question

这是非常奇怪的查询，我不知道如何处理它。 以下是表格。

id descendentId attr_type attr_value 
1 {4}    type_a  
2 {5}    type_a  
3 {6}    type_a  
4 {7,8}    type_b  
5 {9,10}   type_b  
6 {11,12}   type_b  
7 {}    type_x TRUE 
8 {}    type_y "ABC" 
9 {}    type_x FALSE 
10 {}    type_y "PQR" 
11 {}    type_x FALSE 
12 {}    type_y "XYZ" 

输入相关的查询将是1,2,3 ..输出应该"ABC"。

逻辑是 - 从1,2,3开始通过后代ID循环，直到达到attr_type x。如果attr_type x达到了7,9和11，那么检查哪一个是true。对于例如7是真的，那么 得到它的兄弟type_y（检查第4行），这是8并返回它的值。

所有这些都是字符串格式。

Answer 1

这可以用recursive CTEs解决：

with recursive params(id) as (
    select e::int 
    from unnest(string_to_array('1,2,3', ',')) e -- input parameter 
), 
rcte as (
    select '{}'::int[] parents, 
      id, 
      descendent_id, 
      attr_type, 
      attr_value 
    from attrs 
    join params using (id) 
    union all 
    select  parents || rcte.id, 
       attrs.id, 
       attrs.descendent_id, 
       attrs.attr_type, 
       attrs.attr_value 
    from  rcte 
    cross join unnest(descendent_id) d 
    join  attrs on attrs.id = d 
    where  d <> all (parents) -- stop at loops in hierarchy 
) 
select y.attr_value 
from rcte x 
join rcte y using (parents) -- join siblings 
where x.attr_type = 'type_x' 
and x.attr_value = 'true' 
and y.attr_type = 'type_y' 

http://rextester.com/YDLDH11322

Answer 2

这的确是这样的查询复杂的数据模型，但我的方式就是击败了层次第一：

WITH RECURSIVE 
    typex(id, use) AS (
     SELECT id, attr_value::boolean 
     FROM weird 
     WHERE attr_type = 'type_x' 
     UNION 
     SELECT w.id, typex.use 
     FROM weird w 
      JOIN typex 
       ON ARRAY[typex.id] <@ w.descendentid 
    ), 
    typey(id, value) AS (
     SELECT id, attr_value 
     FROM weird 
     WHERE attr_type = 'type_y' 
     UNION 
     SELECT w.id, typey.value 
     FROM weird w 
      JOIN typey 
       ON ARRAY[typey.id] <@ w.descendentid 
    ) 
SELECT id, value 
FROM typex 
    NATURAL JOIN typey 
WHERE use 
    AND id = 1; 

┌────┬───────┐ 
│ id │ value │ 
├────┼───────┤ 
│ 1 │ ABC │ 
└────┴───────┘ 
(1 row)