- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
UNNEST 数组上的 N1ql SUM
我有一个包含以下文档的存储桶(Couchbase Community 6.5 版):
钓鱼文档
{
"boatIds": ["boatId_1","boatId_2","boatId_3"],
"areaIds": ["areaId_1","areaId_2","areaId_3"],
"total": 10,
"date": "2021-05-13T00:00:00Z",
"type": "fishing"
},
{
"boatIds": ["boatId_1","boatId_3"],
"areaIds": ["areaId_2","areaId_3"],
"total": 25,
"date": "2021-05-15T00:00:00Z",
"type": "fishing"
}
河流文档
{
"_id": "areaId_1",
"size": 5,
"type": "river"
},
{
"_id": "areaId_1",
"size": 10,
"type": "river"
},
{
"_id": "areaId_1",
"size": 15,
"type": "river"
}
BoatDoc
{
"_id": "areaId_1",
"name": "Small Boat",
"type": "boat"
},
{
"_id": "areaId_1",
"name": "Medium Boat",
"type": "boat"
},
{
"_id": "areaId_1",
"name": "Large Boat",
"type": "boat"
}
我需要一个查询,在哪里可以获得每条河流和每条船的所有钓鱼文件。我在以下查询中使用 UNNEST 运算符得到了这个工作:
SELECT river.size,
boat.name,
fishing.total
FROM bucket_name fishing
UNNEST fishing.riverIds AS river
UNNEST fishing.boatIds AS boat
WHERE fishing.type = "fishing"
但是这个查询的问题是上面查询中的总值是整个钓鱼对象的总值。
SELECT river.size,
boat.name,
river.size/SUM( fishing.riverIds[0].size, fishing.riverIds[1].size, fishing.riverIds[2].size ) * fishing.total
FROM bucket_name fishing
UNNEST fishing.riverIds AS river
UNNEST fishing.boatIds AS boat
WHERE fishing.type = "fishing"
最佳答案
INSERT INTO default VALUES ("f01", { "boatIds": ["boatId_1","boatId_2","boatId_3"], "areaIds": ["areaId_1","areaId_2","areaId_3"], "total": 10, "date": "2021-05-13T00:00:00Z", "type": "fishing" });
INSERT INTO default VALUES ("f02", { "boatIds": ["boatId_1","boatId_3"], "areaIds": ["areaId_2","areaId_3"], "total": 25, "date": "2021-05-15T00:00:00Z", "type": "fishing" });
INSERT INTO default VALUES ("areaId_1", { "_id": "areaId_1", "size": 5, "type": "river" });
INSERT INTO default VALUES ("areaId_2", { "_id": "areaId_2", "size": 10, "type": "river" });
INSERT INTO default VALUES ("areaId_3", { "_id": "areaId_3", "size": 15, "type": "river" });
INSERT INTO default VALUES ("boatId_1", { "_id": "boatId_1", "name": "Small Boat", "type": "boat" });
INSERT INTO default VALUES ("boatId_2", { "_id": "boatId_2", "name": "Medium Boat", "type": "boat" });
INSERT INTO default VALUES ("boatId_3", { "_id": "boatId_3", "name": "Large Boat", "type": "boat" });
SELECT ARRAY {"size": f.river.[v], "name": f.boat.[f.boatIds[pos]], "total": f.total*f.river.[v]/ARRAY_SUM(OBJECT_VALUES(f.river))}
FOR pos:v IN f.areaIds END AS distribution
FROM (SELECT d.*,
OBJECT v._id:v.size FOR v IN (SELECT r._id, r.size FROM default AS r USE KEYS d.areaIds) END AS river,
OBJECT v._id:v.name FOR v IN (SELECT b._id, b.name FROM default AS b USE KEYS d.boatIds) END AS boat
FROM default AS d
WHERE d.type = "fishing") AS f;
{
"requestID": "fbe127b4-2ebb-4b01-a8a1-0bfe5310ed42",
"signature": {
"distribution": "array"
},
"results": [
{
"distribution": [
{
"name": "Small Boat",
"size": 5,
"total": 1.6666666666666667
},
{
"name": "Medium Boat",
"size": 10,
"total": 3.3333333333333335
},
{
"name": "Large Boat",
"size": 15,
"total": 5
}
]
},
{
"distribution": [
{
"name": "Small Boat",
"size": 10,
"total": 10
},
{
"name": "Large Boat",
"size": 15,
"total": 15
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "6.946602ms",
"executionTime": "6.881065ms",
"resultCount": 2,
"resultSize": 730,
"serviceLoad": 2
}
}
或者
SELECT river AS SectionSize,
f.total*(river/ARRAY_SUM(f.sections)) AS total,
f.boats[UNNEST_POS(river)] AS name
FROM (SELECT d.total,
(SELECT RAW r.size FROM default AS r USE KEYS d.areaIds) AS sections,
(SELECT RAW b.name FROM default AS b USE KEYS d.boatIds) AS boats
FROM default AS d
WHERE d.type = "fishing") AS f
UNNEST f.sections AS river;
{
"results": [
{
"SectionSize": 5,
"name": "Small Boat",
"total": 1.6666666666666665
},
{
"SectionSize": 10,
"name": "Medium Boat",
"total": 3.333333333333333
},
{
"SectionSize": 15,
"name": "Large Boat",
"total": 5
},
{
"SectionSize": 10,
"name": "Small Boat",
"total": 10
},
{
"SectionSize": 15,
"name": "Large Boat",
"total": 15
}
]
}
关于couchbase - UNNEST 数组上的 N1ql SUM,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/67515272/
我有这个示例代码: #include #include int main() { Eigen::MatrixXf M = Eigen::MatrixXf::Random(1000, 1000)
我有一个像这样的数据框: +-----+--------+ |count| country| +-----+--------+ | 12| Ireland| | 5|Thailand| +-
我想要 SUM(tot_bill_1+tot_bill_2) AS 总计,但这不起作用 SELECT *, IF(SUM(bill_1) IS NULL, '99', SUM(bill_1)) AS
如果我们有两个矩阵 X 和 Y,都是二维的,现在在数学上我们可以说:sum(X-Y)=sum(X)-总和(Y). Matlab 哪个效率更高?哪个更快? 最佳答案 在我的机器上,sum(x-y) 对于
我正在运行 Hive 1.1.0 并看到对于两个 bigint 列,active_users 和 inactive_users,SUM(active_users + inactive_users) <
是否可以在一个选择查询中求和? 类似这样的事情: SELECT id, SUM(current_price - bought_price)*amount AS profit FROM purchase
这是一个相当奇怪的结果。我希望这些具有相同的产量。 下面还有从数据库中提取的 excel 链接。 https://twentius.opendrive.com/files?89038281_muoyg
我必须对 2 个字段求和,然后再求和。从性能的角度来看,先添加字段还是在对列求和之后添加字段有什么区别? 方法 1 = SELECT SUM(columnA + columnB) 方法 2 = SEL
这是一个经典问题,但我很好奇是否有可能在这些条件下做得更好。 问题:假设我们有一个长度为4*N的排序数组,即每个元素重复4次。请注意,N 可以是任何自然数。此外,数组中的每个元素都受制于 0 A. 执
我正在编写一个 Pig 程序,该程序加载一个用制表符分隔整个文件的文件 例如:名称 TAB 年份 TAB 计数 TAB... file = LOAD 'file.csv' USING PigStora
我有一个包含以下字段的表: EmpID, Code, Amount, TransDate, CM, CMDate 我想要进入数据网格的是 SUM所有的Amount具有相同的 Code和 SUM CM具
我有两个单独的查询用于提取报告信息。一年效果很好。但是,如果一个月超过 1 年,则不会显示正确的响应。 这是我的两个查询: select SUM(rpt_complete.total) total,
我想查询一个团队的积分。通过在列上执行 SUM + 来自具有相同团队 ID 的另一个表的 SUM 来添加这些点。我试着这样写: SELECT k.id, s.fylke, s.
这个问题在这里已经有了答案: How to deal with floating point number precision in JavaScript? (47 个回答) Unexpected
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。 这个问题似乎与 help center 中定义的范围内的编程无关。 . 关闭 5 年前。 Improve
我已经找了一段时间,但找不到这个问题的答案(也许我没有搜索正确的术语或其他东西)。基本上,我有一个数据库,每个日期有任意数量的条目。我需要取包含条目的最后 X 天的总和(忽略没有条目的天数)。我知道如
我正在尝试获取 B 行中包含 A 行中某个值的所有值中的一些值。我猜这个问题很简单。 这是我的查询: =QUERY('Sheet1'!$A$16:D, "Select sum(D) Where C c
我正在尝试运行以下查询,但出现以下错误: You have an error in your SQL syntax; check the manual that corresponds to your
我有一个 tableA,其中包含以下结构 我将此结构修改为如下所示的tableB,以减少行数,并且类别是固定长度的 假设我在 tableA 中修改为新结构后有 210 万条数据,tableB 仅包含
我的表在 Postgres 中的数据: id user_id sell_amount sell_currency_id buy_amount buy_currency_id type
我是一名优秀的程序员,十分优秀!