Neo4j多查询结果合并与订单时间维度统计技术咨询

如果三个查询返回相同结构的结果集（比如都包含id和数值字段），要将同一id的数值相加，可通过UNION ALL合并后聚合：

// 合并三个查询结果，按id求和
MATCH (n:TargetNode) WHERE n.condition1 = true // 第一个查询条件
RETURN n.id AS id, count(n) AS value
UNION ALL
MATCH (m:TargetNode) WHERE m.condition2 = true // 第二个查询条件
RETURN m.id AS id, count(m) AS value
UNION ALL
MATCH (p:TargetNode) WHERE p.condition3 = true // 第三个查询条件
RETURN p.id AS id, count(p) AS value
WITH id, sum(value) AS total_value
RETURN id, total_value

如果需要将三个查询的结果作为同一行的不同字段合并（比如每个查询对应一个独立统计项），可通过预存结果后关联：

// 分别获取三个查询结果，按id关联合并
WITH
  (MATCH (a:TargetNode) WHERE a.cond1 = true RETURN a.id AS id, count(a) AS val1) AS res1,
  (MATCH (b:TargetNode) WHERE b.cond2 = true RETURN b.id AS id, count(b) AS val2) AS res2,
  (MATCH (c:TargetNode) WHERE c.cond3 = true RETURN c.id AS id, count(c) AS val3) AS res3
UNWIND res1 AS r1
UNWIND res2 AS r2
UNWIND res3 AS r3
WHERE r1.id = r2.id AND r2.id = r3.id
RETURN r1.id AS id, r1.val1, r2.val2, r3.val3, r1.val1 + r2.val2 + r3.val3 AS total

假设使用支持标准SQL的数据库，通过COUNT(IF(...))结合时间函数实现统计：

SELECT
  product_id AS Product_Id,
  -- 当日订单数：时间戳转日期后等于今日
  COUNT(IF(DATE(FROM_UNIXTIME(order_created_date)) = CURDATE(), 1, NULL)) AS orders_today,
  -- 近5天订单数：日期在今日往前推4天范围内（含今日）
  COUNT(IF(DATE(FROM_UNIXTIME(order_created_date)) >= DATE_SUB(CURDATE(), INTERVAL 4 DAY), 1, NULL)) AS orders_last5days,
  -- 近30天订单数：日期在今日往前推29天范围内（含今日）
  COUNT(IF(DATE(FROM_UNIXTIME(order_created_date)) >= DATE_SUB(CURDATE(), INTERVAL 29 DAY), 1, NULL)) AS orders_last30days,
  -- 累计订单总数：统计所有行
  COUNT(*) AS total_orders_tilltoday
FROM
  your_order_table -- 替换为你的表名
GROUP BY
  product_id;

说明： #

• FROM_UNIXTIME(order_created_date)将时间戳转为日期时间格式，DATE()提取日期部分用于比较
• DATE_SUB(CURDATE(), INTERVAL N DAY)计算N天前的日期，确保统计范围包含今日在内的N天
• COUNT(IF(条件,1,NULL))仅统计满足条件的订单，NULL不会被COUNT计入

内容的提问来源于stack exchange，提问作者siri