Оптимизированные запросы в PostgreSQL

Question

Предположим, у вас есть таблица с именем tracker со следующими записями.

issue_id  |  ingest_date         |  verb,status
10         2015-01-24 00:00:00    1,1
10         2015-01-25 00:00:00    2,2
10         2015-01-26 00:00:00    2,3
10         2015-01-27 00:00:00    3,4
11         2015-01-10 00:00:00    1,3
11         2015-01-11 00:00:00    2,4

Мне нужны следующие результаты

10         2015-01-26 00:00:00    2,3
11         2015-01-11 00:00:00    2,4

Я пробую этот запрос

select * 
from etl_change_fact 
where ingest_date = (select max(ingest_date) 
                     from etl_change_fact);

Тем не менее, это дает мне только

10    2015-01-26 00:00:00    2,3

эта запись.

Но я хочу, чтобы все уникальные записи (change_id) с

(а) max(ingest_date) И

(b) приоритет столбцов глагола (2 - первый предпочтительный,1 - второй предпочтительный,3 - последний предпочтительный)

Следовательно, мне нужны следующие результаты

10    2015-01-26 00:00:00    2,3
11    2015-01-11 00:00:00    2,4

Пожалуйста, помогите мне эффективно запросить его.

PS: я не собираюсь индексировать ingest_date, потому что я собираюсь установить его как "ключ распределения" в настройке распределенных вычислений. Я новичок в хранилище данных и запросов.

Поэтому, пожалуйста, помогите мне с оптимизированным способом поразить мою базу данных размером с ТБ.

Answer 1

Это типичная проблема "наибольшие числа групп". Если вы будете искать этот тег здесь, вы получите множество решений, включая MySQL.

Для Postgres самый быстрый способ сделать это - использовать distinct on (который является проприетарным расширением Postgres для языка SQL)

select distinct on (issue_id) issue_id, ingest_date, verb, status
from etl_change_fact
order by issue_id, 
         case verb 
            when 2 then 1 
            when 1 then 2
            else 3
         end, ingest_date desc;

Вы можете улучшить свой исходный запрос, чтобы использовать связанный подзапрос для достижения того же:

select f1.* 
from etl_change_fact f1
where f1.ingest_date = (select max(f2.ingest_date) 
                        from etl_change_fact f2
                        where f1.issue_id = f2.issue_id);

---

редактировать

Для устаревшей и неподдерживаемой версии Postgres вы, вероятно, можете использовать что-то вроде этого:

select f1.* 
from etl_change_fact f1
where f1.ingest_date = (select f2.ingest_date
                        from etl_change_fact f2
                        where f1.issue_id = f2.issue_id
                        order by case verb 
                                  when 2 then 1 
                                  when 1 then 2
                                  else 3
                              end, ingest_date desc
                        limit 1);

Пример SQLFiddle: http://sqlfiddle.com/