N1QL多重联接和求和查询产生错误的输出 [英] N1QL Multiple join and sum query producing wrong output

问题描述

我有一个包含以下三个文档的存储桶:

I have a bucket containing the following three documents:

coffee {
    id
}

cold_coffee {
    coffeeId
    number
}

warm_coffee {
    coffeeId
    number
}

我已经使用Couchbase Server Community Edition 6.0.0在N1QL中编写了以下查询:

I have written the following query in N1QL using Couchbase Server Community Edition 6.0.0:

SELECT META(coffee).id as coffeeId,
SUM(cold_coffee.`number`) as `ccNumber`,
SUM(warm_coffee.`number`) as `wcNumber`,
FROM coffee_bucket coffee
left JOIN coffee_bucket cold_coffee
    ON  META(coffee).id = cold_coffee.coffeeId 
    and cold_coffee.type='cold_coffee'
left JOIN coffee_bucket warm_coffee
    ON META(coffee).id = warm_coffee.coffeeId 
    and warm_coffee.type='warm_coffee'
where coffee.type='coffee'
group by META(coffee).id;

我为每份咖啡有多个cold_coffee和warm_coffee文件，我需要将所有cold_coffee和warm_coffee文件的数字加起来.我遇到的问题是，例如，如果我有以下文档:

I have multiple cold_coffee and warm_coffee documents for every coffee and I need to sum the numbers for all of the cold_coffee and warm_coffee documents. The problem I am having is that if for example I have the following documents:

[
    coffee {
        id: 1
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    warm_coffee {
        coffeeId:1
        number:10
    }
]

我的总计如下:

ccNumber: 10
wcNumber:20 

似乎是由于联接的缘故，对单个warm_coffee文件进行了两次计数?

It seems because of the joins the single warm_coffee document is being counted twice?

我偶然发现了这个网站，可能是相同的错误，但不幸的是它是SQL.

I came across this site with possibly the same error but unfortunately it's SQL.

而且我不确定如何使用N1QL解决此问题，因为JOIN的右手项必须是表/存储桶，如以下

And I am unsure how to solve this using N1QL because the right hand term of a JOIN must be a table / bucket as seen in the following post.

这是一个可能的解决方案，但我不确定如何实现这就是N1QL?

Here is a possible solution but I am not sure how to implement this is N1QL?

有人可以帮忙吗?

推荐答案

JOINS可以扩展原始行.在第一个Join左边，大多数文档被展开.您将第一个文档字段用作第二个连接条件，该条件可以产生相同条件的多个文档.这就是语义的工作方式.您需要根据需要调整加入条件.

JOINS can expand the original rows. During first Join left most document is expanded. You are using first document field as second join condition that can produce multiple documents same condition. This how semantics works. You need to adjust your join condition based on the needs.

第二个JOIN最多使用LEFT中的一个文档.DISTINCT上的SUM可能不起作用，因为对相同值(warm_coffee，可能正在计数)的不同文档进行一次计数.如果我是对的，那么您正在寻找第二次加入，以使用LEFT大多数咖啡中的独特文档.

The second JOIN uses one document from the LEFT most. SUM over DISTINCT may not work because different documents same value (warm_coffee, may under count) is counted once. If I am right you are looking second join to use unique document from LEFT most coffee.

也许您正在寻找类似这样的东西

May be you are looking some thing like this

SELECT c.coffeeId,
       MAX(c.ccNumber) AS `ccNumber`,
       SUM(warm_coffee.`number`) AS `wcNumber`,
FROM ( SELECT META(coffee).id AS coffeeId,
       SUM(cold_coffee.`number`) AS `ccNumber`
       FROM coffee_bucket coffee
       LEFT JOIN coffee_bucket cold_coffee
            ON  META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
       WHERE coffee.type='coffee'
       GROUP BY META(coffee).id
     ) AS c
LEFT JOIN coffee_bucket warm_coffee
          ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
GROUP BY c.coffeeId;

3级加入

SELECT d.coffeeId,
       MAX(c.ccNumber) AS `ccNumber`,
       MAX(c.wcNumber) AS `wcNumber`,
       SUM(ch.`number`) AS `chNumber`
FROM ( SELECT c.coffeeId,
              MAX(c.ccNumber) AS `ccNumber`,
              SUM(warm_coffee.`number`) AS `wcNumber`,
       FROM ( SELECT META(coffee).id AS coffeeId,
              SUM(cold_coffee.`number`) AS `ccNumber`
              FROM coffee_bucket coffee
              LEFT JOIN coffee_bucket cold_coffee
                   ON  META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
              WHERE coffee.type='coffee'
              GROUP BY META(coffee).id
            ) AS c
       LEFT JOIN coffee_bucket warm_coffee
                 ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
       GROUP BY c.coffeeId) AS d
LEFT JOIN coffee_bucket ch
     ON d.coffeeId = ch.coffeeId AND ch.type='chaoc_coffee'
GROUP BY d.coffeeId
;

这篇关于N1QL多重联接和求和查询产生错误的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！