检索PostgreSQL日期范围内的记录

Question

对于每个客户，我正在尝试检索最新submit_date的45天内的记录。

customer  submit_date   salary
      A   2019-12-31   10000
      B   2019-01-01   12000
      A   2017-11-02   11000
      A   2019-03-03   3000
      B   2019-03-04   5500
      C   2019-01-05   6750
      D   2019-02-06   12256
      E   2019-01-07   11345
      F   2019-01-08   12345

Answer 1

窗口函数进行救援：

SELECT customer, submit_date, salary
FROM (SELECT customer, submit_date, salary,
             max(submit_date) OVER (PARTITION BY customer) AS latest_date
      FROM thetable) AS q
WHERE submit_date >= latest_date - 45;

Answer 2

我倾向于尝试：

select t.*
from t
where t.submit_date >= (select max(t2.submit_date) - interval '45 day'
                        from t t2
                       );

我认为这可以很大程度上利用(submit_date)上的索引。

如果您希望与每个客户相关，请使用一个相关条款：

select t.*
from t
where t.submit_date >= (select max(t2.submit_date) - interval '45 day'
                        from t t2
                        where t2.customer = t.customer
                       );

这想要在(customer, submit_date)上进行索引。