Neo4j CQL按日/周/月等时长统计新增成员数量的查询方法

以下是针对不同时长维度的CQL查询，核心逻辑是通过日期截断/分组统计各时段新增成员数，再计算占比并构造指定格式的结果列表：

按日统计 #

// 计算总新增成员数
MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH COUNT(p) AS total
// 按日期分组统计当日新增
MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH total, date(p.joinDate) AS joinDay, COUNT(p) AS dailyCount
ORDER BY joinDay
// 构造结果列表
RETURN COLLECT({
  index: toString(joinDay),
  ratio: toFloat(dailyCount)/total
}) AS stats,
SUM(dailyCount) AS totalMembers

按周统计 #

MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH COUNT(p) AS total
MATCH (p:Person)-[:PART_OF]->(c:Club)
// 截断日期到当周起始日（默认周一）作为分组依据
WITH total, date.truncate('week', date(p.joinDate)) AS joinWeek, COUNT(p) AS weeklyCount
ORDER BY joinWeek
RETURN COLLECT({
  index: date.format(joinWeek, "yyyy-'W'ww"), // 格式化为"2024-W01"样式
  ratio: toFloat(weeklyCount)/total
}) AS stats,
SUM(weeklyCount) AS totalMembers

按月统计 #

MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH COUNT(p) AS total
MATCH (p:Person)-[:PART_OF]->(c:Club)
// 截断日期到当月起始日
WITH total, date.truncate('month', date(p.joinDate)) AS joinMonth, COUNT(p) AS monthlyCount
ORDER BY joinMonth
RETURN COLLECT({
  index: date.format(joinMonth, "yyyy-MM"), // 格式化为"2024-03"样式
  ratio: toFloat(monthlyCount)/total
}) AS stats,
SUM(monthlyCount) AS totalMembers

按季度统计 #

MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH COUNT(p) AS total
MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH total, date(p.joinDate) AS joinDate, COUNT(p) AS quarterlyCount
// 计算季度起始日期（当年1/4/7/10月1日）
WITH total,
     date({year: date.year(joinDate), month: (date.quarter(joinDate)-1)*3 + 1, day: 1}) AS quarterStart,
     quarterlyCount
ORDER BY quarterStart
RETURN COLLECT({
  index: "Q"+toString(date.quarter(joinDate))+" "+toString(date.year(joinDate)), // 格式化为"Q1 2024"样式
  ratio: toFloat(quarterlyCount)/total
}) AS stats,
SUM(quarterlyCount) AS totalMembers

按年统计 #

MATCH (p:Person)-[:PART_OF]->(c:Club)
WITH COUNT(p) AS total
MATCH (p:Person)-[:PART_OF]->(c:Club)
// 截断日期到当年起始日
WITH total, date.truncate('year', date(p.joinDate)) AS joinYear, COUNT(p) AS yearlyCount
ORDER BY joinYear
RETURN COLLECT({
  index: toString(date.year(joinYear)), // 格式化为"2024"样式
  ratio: toFloat(yearlyCount)/total
}) AS stats,
SUM(yearlyCount) AS totalMembers

补充说明 #

• 若需针对单个俱乐部统计，在MATCH语句中添加俱乐部过滤条件，例如(c:Club {name: '足球俱乐部'})
• toFloat()用于避免整数除法导致占比为0，确保返回小数格式的比例值
• 可通过date.format()函数自定义index的显示格式，适配不同业务需求

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