Как правильно составить запрос для взятия первого значения из группы?

Question

[Владимир Скибин]

Есть таблица

timestamp                        | data
-----------------------------------------------------------------
2017-12-10 10:33:19.563241+00    | {"foo":900}
2017-12-11 10:34:19.563241+00    | {"foo":1800}
2017-12-11 10:35:19.563241+00    | {"foo":1800,"bar":400}
2017-12-11 10:36:19.563241+00    | {"foo":2400,"bar":400}

Из нее надо взять последнее значение столбца data для каждого дня, т.е. чтобы получилось

day                       | data
--------------------------------------------------------
2017-12-11 00:00:00+00    | {"foo":2400,"bar":400}
2017-12-10 00:00:00+00    | {"foo":900}

Понимаю, что нужно использовать оконные функции, но для типа json нет функции сравнения
т.е для запроса вида

SELECT DISTINCT "timestamp", date_trunc('day', "timestamp") AS "day", FIRST_VALUE("data") OVER (PARTITION BY "timestamp")
FROM "table"
ORDER BY "timestamp" DESC

получаю ошибку не удалось найти оператор равенства для типа json
Как такое все таки решить?
Система: PostgreSQL 9.5 Win 7

Answer 1

[Melkij]

Ну а когда станет скучно перебирать всю таблицу - обращайтесь :-)

Postgresql 9.4 и выше, выбор по известному диапазону дат через index scan по timestamp полю:

select day, data 
from generate_series('2017-12-10', '2017-12-12', interval '1 day') as day, 
lateral (
    select data from tablename 
    where "timestamp" between day and day + interval '1 day' 
    order by "timestamp" desc limit 1
) ljd;

Все дни из таблицы по индексу:

with recursive t as (
(select "timestamp"::date as day, data from tablename order by "timestamp" desc limit 1)
union all
select bpt.* from t, lateral (
select "timestamp"::date as day, data from tablename where "timestamp" < t.day order by "timestamp" desc limit 1
) as bpt
)
select * from t;

Используя loose index scan

Comments

[Владимир Скибин]

Melkij, никогда не использовал recursive
Спасибо за наводку, позже перепишу через рекурсивность )

[Владимир Скибин]

Melkij, хотя план выполнения запроса выглядит как то не утешающе

по сравнению с "полным перебором"

Возможно в дальнейшем это будет лучше

[Melkij]

По вашему плану очевидно, что вы перенесли запрос в свою исходную задачу некорректно. У вас есть некий фильтр country, отсутствующий в постановке вопроса и напрочь ломающий весь механизм loose index scan.

[Melkij]

На сколько я по плану представляю полученный запрос и задачу - вам необходим индекс using btree(country, "timestamp")

[Владимир Скибин]

Melkij, Ну возможно больше данных повлияло бы на конкретизацию ответа. Но насколько я столкнулся с проблемой - настолько и поставил вопрос. Я не претендую на то, чтобы называть Ваше решение неэффективным, возможно я его просто не умею готовить :)
А по запросу - это только малая часть всей постановки, там еще несколько JOIN и UNION еще к этому всему делу. В итоге все в оконцовке будет прогоняться по тестированию с намного большим количеством данных для построения нужных индексов.
Главное что Ваш совет по поводу рекурсивных запросов смог повлиять на другой запрос, который я смог улучшить, так что Спасибо Вам все равно причитается

[Melkij]

Вот loose index scan вручную делать надо только с пониманием, что, где и как именно хотим достать и какие исходя из этого индексы требуются. Т.е. сначала решаем, по каким индексам будет запрос работать хорошо и потом пишем запрос именно для этого поведения, а не как обычно, когда сначала пишется запрос для получения нужных данных и потом смотрим на реальных данные какие для него нужны индексы.
Обычно в такие дебри залезаем в попытке ускорить то что уже есть и где возможно переписать запрос радикально. Без индекса, под который запрос строился - результат наверняка будет крайне плачевным. Рекурсивные запросы вообще неплохо могут выстрелить в ногу.

Кстати, вот презентация по нескольким переосмыслениям запросов от моего коллеги и руководителя: https://pgday.ru/presentation/232/5964945ea4142.pdf (запись доклада конференции в свободном доступе к сожалению не опубликовано)

[Владимир Скибин]

Melkij, Спасибо, буду наматывать на ус

Answer 2

[azio]

Без дополнительных условий задачи можно так

SELECT
  date_trunc('day', sub."timestamp") AS "day",
  sub."data"
FROM (SELECT
        "timestamp",
        "data",
        row_number() OVER ( PARTITION BY date_trunc('day', "timestamp")
          ORDER BY "timestamp" DESC ) AS rownum
      FROM "table") sub
WHERE sub.rownum = 1

Только следует помнить, что этот вариант переберет все записи в таблице, поэтому может оказаться неприменимым для больших таблиц

Comments

[Владимир Скибин]

azio, Таблица как раз планируется для биллинга и врядли маленькая получится, но то что напомнили за row_number - Спасибо

[Владимир Скибин]

azio, Надо будет потестировать Ваше решение и то, что я пока что поставил

SELECT 
  DISTINCT date_trunc('day', "s"."timestamp") AS "day", 
  FIRST_VALUE("s"."data"::jsonb) OVER (PARTITION BY date_trunc('day', "s"."timestamp")) AS "dat"
FROM 
  (SELECT "data", "timestamp" FROM "table" ORDER BY "timestamp" DESC) AS "s"
ORDER BY "day" DESC

[azio]

Владимир Скибин, у Вас тоже нет дополнительных условий на выборку, поэтому будет перебираться вся таблица. Может для текущей задачи это и не так страшно, потому что скорее всего Вам не нужно будет постоянно перебирать все данные от "начала времен". Если речь идет о биллинге, то вероятно, нужно будет смотреть данные за какой-то период, например, за месяц. В таком случае можно будет добавить индекс на "timestamp" и в подзапросе sub накладывать дополнительный фильтр where "timestamp" between :a and :b. В этом случае выборка будет более эффективной.

[Владимир Скибин]

azio, Да, индекс и ограничение по времени есть, я не стал это вставлять в вопрос, поскольку большой роли в логике не играет

Answer 3

[Владимир Скибин]

Как одно из решений - это преобразование json в jsonb и с ним уже работать
Если есть другие варианты - было бы неплохо

Answer 4

[d-stream]

как извращенный, но рабочий вариант:

select 
*
from table 
inner join (
select 
dt=max(timestamp) 
from table
group by date_trunc('day', "timestamp")  -- под рукой mssql - там юзал cast(... as date)
) as t on t.dt=table.timestamp

Comments

[Владимир Скибин]

d-stream, Спасибо
все бы ничего, если бы timestamp был бы уникальный. На данном примере он таковой, но в теории может повториться и запрос может не выдать корректный результат

[d-stream]

Естественно в ситуациях совпадающего таймстампа - будет кака... ибо в том или ином виде нужен uid (который можно при желании "навесить")

[d-stream]

Владимир Скибин, синхронно написали -)