如何在BigQuery中基于时间戳获取工单最后一条交易记录

问题描述 #

表中存储各工单全量操作流水，单条工单数据为嵌套结构：

• ticket.ticket_id：工单唯一ID
• ticket.type：数组类型，存储工单所有操作类型
• ticket.time_stamp：数组类型，与type数组一一对应，存储每个操作的执行时间戳

需求为按工单维度，提取每个工单时间戳最新的最后一条操作记录。

示例测试数据 #

with sample_data as (
select struct(111 as ticket_id, ['Open','ReOpen','Modified','Cancelled'] as type, [timestamp('2022-7-14 03:39:00'),timestamp('2022-7-14 03:40:00'),timestamp('2022-7-14 03:50:00'),timestamp('2022-7-14 04:39:00')] as time_stamp) as ticket,
union all select struct(122 as ticket_id, ['Open','ReOpen','Modified'] as type, [timestamp('2022-7-14 07:39:00'),timestamp('2022-7-14 07:40:00'),timestamp('2022-7-14 07:50:00')] as time_stamp) as ticket
)

期望输出效果 #

解决方案 #

场景1：数组内操作按时间正序存储（默认场景） #

如果写入数据时已经保证操作按发生时间先后顺序存入数组，直接通过数组偏移量取最后一位即可，性能最高，不需要展开数组做聚合排序：

with sample_data as (
select struct(111 as ticket_id, ['Open','ReOpen','Modified','Cancelled'] as type, [timestamp('2022-7-14 03:39:00'),timestamp('2022-7-14 03:40:00'),timestamp('2022-7-14 03:50:00'),timestamp('2022-7-14 04:39:00')] as time_stamp) as ticket,
union all select struct(122 as ticket_id, ['Open','ReOpen','Modified'] as type, [timestamp('2022-7-14 07:39:00'),timestamp('2022-7-14 07:40:00'),timestamp('2022-7-14 07:50:00')] as time_stamp) as ticket
)
select
  ticket.ticket_id,
  ticket.type[offset(array_length(ticket.type) - 1)] as last_operation_type,
  ticket.time_stamp[offset(array_length(ticket.time_stamp) - 1)] as last_operation_time
from sample_data

场景2：数组内操作时间顺序不固定 #

如果无法保证数组内元素按时间排序，可先对齐两个数组的关联关系，按时间倒序排序后取第一条，兼容乱序场景：

with sample_data as (
select struct(111 as ticket_id, ['Open','ReOpen','Modified','Cancelled'] as type, [timestamp('2022-7-14 03:39:00'),timestamp('2022-7-14 03:40:00'),timestamp('2022-7-14 03:50:00'),timestamp('2022-7-14 04:39:00')] as time_stamp) as ticket,
union all select struct(122 as ticket_id, ['Open','ReOpen','Modified'] as type, [timestamp('2022-7-14 07:39:00'),timestamp('2022-7-14 07:40:00'),timestamp('2022-7-14 07:50:00')] as time_stamp) as ticket
)
select
  ticket.ticket_id,
  last_op.op_type as last_operation_type,
  last_op.op_time as last_operation_time
from sample_data,
unnest([
  array(
    select as struct op_type, op_time
    from unnest(ticket.type) as op_type with offset op_pos
    inner join unnest(ticket.time_stamp) as op_time with offset op_pos
    using(op_pos)
    order by op_time desc
    limit 1
  )[offset(0)]
]) as last_op

执行结果 #

两种写法返回结果一致，完全匹配期望输出：

内容的提问来源于stack exchange，提问作者Mohamed Othman