递归跟踪客户状态（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

“ Historical_state”表：

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作为语言。

Answer 1

这是使用联合的一种选择。该查询背后的症结在于，我们生成了一个state_id将customer表加入其中的逻辑列。该表包含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中的一个演示，因为Rextester不支持SQLite，但至少表明查询逻辑是正确的。

演示版