Как получить последнее непустое значение иерархии?

Question

У меня есть иерархия с соответствующим значением, связанным с каждым уровнем, скажем:

A               100
  A1            NULL
  A2            NULL
B
  B1            NULL
  B2            1000
      B21       500 
      B22       500
  B3            NULL

Эта иерархия материализована в моей базе данных как иерархия родитель-потомок

Hierarchy Table
------------------------
Id       Code      Parent_Id
1          A          NULL
2          A1          1
3          A2          3
4          B          NULL
5          B1          4
6          B2          4
7          B21         6
8          B22         6
9          B3          4

А вот моя таблица фактов:

Fact Table
------------------------
Hierarchy_Id          Value
1                      100
6                      1000
7                      500
8                      500

У меня вопрос: знаете ли вы / имеете ли вы представление о том, как получить только последнее непустое значение моего иерархии? Я знаю, что есть функция MDX, которая может выполнять эту работу, но я бы хотел сделать это по-другому.

Чтобы было ясно, желаемый результат будет:

Fact Table
------------------------
Hierarchy_Id          Value
1                      100
7                      500
8                      500

(При необходимости работа по сглаживанию иерархии уже выполнена...)

Заранее спасибо!

Answer 1

Если коды для вашей иерархии верны, то вы можете использовать информацию в кодах, чтобы определить глубину иерархии. Я думаю, что вы хотите отфильтровать любой "код", где есть более длинный код, который начинается с него.

В таком случае:

select f.*
from fact f join
     hierarchy h
     on f.hierarchyId = h.hierarchyId
where not exists (select 1
                  from fact f2 join
                       hierarchy h2
                       on f2.hierarchyId = h2.hierarchyId
                  where h2.code like concat(h.code, '%') and
                        h2.code <> h.code
                 )

Здесь я использовал функцию concat() создать шаблон. В некоторых базах данных вы можете использовать + или же || вместо.