sql - 根据某些字段比较两行并在发现差异时标记它们

Question

我在雅典娜中创建了一个表。表结构如下所示：

id (string)
event_val (string)
eventsource (string)
eventtype (string)
payload (struct<field1:struct<attribute2:struct<tokenRegion:string,tokenType:string,token:string>,attribute15:string,attribute1:string,attribute8:string,updatedAt:string,status:string,internalRevision:int,attribute13:string,attribute12:string,id:int,textLineCount:int,createdAt:string,attribute11:string,supplierNumber:struct<tokenRegion:string,tokenType:string,token:string>,shippingTermsCode:string,attribute10:string,attachmentTextConcat:string,attribute18:string,attribute4:string,fileName:string,textLineSplitCount:int,version:int>,textLines:array<struct<price:int,invoiced:int,attribute5:string,attribute11:string,status:string,id:int,lineVersion:int,suppAuxPartNum:string,total:double,attribute12:string,description:string,uOM:string,updatedAt:string,attribute2:string,texteld2:array<struct<segment2:string,accountAllocationSequence:int,segment3:string,segment4:string,orderLineId:int,segment5:string,attribute11:string,segment6:string,attribute12:string,accountAllocationId:int,segment7:string,segment1:string,fileName:string,accountAllocationAmount:double,version:int,orderHederId:int>>,quantity:double,lineNumber:int,sourcePartNum:string,lineType:string,version:int,fileName:string,needByDate:string,createdAt:string,orderHeaderId:int>>>)

在此表中，每个 event_val 键将有两个条目。并且事件源字段将是“value_a”或“value_b”。基本上，我们从两个不同的事件源插入两个具有相同 event_val 值的事件。

我需要比较每一对这样的对，如果有区别的话。有效载荷中的某些字段可能存在也可能不存在。

另外，我们如何对 payload.textLines 进行比较？

标签： sqlamazon-athena

Answer 1

您可以使用map_agggroup by onevent_val

注意：您需要检查实际语法。但是像下面这样的事情应该做。

With
intermediate_table as
(
  select event_val, map_agg(eventsource, payload) as payload_by_eventsource
  from <table_name>
  group by event_val
),
intermediate_table_2 as
(
select event_val, payload_by_eventsource['source_a'] as source_a_payload, payload_by_eventsource['source_b'] as source_b_payload
from intermediate_table
)

select .......
from intermediate_table_2
where....