时间戳

首页 > 解决方案 > couchbase 查询返回总和的高值

couchbase - couchbase 查询返回总和的高值

问题描述

我的文档包含 183 个 Person 类型和 2723 个 Purchase 类型文档。基本上，一个人购买产品。示例文档如下。一个人可能从未购买过任何东西。

{"Type" : "Person", PersonID: 1, InitialFee: 100, "MemberGroupTypeID" : 1, _id:"Person_1_1"},
{"Type" : "Person", PersonID: 2, InitialFee: 200, "MemberGroupTypeID" : 1, _id:"Person_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:1, Value: 5, "MemberGroupTypeID" : 1, _id:"Purchase_1_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:2, Value: 10, "MemberGroupTypeID" : 1, _id:"Purchase_2_1_1"},
{"Type" : "Purchase", PersonID: 1, PurchaseID:3, Value: 25, "MemberGroupTypeID" : 1, _id:"Purchase_3_1_1"}

基本上，预期的输出是：

MemberGroupTypeID: 1, FeesTotal: 300, TotalPurchasesCost: 40

我有以下查询，它为每种类型的成员组返回正确的总购买成本，但 FeesTotal 太高了，我不知道它是如何获得这个数字的。我的意思是，在运行查询后，我无法发现预期数字与实际 FeesTotal 之间的关系：

SELECT SUM(person.InitialFee) AS FeesTotal,
SUM(purchase.Value) as TotalPurchasesCost,
person.MemberGroupTypeID
FROM bucket person
LEFT JOIN bucket purchase
ON person.MemberGroupTypeID= purchase.MemberGroupTypeID
WHERE person.Type = "Person" AND purchase.Type = "Purchase" AND
person.PersonId = purchase.PersonId
GROUP BY person.MemberGroupTypeID;

标签： couchbasen1ql

解决方案

JOIN 时，每个 Left Side 可以产生 1:n。如果您汇总，则它会计算多次。

通过首先对整个左侧文档进行分组，然后对所需字段进行分组来使用两级聚合。

SELECT b.MemberGroupTypeID,
       SUM(b.InitialFee) AS FeesTotal,
       SUM(b.PurchasesCost) AS TotalPurchasesCost,
FROM (SELECT person.InitialFee,
             person.MemberGroupTypeID,
             SUM(purchase.`Value`) AS PurchasesCost
       FROM bucket AS person
       LEFT JOIN bucket AS purchase
       ON person.MemberGroupTypeID = purchase.MemberGroupTypeID
          AND purchase.Type = "Purchase" AND person.PersonId = purchase.PersonId
       WHERE person.Type = "Person"
       GROUP BY person) AS b
GROUP BY b.MemberGroupTypeID;

推荐阅读