如何用Lucid ORM按月份统计近一年用户注册量（PG/CockroachDB）

针对你的需求，结合PostgreSQL/CockroachDB的特性，可以通过Lucid ORM的selectRaw、groupByRaw和orderByRaw方法直接嵌入原生SQL片段来实现查询。以下是完整的实现方案：

完整查询代码（AdonisJS v5+） #

import User from 'App/Models/User'

const monthlyRegistrations = await User.query()
  // 格式化月份为"YYYY, Month"格式
  .selectRaw("to_char(date_trunc('month', created_at), 'YYYY, Month') AS created_month")
  // 统计每月注册用户数
  .selectRaw("COUNT(id) AS total_registrations")
  // 过滤近一年的数据（匹配你"近一年注册量"的需求，可按需移除）
  .whereRaw("created_at >= NOW() - INTERVAL '1 year'")
  // 按月份分组
  .groupByRaw("date_trunc('month', created_at)")
  // 按月份顺序排序
  .orderByRaw("date_trunc('month', created_at)")

代码说明 #

• selectRaw：用于定义包含数据库特定函数的查询字段，这里通过date_trunc将日期截断到月份级别，再用to_char格式化为指定的月份名称格式。
• groupByRaw：由于分组依据是date_trunc的计算结果而非普通字段，需要用该方法指定原生分组条件。
• orderByRaw：排序依据同样是日期截断后的结果，因此使用原生排序语句保证顺序正确。
• whereRaw：添加近一年数据的过滤条件，确保只统计最近12个月的注册量，不需要的话可直接删除该行。

AdonisJS v4兼容版本 #

如果使用AdonisJS v4，只需在查询末尾添加.fetch()获取结果：

const monthlyRegistrations = await User.query()
  .selectRaw("to_char(date_trunc('month', created_at), 'YYYY, Month') AS created_month")
  .selectRaw("COUNT(id) AS total_registrations")
  .whereRaw("created_at >= NOW() - INTERVAL '1 year'")
  .groupByRaw("date_trunc('month', created_at)")
  .orderByRaw("date_trunc('month', created_at)")
  .fetch()

兼容性说明 #

该实现完全适配PostgreSQL和CockroachDB，两个数据库均支持date_trunc和to_char函数，无需额外调整。

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