arangodb - 如何将 ArangoDb 中的结果分组为单个记录？

Question

我有某种类型的事件列表，其结构如下:

{
 createdAt: 123123132,
 type: STARTED,
 metadata: {
     emailAddress: "foo@bar.com"
 }
}

类型数量是预定义的(START、STOP、REMOVE...)。用户在一段时间内产生一个或多个事件。

我想要获得以下聚合:

For each user, calculate the number of events for each type.

我的 AQL 查询如下所示:

FOR event IN events
  COLLECT
    email = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  LIMIT 10
  RETURN {
      email,
      t: {type, count}
  }

这会产生以下输出:

{ email: '_84@example.com', t: { type: 'CREATE', count: 203 } }
{ email: '_84@example.com', t: { type: 'DEPLOY', count: 214 } }
{ email: '_84@example.com', t: { type: 'REMOVE', count: 172 } }
{ email: '_84@example.com', t: { type: 'START', count: 204 } }
{ email: '_84@example.com', t: { type: 'STOP', count: 187 } }
{ email: '_95@example.com', t: { type: 'CREATE', count: 189 } }
{ email: '_95@example.com', t: { type: 'DEPLOY', count: 173 } }
{ email: '_95@example.com', t: { type: 'REMOVE', count: 194 } }
{ email: '_95@example.com', t: { type: 'START', count: 213 } }
{ email: '_95@example.com', t: { type: 'STOP', count: 208 } }
...

即每种类型我都有一行。但我想要这样的结果:

{ email: foo@bar.com, count1: 203, count2: 214, count3: 172 ...}
{ email: aaa@fff.com, count1: 189, count2: 173, count3: 194 ...}
...

或者

 { email: foo@bar.com, CREATE: 203, DEPLOY: 214, ... }
 ...

即对结果再次分组。

我还需要按计数对结果(而不是事件)进行排序:返回例如CREATE 事件数量最多的前 10 位用户。

如何做到这一点？

一个解决方案

这里有一个解决方案，请检查已接受的答案以了解更多信息。

FOR a in (FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
  RETURN MERGE(PUSH(perUser[* RETURN {[LOWER(CURRENT.type)]: CURRENT.count}], {email})))
SORT a.create desc
LIMIT 10
RETURN a

Answer 1

您可以按用户和事件类型进行分组，然后按用户再次分组，仅保留类型和已计算的事件类型计数。在第二次聚合中，了解事件属于哪些组以构建结果非常重要。安array inline projection可以用来保持查询简短:

FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
    RETURN MERGE(PUSH(perUser[* RETURN {[CURRENT.type]: CURRENT.count}], {email}))

另一种方法是按用户分组并保留事件类型，然后将类型分组到子查询中。但在我的测试中它明显慢了(至少没有定义任何索引):

FOR event IN events
  LET type = event.type
  COLLECT
    email = event.metadata.emailAddress INTO groups KEEP type
    LET byType = (
    FOR t IN groups[*].type
        COLLECT t2 = t WITH COUNT INTO count
        RETURN {[t2]: count}
    )
    RETURN MERGE(PUSH(byType, {email}))

返回 CREATE 事件最多的前 10 位用户要简单得多。筛选 CREATE 事件类型，然后按用户分组并计算事件数量，按此数字降序排序并返回前 10 个结果:

FOR event IN events
    FILTER event.type == "CREATE"
    COLLECT email = event.metadata.emailAddress WITH COUNT INTO count
    SORT count DESC
    LIMIT 10
    RETURN {email, count}

EDIT1:为每个用户返回一个文档，其中事件类型已分组和计数(如第一个查询中)，但捕获 MERGE 结果，按一个特定事件类型的计数排序(此处:CREATE )并返回该类型的前 10 位用户。结果与问题中给出的解决方案相同。它节省了子查询 FOR a IN (FOR event IN events ...) ... RETURN a 但是:

FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
  LET ret = MERGE(PUSH(perUser[* RETURN {[CURRENT.type]: CURRENT.count}], {email}))
  SORT ret.CREATE DESC
  LIMIT 10
  RETURN ret

EDIT2:查询生成示例数据(需要存在集合events):

FOR i IN 1..100
    LET email = CONCAT(RANDOM_TOKEN(RAND()*4+4), "@example.com")
    FOR j IN SPLIT("CREATE,DEPLOY,REMOVE,START,STOP", ",")
        FOR k IN 1..RAND()*150+50
            INSERT {metadata: {emailAddress: email}, type: j} INTO events RETURN NEW