如何使用Ecto查询每月新增用户数及工作日、周末新增用户数

query =
  from(
    u in User,
    # 原查询的where u.user_id == ^user_id会限制仅查单个用户，统计全量新增请删除该行，如有其他过滤条件可自行保留
    group_by: [
      fragment("date_part(?,?)::int", "month", u.inserted_at)
      # 如果需要区分不同年份的同月数据，新增下面这行分组，同时select里也要加对应的年份字段
      # fragment("date_part(?,?)::int", "year", u.inserted_at)
    ],
    select: %{
      month: fragment("date_part(?,?)::int", "month", u.inserted_at),
      # 当月总新增用户数
      users: count(u.id),
      # 工作日新增：周1到周5，PostgreSQL中dow取值0=周日、1=周一、6=周六
      weekday: filter(count(u.id), fragment("extract(dow from ?) between 1 and 5", u.inserted_at)),
      # 周末新增：周六、周日
      weekend: filter(count(u.id), fragment("extract(dow from ?) in (0,6)", u.inserted_at))
    }
  )

• 上述查询返回的结构和你要求的预期格式完全匹配，直接执行即可得到对应结果。
• 如果你使用的不是PostgreSQL，需要调整周几判断的逻辑：
  • MySQL：用dayofweek(inserted_at)判断，取值1=周日、2=周一...7=周六，对应工作日判断为between 2 and 6，周末为in (1,7)
• 如果业务需要区分不同年份的同月数据，建议把年份也加入分组和返回字段，避免不同年份的同月统计数据合并。

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