Ecto查询：如何同时实现分组求和与获取全局总和？

我们有一个包含非唯一date字段的数据表，对应的Ecto Schema定义如下：

defmodule Record do
  field(:plays, :integer)
  field(:date, :date) 
end

需要编写Ecto查询，同时得到按date分组的plays字段求和值，以及所有记录的plays字段全局总和。现有部分查询代码，需补充全局总和的实现：

query =
  Record
  |> group_by([r], [r.date])
  |> select([r], %{
    date: r.date, 
    total_plays_per_date: fragment("coalesce(sum(?), 0)", r.plays),
    overall_total_plays: # Sum of all plays from all records
  })

两种可行的实现方法 #

方法1：子查询计算全局总和

先通过子查询算出所有记录的plays总和，再将其作为常量带入分组查询，每个分组结果都会附带这个全局值：

# 先定义子查询计算全局总和
overall_total_query = from r in Record, select: coalesce(sum(r.plays), 0)

query =
  Record
  |> group_by([r], r.date)
  |> select([r], %{
    date: r.date,
    total_plays_per_date: fragment("coalesce(sum(?), 0)", r.plays),
    overall_total_plays: ^subquery(overall_total_query)
  })

方法2：使用窗口函数

利用SQL窗口函数SUM() OVER ()直接计算全局总和，无需额外子查询，代码更简洁：

query =
  Record
  |> group_by([r], r.date)
  |> select([r], %{
    date: r.date,
    total_plays_per_date: fragment("coalesce(sum(?), 0)", r.plays),
    overall_total_plays: fragment("coalesce(sum(?) OVER (), 0)", r.plays)
  })

两种方法对比 #

• 子查询方式：全局总和仅计算一次，在数据量较大时性能更优。
• 窗口函数方式：无需额外定义子查询，代码更紧凑，适合大多数常规场景。

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