@wootaw 别闹

翻完评论才看明白什么意思。我觉得这个计算逻辑不适合用 SQL 做，因为它的关注点并非在集合，而在集合中的每条元素。用两个链表做计算（递归比较头节点）反而更容易，计算结果再保存进数据库就行。

如果有非常特殊的原因一定要在 SQL 里实现，可以看一下 lateral join 和 with recursive。

PG 文档上都有，见 4.2.7 Aggregate Expression

{} 跟 do ... end 的区别只在于跟前面的方法结合的优先级，简单来说 {} 会“紧贴”它前面的方法，而 do ... end 更加宽松。

我看楼主之前有过几个帖子讨论语言的设计优劣问题。但说实话纠结这些并没有太多意义，更多是结合个人偏好的主观问题。倒不如多了解下各种语法的客观差异，避免踩坑，然后总结出一套符合自己偏好的编程方式和风格干活。

确实 query plan 不同，感觉 CTE 更严格的按照每一步去划分优化的边界，而子查询作为一个整体查询有时候会优化得更多一点。所以我一般是测试时写 CTE 然后转到代码里再换成子查询。这样也更容易转换成 app 层的代码。因为 Elixir 的 Ecto 可以写出非常复杂的分析型查询，但目前还不支持 CTE。

Emacs 有个插件能帮你对齐，也是你说的 river 风格。不过 Emacs 的对齐功能基本都有点自恋 + 死不认错，人工调整后编辑器也会不停的帮你“纠正”过来。想调整规则得去研究类似 AST 的数据结构，所以我直接放弃了……

缩进方式很有意思。这个例子还是很考究的，比如 JOIN 其实是属于 FROM clause 的部分，算是 FROM 下一层的表达式，因此缩进在 FROM 的更右边，ON 下面换行的 AND 同理。

这种方式我尝试过一段时间（可能不是严格照着这个 styleguide 来的）。最大的感受是必须有编辑器提供自动格式化才好用，几乎没办法靠人工维护。尤其是写超过 20 行的话自己都不知道缩进对不对，每多加一行都可能导致之前写的部分重新缩进。

PostgreSQL 文档里有些 SQL 例子是按这种方式缩进的，但也有混用的。比如 window function 的一个例子，内层按分界缩进外层就顶头了：

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

DRY 的代码特点是“改了一个地方，所有相关的地方都被影响” 。有时候这是好事，另一些时候则是坏事。我觉得用 reason to change 来衡量代码是否需要可重用是个可行的做法。如果两段代码实现相同，但是为了不同的业务目的，那就让它重复。

另外也赞同接口设计比实现更重要。接口定义得良好，实现烂一点影响范围也有限，并且容易重构过来。个人感觉过度重视局部代码的精巧结构，以 DRY 为主要的衡量指标，甚至以实现来反过来影响接口设计是很多新手常犯的错误。

@chenge ROM 里借鉴了 Changeset 的概念：https://api.rom-rb.org/rom/ROM/Changeset.html

Rails 的 ActiveRecord 作为一个超级 model，在框架层面集成好用的功能于一身。这点跟 Ecto 在设计理念上有根本差异，脱离这点谈 Changeset 不大现实。毕竟 Changeset 的所有功能在 AR 里都有，只是如何组织，以及好不好用的问题。无端添加一个概念反而容易让人迷惑 -- 我该用那种方式修改数据？这点在 Ecto 里并不存在，因为 Changeset 是唯一修改“模型”数据的方式。

Changeset 另一个好处是容易脱离数据模型再开一层抽象，比如做外部数据的校验。这方面其实 Ruby 也早就探索过，不过是叫做 Form Object。虽然 ActiveModel 不太给力，但 Ruby 社区的轮子也不少，不喜欢 AR 的可以去用 dry-rb，有相对完善的校验和类型系统，足够你干很多事情。

名词是新名词，但内部概念还真不是新概念。

@chenge 建议从 Ecto + PostgreSQL 搭配入手。Ecto 对它的支持最好。

没看懂你要的计算逻辑，好歹也表达清楚点吧？

嗯，就是延迟反序列化的过程，这里的 pg 也是说的 Ruby 库，跟 PostgreSQL 本身没什么关系。

PG::Result stores values received from the database internally in a compact memory format. When the value is accessed, it is converted to a ruby string or casted to another ruby object.

不考虑数据量问题的话，就一个简单的 reduce：

require 'csv'

CSV.foreach("data.csv", headers: true).reduce({}) do |acc, row|
  priority = row["优先级"]
  acc[priority] = (acc[priority] || 0) + 1
  acc
end

如果量大并且需要跟其他表结合做统计，导入数据库是更合适的选择。除了用 COPY 外 Foreign Data Wrapper 也可以考虑，简单粗暴。

老实说，不会。原因几点：

1. 越是受众面不广的技术越要跟国外的一手资料。一般来说订阅 Elixir Radar 并定期逛逛 Elixir Forum 足矣。而且像 @Rei 所说 周边的书也已经不少了。

2. 国内资料太少，考虑到实效性就更少了。因此我一般不会选择看中文的（即使搜索到）。我想近几年愿意吃螃蟹学习 Elixir 的人大多也是这个习惯。所以中文资料就更难有市场。这确实是个恶性循环，但任何圈子的形成都是需要时间的。大部分时候我们是要解决问题，那就得用更有效率的获取知识的方式。

@jmmbite 本地试了下你的 SQL，总时间 3 分半。SSD

我感觉这样已经是最快的方案了，因为执行过程全在 PostgreSQL 内部。其他那种要到数据库外做一圈交互的方式应该性能更低。对比下来你的问题应该更多的在于硬件，所以升级机器吧。

如果要从某个数据源导入，可以生成 csv 文件然后 COPY ... FROM ，INSERT 能快到哪去……

如果只是随便生成大量数据，generate_series 函数了解一下。

elixir 药丸，赶紧 go 到 go 那边去

所以呢我说的其实是 query builder 的抽象能力。因为大部分 ORM 都有个 query builder 因此它也算是 ORM 的一部分。如果完全没有抽象的话对查询的共用部分就只能拼接 SQL 字符串了。mini_sql 的 builder.where 看起来已经够用了。我再举个 Ecto（Elixir 语言的 data wrapper）的例子，实现 Sam 文中提到的完整 SQL：

from(
  ftl in TopicLink,
  left_join: ft in assoc(ftl, :link_topic),
  left_join: c in assoc(ft, :category),
  group_by: [ftl.url, ft.title, ftl.title, ftl.link_topic_id, ftl.reflection, ftl.internal, ftl.domain],
  order_by: [desc: sum(ftl.clicks), desc: count(ftl.id)],
  limit: 50,
  select: %{
    url: ftl.url,
    title: coalesce(ft.title, ftl.title),
    link_topic_id: ftl.link_topic_id,
    reflection: ftl.reflection,
    internal: ftl.internal,
    domain: ftl.domain,
    user_id: min(ftl.user_id),
    clicks: sum(clicks)
  }
)
|> where([ftl, ...], ftl.topic_id == ^topic_id)
|> where([_, ft, ...], is_nil(ft.deleted_at))
|> where([ftl, ...], not (ilike(ftl.url, "%.png") or ilike(ftl.url, "%.jpg") or ilike(ftl.url, "%.gif")))
|> where([_, ft, ...], coalesce(ft.archetype, "regular") != ^Archetype.private_message)
|> secure_category(guardian.secure_category_ids)
|> Repo.all()

拿它举例并不是抬杠或者吹 Ecto 如何，我相信以 Ruby 的灵活性可以做到同样的事情（只是感觉没多少人觉得它很重要）。只是说明语言层面提供的抽象可以用来更好的组织和重用代码。这点我个人还是觉得很重要的。毕竟 SQL 作为声明式的语言不善于表达过程式的代码，而生成 query 的过程恰好是过程式的，应用层的语言擅长此道。

另外拿我最近做的一个统计做例子：

query_1 =
  FunnelItem
  |> filter_by_subject(subject_id)
  |> filter_by_type(:fam)
  |> score_btw(3, 4)

query_2 =
  FunnelItem
  |> filter_by_subject(subject_id)
  |> filter_by_type(:ove)
  |> score_btw(6, 7)

query_3 =
  FunnelItem
  |> filter_by_subject(subject_id)
  |> filter_by_type(:puc)
  |> score_eq(4)

[query_1, query_2, query_3]
|> join_queries()
|> stat_by_filters(filter_key, filter_value_ids)

上面的例子把部分 where 条件抽成了共用的函数，再自定义了一些函数来组合查询，比如 join_queries ：

def join_queries(queries) do
  Enum.reduce(queries, fn query, acc ->
    from(
      [f1, ...] in acc,
      join: ft in ^query,
      on: ft.sample_id == f1.sample_id and ft.subject_id == f1.subject_id
    )
  end)
end

@hooopo 用 find_by_sql 可以对付注入，就是不大好看，必须用某个具体的 model 来调用该方法。加上结果是该 model 的实例加上从 SELECT 部分动态生成的属性就非常奇怪了。这个问题可以通过建立一个专门的 stat model 来解决，避免尴尬。另外 sanitize_sql_xxx 的那批方法应该也可以解决注入问题（没试过），不过用起来就非常麻烦了。

虽然统计不是 ORM 需要解决的问题，一个是 OLAP 一个是 OLTP。但有时候两者都需要共用一些查询条件和表关联，而且有时候相似的统计 SQL 也会有些代码碎片是重复的，这时候就又回到如何重用的问题上来了。query builder 本身用在构造 SQL 上是合适的，问题是 AR 的 query builder 抽象程度还不够，经常写成了大批的 SQL 片段，也就是你说的 SQL + Rails 的方式。

前后端分离后的前端更应该当作一个独立的应用程序，而不是某个大产品的附属。如果再考虑离线和数据同步的情况，也可以说是一种分布式系统了。因此我觉得考虑是否分离的最大因素，就是前端是否复杂到了需要做成一个独立的应用程序。

本质上这是衡量软件复杂度和抽象的问题。一方面它取决于实际需求。比如 MVC 分层是 web 框架最佳实践？做个简单的网站也许并不需要，Sinatra 那种在 block 里写实现的方式也许更快；jQuery 难以维护？如果你的需求只是网页上加少数动态组件可能它更简单。另一方面也在于开发者自身的技术背景。同样做个 SPA 在专业前端和顺带写点网页的后端眼里复杂度可能完全不同，因此做出的决策也不同，但结果可能殊途同归。

如果是仅仅是删除旧数据，为什么不用 SQL？

1. 如果不是很老的 mac 基本可以放心。我 12 年的 macbook 用到去年，一直都是升级到最新的版本没问题。不过周围也有少数出意外的（基本是比我更老的），备份一次数据再做比较好。
2. 这是既定事实，无法改变。不如调整自己的心态接受。

作为软件开发者，我觉得不更新是没法接受的。这意味着没有新特性，bug 不会修复，并且迟早会被抛弃。

不升级的理由是什么？

对每个 order 而言，找它的 package 的数量和状态为 4 的 package 的数量相等的就行了。

SELECT o.*
FROM orders o
JOIN packages p ON p.order_id = o.id
WHERE o.state = 4
GROUP BY o.id
HAVING COUNT(p.id) = SUM(IF(p.state = 4, 1, 0))
;

为了更贴近 SQL 标准（也方便以后弃坑 MySQL），把 if 换成 case 更好：SUM(CASE WHEN p.state = 4 THEN 1 ELSE 0 END) 。算是补充炮哥言简意赅的回答吧。

分两套，说下原因：

1. API 并不是把原本渲染网页的 controller 数据改成渲染 JSON 就完了，它的职责更加单一化
2. 如果主要逻辑在 service object (model 也行) 里，controller 分开很简单。把 controller 想像成处理 HTTP 接口的层就行。

应用层面的唯一性验证因为 race condition 不能完全保证数据唯一（虽然大部分情况下够用）。下面的链接跟 Rails 无关，不过足以解释你的问题：

https://hexdocs.pm/ecto/Ecto.Changeset.html#unsafe_validate_unique/4 https://hexdocs.pm/ecto/Ecto.Changeset.html#unique_constraint/3

支持 JSON:API 规范的库里 AMS 一直都是速度垫底的，也不是功能最强大的。

这是一个冷知识。虽然看起来不可思议，但确实可以，见 Implicit Array Assignment

a = 1, 2, 3

p a # prints [1, 2, 3]

不过谁真在项目里这么写肯定拉出去砍了。太不直观，容易误读。

如果先排名后分页，你需要 window function：

select
  user_id,
  score,
  dense_rank() over (order by score desc) as rank
from exam_result
order by score desc, id asc
limit 4
offset 2;

如果先分页后排名，把上述查询改成子查询就行：

select *, dense_rank() over (order by score desc) as rank
from (
  -- select, limit, offset
) t

或者用分页完成后在 Ruby 层面算。

https://www.postgresql.org/docs/current/static/tutorial-window.html https://www.postgresql.org/docs/current/static/functions-window.html

上面已经说得差不多了，我补充一点。对于一些小修改不需要频繁发布大版本，只要不改 payload 属性名称和值类型一般不会破坏客户端应用。可以新增属性或 API，然后在客户端的新版本里使用，冗余部分积累到下个大版本一起清理掉。