Couchbase N1ql查询选择非分组字段 [英] couchbase N1ql query select with non-group by fields

问题描述

我是不熟悉Couchbase的人，已经浏览了Couchbase文档和其他在线资源已有一段时间，但是我无法使查询正常运行。下面是数据结构和我的查询：

I am new to couchbase and I have been going through couchbase documents and other online resources for a while but I could't get my query working. Below is the data structure and my query:

Table1:
{
    "jobId" : "101",
    "jobName" : "abcd",
    "jobGroup" : "groupa",
    "created" : " "2018-05-06T19:13:43.318Z",
    "region" : "dev"
},
{
    "jobId" : "102",
    "jobName" : "abcd2",
    "jobGroup" : "groupa",
    "created" : " "2018-05-06T22:13:43.318Z",
    "region" : "dev"
},
{
    "jobId" : "103",
    "jobName" : "abcd3",
    "jobGroup" : "groupb",
    "created" : " "2018-05-05T19:11:43.318Z",
    "region" : "test"
}

我需要获取具有给定jobGroup和区域（按jobGroup和区域分组）的最新工作信息（创建时间戳记的最大值）的jobId。

I need to get the jobId which has the latest job information (max on created timestamp) for a given jobGroup and region (group by jobGroup and region).

我的sql查询无法帮助我在jobId上使用自我联接。

查询：

/ *
想法是拔出原本是对所有可能的团体和地区执行最新的在并打印该特定作业的详细信息

My sql query doesn't help me using self-join on jobId.
Query:

/* Idea is to pull out the job which was executed latest for all possible groups and region and print the details of that particular job

select * from (select max(DATE_FORMAT_STR(j.created,'1111-11-11T00:00:00+00:00')) as latest, j.jobGroup, j.region from table1 j 
group by jobGroup, region) as viewtable
join table t
on keys meta(t).id
where viewtable.latest in t.created and t.jobGroup = viewtable.jobGroup and 
viewtable.region = t.region

错误结果：未显示结果

Error Result: No result displayed

所需结果：

{
    "jobId" : "102",
    "jobName":"abcd2",
    "jobGroup":"groupa",
    "latest" :"2018-05-06T22:13:43.318Z",
    "region":"dev" 
},
{ 
    "jobId" : "103", 
    "jobName" : "abcd3",
    "jobGroup" : "groupb",
    "created" : " "2018-05-05T19:11:43.318Z",
    "region" : "test"
}

推荐答案

如果我理解您的查询正确无误，可以使用 group by（没有分组）来回答。我尝试输入示例数据，以下查询给出了正确的结果：

If I understand your query correctly, this can be answered using 'group by' and no join. I tried entering your sample data and the following query gives the correct result:

select max（[created，d]）[1] max_for_group_region $来自默认d
按jobGroup，地区分组的b $ b；

它是如何工作的？它使用分组依据按jobGroup和region对文档进行分组，然后为该组中的每个文档创建一个包含两个元素的数组：

How does it work? It uses 'group by' to group documents by jobGroup and region, then creates a two-element array holding, for every document in the group:

• 创建的时间戳字段


• 时间戳来自的文档

然后将max函数应用于2元素数组的集合。一组数组的最大值在第一个数组位置中寻找最大值，如果有平局，则在第二个位置中寻找，依此类推。在这种情况下，我们将获得具有最大时间戳的两元素数组。

It then applies the max function on the set of 2-element arrays. The max of a set of arrays looks for the maximum value in the first array position, and if there's a tie look at the second position, and so on. In this case we are getting the two-element array with the max timestamp.

现在我们有了一个数组[timestamp，document]，因此我们应用[1]提取只是文档。

Now we have an array [ timestamp, document ], so we apply [1] to extract just the document.

这篇关于Couchbase N1ql查询选择非分组字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！