sql - 如何根据程序订单更新日期列？

Question

我有一个包含三种不同状态的表'

点击 > 认领 > 购买(这是正确的顺序)

(见下表)

对于每个 number_id，我需要将这些状态日期时间中的每一个都放在单独的列中。

DROP TABLE TBL_A;
CREATE TABLE TBL_A
(
    number_id varchar(50), 
    deadline_date datetime, 
    status varchar,
    updated_at_datetime datetime
);

INSERT INTO TBL_A
VALUES (121144, '2021-12-30', 'clicked','2021-10-08'),
       (121144, '2021-12-30', 'claimed','2021-10-09'),
       (121144, '2021-12-30', 'bought','2021-10-10'),
       (121111, '2021-11-30', 'clicked','2021-09-08'),
       (121115, '2021-11-30', 'clicked','2021-07-08'),
       (121122, '2021-12-15', 'clicked','2021-08-09'),
       (121122, '2021-12-15', 'claimed','2021-08-10'),
       (121166, '2021-12-20', 'clicked','2021-07-09'),
       (121166, '2021-12-20', 'claimed','2021-08-09'),
       (120022, '2021-12-15', 'bought','2021-06-10'),
       (120023, '2021-12-20', 'bought','2021-06-09'),
       (120024, '2021-12-20', 'claimed','2021-06-09');
       
   
select 
NUMBER_ID,
deadline_date,
(array_agg(STATUS) within group(order by updated_at_datetime desc)[0])::varchar as last_status,
   coalesce(max(case when STATUS = 'clicked'  THEN updated_at_datetime END),'2999-12-31'::datetime) as clicked_date,
   coalesce(max(case when STATUS = 'claimed'   THEN updated_at_datetime END),'2999-12-31'::datetime) as claimed_date,
   coalesce(max(case when STATUS = 'bought' THEN updated_at_datetime END),'2999-12-31'::datetime) as bought_date
from TBL_A a
group by 1,2
order by number_id

在最后一个查询中，我需要查询具有基于 updated_at_datetime 的 LAST 状态，并且每个状态的 update_at_datetime 在它们自己的列中。

目前，查询表示如果未找到该 number_ID 的状态，则将此值默认为 = '2999-12-31'

但是，现在我需要更新查询以说明如果未找到已点击的状态，则根据以下状态更新 clicked_datetime。

例如，如果未找到状态 CLICKED 的 update_at_datetime(而不是将 clicked_datetime 更新为“2999-12-31”)，则更新此日期以匹配 CLAIMED STATUS 的 update_at_datetime。如果也未找到 CLAIMED 状态，则更新 clicked_datetime 以匹配已购买 STATUS 的 update_at_datetime。

CLICKED_DATETIME 应该总是有一个 VALID 日期，因为我们可以将它与 status = 'claimed' 的日期时间匹配，如果也没有找到状态 'claimed'，更新 status = 'bought' 的 update_at_datetime 的 clicked_datetime

如果未找到 CLAIMED OR BOUGHT 状态的 update_at_datetime，那么我们可以将该值默认为“2999-12-31”。

有人可以帮我修改最后一个查询以考虑到这一点吗？

Answer 1

看起来很简单？
只需添加更多内容即可。

select NUMBER_ID, deadline_date
, (array_agg(STATUS) within group(order by updated_at_datetime desc)[0])::varchar as last_status
, coalesce(
    max(case when STATUS = 'clicked' THEN updated_at_datetime END)
  , max(case when STATUS = 'claimed' THEN updated_at_datetime END)
  , max(case when STATUS = 'reverted' THEN updated_at_datetime END)
  , '2999-12-31'::datetime
 ) as clicked_date
, coalesce(max(case when STATUS = 'claimed'   THEN updated_at_datetime END),'2999-12-31'::datetime) as claimed_date
, coalesce(max(case when STATUS = 'bought' THEN updated_at_datetime END),'2999-12-31'::datetime) as bought_date
from TBL_A a
group by number_id, deadline_date
order by number_id