Как использовать функцию `lttb` от timescaledb с кверисетом django?

У меня есть проект django, который хранит данные нескольких устройств в реальном времени. Для этого я использовал timescaledb, которая подходит для временных рядов. Timescale предлагает несколько гиперфункций, которые мне необходимо использовать (в частности lttb, которая используется для понижающей выборки данных).

Например, вот один из запросов, который я хочу выполнить:

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s) 
FROM core_devicedata where device_id=%s and timestamp between %s and %s))

Я могу получить результат этого запроса в виде набора raw query set с помощью:

for data in DeviceData.objects.raw(query):
    ...

Я уже пробовал сырые sql-запросы с помощью django. Они работают. Дело в том, что они не предлагают никаких функций фильтрации и упорядочивания, поскольку не возвращают фактический набор запросов. Вместо этого они возвращают сырой набор запросов. Я пытаюсь выполнить запрос, как показано ниже , используя только возможности djagno orm.

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s)

Есть ли какие-нибудь предложения? Если нет возможности сделать это с помощью django orm, пожалуйста, помогите мне написать модульный метод менеджера для этого? Например, я должен иметь возможность фильтровать или упорядочивать только то, что мне нужно.

Django или нет, но я полагаю, что вы можете вставлять его в строку. Давайте посмотрим на пример:

select lttb(time,price,5)->unnest() from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month';
              ?column?
------------------------------------
 ("2024-04-09 13:23:29+00",70431)
 ("2024-04-17 16:08:07+00",59714.9)
 ("2024-04-22 23:15:59+00",67228.5)
 ("2024-05-01 08:24:03+00",56534.5)
 ("2024-05-07 14:37:03+00",63190.8)
(5 rows)

Теперь включим символ и сгруппируем по нему.

tsdb=> select symbol, lttb(time,price,5)->unnest() from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month' group by 1;
 symbol  |              ?column?
---------+------------------------------------
 BTC/USD | ("2024-04-09 13:23:29+00",70431)
 BTC/USD | ("2024-04-17 16:08:07+00",59714.9)
 BTC/USD | ("2024-04-22 23:15:59+00",67228.5)
 BTC/USD | ("2024-05-01 08:24:03+00",56534.5)
 BTC/USD | ("2024-05-07 14:37:38+00",63266.6)
(5 rows)

Теперь давайте разложим значения на ().*

tsdb=> select symbol, (lttb(time,price,5)->unnest()).* from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month' group by 1;
 symbol  |          time          |  value
---------+------------------------+---------
 BTC/USD | 2024-04-09 13:23:29+00 |   70431
 BTC/USD | 2024-04-17 16:08:07+00 | 59714.9
 BTC/USD | 2024-04-22 23:15:59+00 | 67228.5
 BTC/USD | 2024-05-01 08:24:03+00 | 56534.5
 BTC/USD | 2024-05-07 14:37:38+00 | 63266.6

Мой первый совет заключается в том, что ORM поможет вам только в одном случае. Когда все начинает усложняться, лучше пойти по пути сырых запросов, иначе вы будете тратить больше времени на борьбу с ORM, чем на решение реальных бизнес-задач.

Вы можете добавить фильтрацию и упорядочивание в необработанный запрос с помощью предложения WHERE и предложения ORDER BY.

Вы можете писать пользовательские ORM SQL-функции с помощью выражений Func(). Примеры функций timescaledb, написанных для Django, в данном случае time_bucket, можно посмотреть в репо библиотеки django-timescale.

Вы могли бы определить функцию примерно так (это не работает):

class LTTB(Func):
    arity = 3
    function = "lttb"
    template = "(%(function)s(%(expressions)s)->unnest()).*"

Проблема в том, что пользовательские функции требуют output_field, так как unnest возвращает 2 дополнительных столбца, я не знаю, поддерживает ли это Django ORM.

Вернуться на верх