我想用下表来计算z:
| item | value |
|---|---|
| a | 6 |
| b | 8 |
| group | source_item | weighting |
|---|---|---|
| x | a | 1 |
| x | b | 0.25 |
| y | a | 1 |
| group | source_group | weighting |
|---|---|---|
| y | x | 0.5 |
| z | x | 1 |
| z | y | 1 |
作为一个人类，我会通过计算来解决:
x=1a+0.25b=8
y=1a+0.5x=10
z=1x+1y=18
然而，我正在努力寻找一种在SQL中执行此操作的干净方法.
通过使用递归CTE，我可以相当容易地(我希望)干净地获得操作顺序--如下所示:
WITH RECURSIVE
iterations AS (
--start by getting the groups that aren't dependent on other groups
SELECT
g.group
, 1 AS iteration
FROM
groups AS g
LEFT JOIN groups_groups AS gg
ON gg.source_group = g.group
WHERE
gg.source_group IS NULL
--traverse dependencies upwards
UNION ALL
SELECT
gg.group
, i.iteration + 1 AS iteration
FROM
iterations AS i
LEFT JOIN groups_groups AS gg
ON gg.source_group = i.group
)
--filter to max iteration
SELECT DISTINCT
i.group
, MAX(i.iteration) OVER (PARTITION BY i.group) AS iteration
FROM
iterations AS i
ORDER BY
iteration
这将按如下方式返回订单:
| group | iteration |
|---|---|
| x | 1 |
| y | 2 |
| z | 3 |
从这里开始，我计划使用另一棵递归树来遍历迭代并计算值.但是，由于您只能连接到最后一次递归，因此当您达到z时，x的值是不可访问的.我可能会在每次递归中提取所有的值，并在iteration + 1上做一些联接，以确保循环适当地结束，但我开始感觉到这不可能是做事情的"正确"方式.
有没有更简单、更干净的方法来解决这个问题？