对于每个客户,我都会尝试检索最近一次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
发布于 2019-03-20 07:22:56
窗口函数提供了帮助:
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;
发布于 2019-03-20 07:27:59
我倾向于尝试:
select t.*
from t
where t.submit_date >= (select max(t2.submit_date) - interval '45 day'
from t t2
);
我认为这可以很好地利用(submit_date)
上的索引。
如果您希望与每个客户相关,请使用correlation子句:
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)
的索引。
https://stackoverflow.com/questions/55251339
复制相似问题