ruby-on-rails - PSQL：查找另一张发票在不到 8 个月内创建的发票

Question

Table: Invoice
user_id
created_at

目标是找到在不到 8 个月的另一张发票中创建的发票，这两张发票都属于同一用户。

示例：

Invoice 2: created_at: 2020-12-01 user_id: 1
Invoice 1: created_at: 2020-08-01 user_id: 1

Invoice 4: created_at: 2020-12-01 user_id: 2
Invoice 3: created_at: 2019-12-01 user_id: 2

Invoice 7: created_at: 2019-09-01 user_id: 3
Invoice 6: created_at: 2019-07-01 user_id: 3
Invoice 5: created_at: 2019-06-01 user_id: 3

查询应返回发票 2，因为在发票 1 之前不到 8 个月内创建了另一张发票（发票 1）。

查询还应返回发票 7，因为在发票 7 之前不到 8 个月内已创建了另一张发票（发票 6）。

查询还应返回发票 6，因为在发票 6 之前不到 8 个月内已创建了另一张发票（发票 5）。

至今：

Invoice.where(created_at: 8.months.ago..Time.now).group_by(&:user_id).select { |k, v| v.size > 1 }

select * from invoices where created_at BETWEEN ('2020-04-21 14:05:57.009849' AND '2020-12-21 14:05:57.009949')

我的方法的问题是它只能在过去 8 个月内找到，而不是一般情况下。并且它使用ruby+sql，只有SQL才能更好。

标签： ruby-on-railsrubypostgresqlpsql

Answer 1

这应该有效：

WITH cte AS (
    SELECT  user_id
        ,   invoice
        ,   created_at
        ,   LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) previous
    FROM    invoice
)
SELECT  *
FROM    cte
WHERE   AGE(created_at, previous) <= INTERVAL '8 months';