如何在Cloudant中为MySQL中的不同查询和Cout查询创建等效视图 [英] How to create equivalent view in cloudant for distinct and cout query in mysql

问题描述

我下面有1000个类型的文档，我想在cloudant中设计一个文档（写一个视图），该文档等效于此Mysql查询：

I have 1000 documents of type below and I want to design a document in cloudant (write a view) equivalent to this Mysql query:

 (SELECT 
      COUNT(DISTINCT(correlationassetid)), PERIOD 
  FROM 
      view_asset 
  WHERE 
      ((PERIOD >= '201705') AND (PERIOD <= '201705')) 
  GROUP BY 
      PERIOD 
  ORDER BY 
      PERIOD ASC)

我尝试过以下视图，但未给出正确的结果。有人可以帮我吗？

I have tried below view but it does not give proper result. could any one help me?

function (doc) {
  if(doc.type == "asset"){
    emit(doc.period,1);
  }

{
  "_id": "24dee0ec910e22605fe8fc4189000c56",
  "_rev": "1-d667f0b4ce3d984c0d7aafadd223674a",
  "parentName": "",
  "period": "201701",
  "providerGlobalAssetId": "",
  "cost": "5",
  "providerRegionCode": "",
  "owner": "",
  "snapshotId": "659c5f7a-35d62", 
  "correlationAssetId": "aws-6082634880291"
}

推荐答案

示例查询和示例文档让我有些困惑，因为存在一些不一致之处，但是我将尝试基于两个假设回答您的问题。

I'm a bit confused by your example query and your sample document because there are some inconsistencies, but I am going to try to answer your question based on a couple assumptions.

如果您按单个PERIOD值进行过滤，则SQL语句中不需要GROUPBY子句：

If you are filtering by a single PERIOD value no GROUPBY clause should be required in your SQL statement:

SELECT COUNT(DISTINCT(correlationassetid))
FROM view_asset 
WHERE PERIOD = '201705' 

定义以下设计文档

{
  "_id": "_design/howdoi",
  "views": {
    "filter-by": {
      "map": "function (doc) {\n  if(doc.type === \"asset\") {\n   emit([doc.period, doc.correlationAssetId], 1);\n  }\n}",
      "reduce": "_count"
    }
  },
 "language": "javascript"
}

一个 GET 请求以查询视图

 https://$USER:$PASSWORD@$HOST/$DATABASE/_design/howdoi/_view/filter-by?inclusive_end=true&start_key=[%22201705%22]&end_key=[%22201705%22%2C{}]&reduce=true&group_level=2

如果指定，应返回所需结果start_key = [ 201705] 和 end_key = [ 201705，{}] 。

示例响应，如果在此期间有两个文档，两个文档都包含相同的 correlationAssetId ：

Example response if there are two documents within that period, both containing the same correlationAssetId:

 {
  "rows": [
   {
    "key": [
      "201705",
      "aws-6082634880291"
    ],
    "value": 2
  }
 ]
}

结果集中的行数应标识指定期间的 correlationAssetId s的不同数目。

The number of rows in the result set should identify the distinct number of correlationAssetIds for the specified PERIOD.

两个 correlationAssetId s的示例结果：

{
  "rows": [
    {
      "key": [
        "201701",
        "aws-6082634880291"
      ],
      "value": 1
    },
    {
     "key": [
       "201701",
       "aws-6082634880292"
     ],
     "value": 1
   }
  ]
 }

PS您的示例文档未定义 asset 属性，因此视图定义将不会返回该文档。我在上面的回复中假设该属性在文档中定义。

P.S. Your example document didn't define the asset property and your view definition would therefore not have returned the document. My response above assumes that that property is defined in the documents.

这篇关于如何在Cloudant中为MySQL中的不同查询和Cout查询创建等效视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！