sql - 递归跟踪状态数量未知的客户状态(Presto SQL)

Question

我有一张 table ，当前 state_id我的客户和另一张 table ，其中包含所有州和他们的 state_id s，但没有相应的 customer_id .

但是，历史状态表保存了state_id的信息。它取代了。因此，应该可以递归跟踪客户的状态/旅程。

考虑以下示例:

“客户”表:

customer_id    state_created      current_state_id
1              2017-11-09         33
2              2018-04-01         243
3              2018-07-10         254

“历史状态”表:

state_name     replace_state_id   state_id           state_created
State1                            22                 2015-10-08
State1                            211                2017-06-28
State3                            254                2018-07-10
State6         211                226                2017-12-13
State4         226                243                2018-04-01
State5         22                 33                 2017-11-09

我有兴趣获取每个客户的历史状态信息，即。下表:

customer_id    state_created      state_name       
1              2015-10-00         State1
1              2017-11-09         State5
2              2017-06-28         State1
2              2017-12-13         State6
2              2018-04-01         State4
3              2018-07-10         State3

所以，对于任何 customer_id我知道 current_state_id .有了这个 state_id我可以，用 replace_state_id ，递归跟踪给定客户所处的所有状态。

我想要一个表格，显示所有客户以及这些客户历来所处的所有状态(带有 state_created 列)。没有明确给出每个客户所处的状态数。

数据放在AWS的Athena中，所以应该使用presto sql作为语言。

Answer 1

尝试在此处使用递归 CTE:

WITH RECURSIVE cte (state_id, state_name, state_created) AS (
    SELECT state_id, state_name, state_created
    FROM Historical_state
    UNION ALL
    SELECT h1.state_id, h2.state_name, h2.state_created
    FROM Historical_state h1
    INNER JOIN cte h2
        ON h1.replace_state_id = h2.state_id
)

SELECT
    c.customer_id,
    t.state_created,
    t.state_name
FROM Customer c
INNER JOIN cte t
    ON c.current_state_id = t.state_id
ORDER BY
    c.customer_id,
    t.state_created;

同样，就像 your last question 中的情况一样，我无法展示 SQLite 的 Rexester 演示，它不支持该 SQL 方言。但是，下面的演示表明递归 CTE 逻辑实际上适用于 SQL Server。

Demo