sql - 检索最佳结果的高效 SQL

Question

我有下表:

id, first_name, last_name, score, feedback, report_date
1,  Barry     , Smith    , 5    , positive, 2017-02-01
2,  John      , Smith    , 6    , negative, 2017-02-01
3,  Barry     , Smith    , 3    , negative, 2017-01-31
5,  John      , Smith    , 1    , positive, 2017-01-31

我想检索名字和姓氏的最新分数和反馈。

我尝试了以下查询:

select m.ad_id, m.country, m.score, m.feedback
from records m
inner join  (
   select first_name, last_name, max(report_date) as max_date
   from records
   where report_date <= '2017-02-19 15:00:00'
     and report_date >='2017-02-19 10:00:00'
     and score is not null
   GROUP BY first_name, last_name
   ) mp  on m.first_name = mp.first_name
         and m.last_name=mp.last_name
         and m.report_date = mp.max_date
         and m.report_date <= '2017-02-19 15:00:00'
         and m.report_date>='2017-02-19 10:00:00'
   ;

但是，在 18M 记录上需要几秒钟。

做一个简单的查询:

select m.fist_name, m.last_name, m.score, m.feedback
from records m
where m.report_date <= '2017-02-19 15:00:00'
  and m.report_date>='2017-02-19 10:00:00' 
  and score is not null

用时不到半秒，但它会返回 x 条记录，这会占用更多流量。如何使第一个查询更有效率？

Answer 1

区别于

select distinct on (first_name, last_name) *
from records
where
    report_date <= '2017-02-19 15:00:00' and report_date >='2017-02-19 10:00:00' 
    and score is not null
order by first_name, last_name, report_date desc