Rails中基于季度值查询：获取季度支出下滑的用户

核心思路 #

先算出当前季度和上一季度的时间范围，再按用户分组统计两个季度的支出总和，最后筛选出「当前季度总和 < 上一季度总和」的用户。结合你已有的users.requested_users集合，直接基于这个集合做关联查询即可，避免N+1性能问题。

步骤1：定义季度时间范围 #

用Rails自带的日期方法快速获取两个季度的起止日期：

# 当前季度起止日期
current_quarter_start = Date.today.beginning_of_quarter
current_quarter_end = Date.today.end_of_quarter

# 上一季度起止日期
last_quarter_start = Date.today.last_quarter.beginning_of_quarter
last_quarter_end = Date.today.last_quarter.end_of_quarter

步骤2：基于现有集合筛选用户 #

通过left_joins关联支出表，用CASE语句分别统计两个季度的支出总和，最后用having子句筛选符合条件的用户：

users = users.requested_users
            .left_joins(:expenditures)
            .select(
              "users.*",
              # 统计上一季度总支出
              "SUM(CASE WHEN expenditures.entry_date BETWEEN ? AND ? THEN expenditures.amount ELSE 0 END) AS last_quarter_total",
              # 统计当前季度总支出
              "SUM(CASE WHEN expenditures.entry_date BETWEEN ? AND ? THEN expenditures.amount ELSE 0 END) AS current_quarter_total"
            )
            .group("users.id")
            # 筛选当前季度支出更少的用户
            .having("current_quarter_total < last_quarter_total")
            # 绑定参数避免SQL注入
            .bind(last_quarter_start, last_quarter_end, current_quarter_start, current_quarter_end)

可选：封装成模型Scope复用 #

如果需要多次使用这个筛选逻辑，可封装成User模型的Scope，简化调用：
在user.rb中添加：

scope :spent_less_current_quarter_than_last, -> {
  current_quarter_start = Date.today.beginning_of_quarter
  current_quarter_end = Date.today.end_of_quarter
  last_quarter_start = Date.today.last_quarter.beginning_of_quarter
  last_quarter_end = Date.today.last_quarter.end_of_quarter

  left_joins(:expenditures)
    .select(
      "users.*",
      "SUM(CASE WHEN expenditures.entry_date BETWEEN ? AND ? THEN expenditures.amount ELSE 0 END) AS last_quarter_total",
      "SUM(CASE WHEN expenditures.entry_date BETWEEN ? AND ? THEN expenditures.amount ELSE 0 END) AS current_quarter_total"
    )
    .group("users.id")
    .having("current_quarter_total < last_quarter_total")
    .bind(last_quarter_start, last_quarter_end, current_quarter_start, current_quarter_end)
}

之后调用就非常简洁：

users = User.all.requested_users.spent_less_current_quarter_than_last

补充说明 #

• 用left_joins而非joins：确保没有任何支出记录的用户也会被纳入统计（但这类用户两个季度总和都是0，不会被筛选出来）
• 参数绑定：严格遵循Rails安全规范，避免SQL注入风险
• 无支出场景处理：某一季度没有支出时，总和按0计算，比如上一季度有支出、当前季度无支出的用户会被正常筛选出来

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