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

Z时代
2024-01-10
分类：问答

我有一个表，该表包含我的客户的当前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作为语言。

回答：

这是使用联合的一种选择。该查询背后的症结在于，我们生成了一个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;