• 这样递减的 sql 怎么写 at 2019年01月04日

    @wootaw 别闹 😄

  • 这样递减的 sql 怎么写 at 2019年01月02日

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

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

  • 一招秒杀 N+1 agg 问题 at 2018年11月02日

    PG 文档上都有,见 4.2.7 Aggregate Expression

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

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

  • SQL Style Guide at 2018年09月10日

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

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

  • SQL Style Guide at 2018年09月09日

    缩进方式很有意思。这个例子还是很考究的,比如 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;
    
  • Why Sometimes I Write WET Code at 2018年09月08日

    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,有相对完善的校验和类型系统,足够你干很多事情。

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

  • Elixir 的一些想法 at 2018年09月04日

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

  • 求一条 sql 解决分组问题 at 2018年09月04日

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

  • 嗯,就是延迟反序列化的过程,这里的 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.

  • Ruby 对 csv 文件进行统计 at 2018年08月04日

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

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

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

  • 老实说,不会。原因几点:

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

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

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

    aaa

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

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

    如果只是随便生成大量数据,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
    
  • @hooopofind_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 不会修复,并且迟早会被抛弃。

  • 不升级的理由是什么?

  • 求一个 MySQL 查询语句 at 2018年04月15日

    对每个 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 一直都是速度垫底的,也不是功能最强大的。

  • 定义数组可以不要括号? at 2018年02月24日

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

    a = 1, 2, 3
    
    p a # prints [1, 2, 3]
    

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

  • 请教一个 sql 的问题 at 2018年02月12日

    如果先排名后分页,你需要 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,然后在客户端的新版本里使用,冗余部分积累到下个大版本一起清理掉。