java - OrientDB集合扩展和分组

Question

我的数据库中有以下类(class):

1. 商品{名称:字符串，价格:双倍，...}
2. OrderSpec{商品(1-1):链接，数量:双}
3. 订单{spec(1-*):链接列表, 日期:日期, ...}

然后我必须从特定日期的订单中选择所有商品，将其分组并进行一些聚合，例如每件商品的数量总和。在标准 SQL DB 中，我会进行一些连接、按日期过滤和按 Goods_id 分组，但不知道如何在 OrientDB 中实现这一点。我从

开始

select expand(spec) from Order where date = "..."

但是当我添加分组时:

select expand(spec) from Order group by spec.goods

结果为空。

一般来说，我希望找到与以下 SQL 查询等效的内容:

select goods_id, goods_name, sum(order_spec_quantity), sum(goods_price) from Order
    left join OrderSpec on order_id = order_spec_order_id
    left join Goods on goods_id = order_spec_goods_id
where date = 'some date'
group by goods_id, goods_name

Answer 1

使用 OrientDB，您可以选择图形数据库，通过直接使用顶点和边从数据库中删除 PK 和 FK 字段。我创建了一个包含代码和查询的示例:

create class Goods extends V;
create property Goods.id integer;
create property Goods.name String;

create property Goods.price double;
create property Goods.description String;

create class Order extends V;
create property Order.id integer;
create property Order.dateorder date;
create property Order.country String;

create class orderSpec extends E;
create property orderSpec.quantity integer;

insert into Goods(id,name,price,description) values (1,"oven",260.5,"kitchen");
insert into Goods(id,name,price,description) values (2,"television",300.7,"sitting room");
insert into Goods(id,name,price,description) values (3,"bed",100.3,"bedroom");
insert into Goods(id,name,price,description) values (4,"table",120.4,"dining room");

insert into Order(id,dateorder,country) values (1,"2013-04-12","UK");
insert into Order(id,dateorder,country) values (2,"2014-07-12","Germany");
insert into Order(id,dateorder,country) values (3,"2014-07-12","UK");

create edge orderSpec from (select from Order where id=1) to (select from Goods where id=1) set quantity = 2;
create edge orderSpec from (select from Order where id=1) to (select from Goods where id=2) set quantity = 3;
create edge orderSpec from (select from Order where id=1) to (select from Goods where id=3) set quantity = 2;
create edge orderSpec from (select from Order where id=2) to (select from Goods where id=2) set quantity = 1;
create edge orderSpec from (select from Order where id=3) to (select from Goods where id=2) set quantity = 1;
create edge orderSpec from (select from Order where id=3) to (select from Goods where id=4) set quantity = 2;

这是图表:

现在，您可以使用顶点之间的直接链接来查询数据库以获取所有信息，而无需连接操作。

按文章过滤:

按日期过滤: