如何在neo4j中将第二个查询和第三个查询的输出与第一个查询的输出相加

b5lpy0ml  于 2022-10-21  发布在  其他
关注(0)|答案(1)|浏览(125)

在一个表中,您有product_id和Order_Created_Date,这是一个日期时间戳字段。
现在我们需要的输出如下所示。
对于每个product_id,今天创建了多少订单,过去5天创建了多少订单,过去30天创建了多少订单,截至今天创建的订单总数

Product_Id orders_today orders_last5days orders_last30days total_orders_tilltoday
101             5              20              100                 250
102             7              27              150                 450
wkftcu5l

wkftcu5l1#

请注意,这些计数是累积的。因此,对于产品101,今天有5个订单包含在过去5天内,25个订单包含在最后30天内。
我对产品中的所有product_id进行了计数,然后调用子查询,分别从今天、5天和30天开始计数。注意,我使用了Dummy Count(例如:0作为ORDERS_TODAY),因为Union all要求所有列具有相同的名称。
最后,i合计(总和)每个产品ID。请注意,订单总数是重复的,因此不需要获取总数。

MATCH (n:Product)  
WITH n.product_id as Product_Id, count(n) as total_orders_tilltoday 
WITH Product_Id, total_orders_tilltoday ORDER BY Product_Id
CALL {
    WITH Product_Id
    OPTIONAL MATCH (n:Product {product_id: Product_Id}) 
    WHERE  duration.inDays(date(n.order_created_date), date()).days <= 30
    WITH n.product_id as Product_Id, count(n) as orders_last30days
    RETURN   0 as orders_today, 0 as orders_last5days, orders_last30days 
    UNION ALL
    WITH Product_Id
    OPTIONAL MATCH (n:Product {product_id: Product_Id}) 
    WHERE  duration.inDays(date(n.order_created_date), date()).days <= 5
    WITH n.product_id as Product_Id, count(n) as orders_last5days
    RETURN   0 as orders_today, orders_last5days, 0 as orders_last30days 
    UNION ALL
    WITH Product_Id
    OPTIONAL MATCH (n:Product {product_id: Product_Id}) 
    WHERE  duration.inDays(date(n.order_created_date), date()).days <= 1
    WITH n.product_id as Product_Id, count(n) as orders_today
    RETURN  orders_today, 0 as orders_last5days, 0 as orders_last30days  
}
RETURN Product_Id,sum(orders_today) as orders_today,sum(orders_last5days) as orders_last5days,sum(orders_last30days) as orders_last30days,total_orders_tilltoday)

使用3个产品ID的样本结果:

╒════════════╤══════════════╤══════════════════╤═══════════════════╤════════════════════════╕
│"Product_Id"│"orders_today"│"orders_last5days"│"orders_last30days"│"total_orders_tilltoday"│
╞════════════╪══════════════╪══════════════════╪═══════════════════╪════════════════════════╡
│101         │0             │0                 │2                  │2                       │
├────────────┼──────────────┼──────────────────┼───────────────────┼────────────────────────┤
│102         │0             │1                 │1                  │1                       │
├────────────┼──────────────┼──────────────────┼───────────────────┼────────────────────────┤
│103         │1             │1                 │1                  │1                       │
└────────────┴──────────────┴──────────────────┴───────────────────┴────────────────────────┘

相关问题