sql - 递归跟踪客户状态（Presto SQL）

Question

我有一张表，其中包含我的客户的当前 state_id，另一张表包含所有状态及其 state_id，但没有相应的 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
State4         211                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              2018-04-01         State4
3              2018-07-10         State3

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

标签： sqlamazon-athenapresto

Answer 1

这是使用联合的一种选择。这个查询背后的关键是我们生成了一个逻辑state_id列，我们将客户表连接到该列。此表包含state_id给定状态的当前值和替换值。

SELECT
    c.customer_id,
    t.state_created,
    t.state_name
FROM Customer c
INNER JOIN
(
    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 Historical_state h2
        ON h1.replace_state_id = h2.state_id
) t
    ON c.current_state_id = t.state_id;

这里是MySQL中的demo，因为Rextester不支持SQLite，但至少说明查询逻辑是正确的。

演示