Введение
В данный момент я работаю над системой отчетности, которая должна аггрегировать большое количество данных, причем аггрегировать таким образом, что нужно строить сводные таблицы. Раньше задачи подобно рода я делал с помощью Pandas, но недавно я узнал что это можно делать сразу в PostgreSQL c помощью модуля tablefunc и я решил проверить что будет оптимальней для моего случая.
Тестирование
Я написал следующий тест для сравнения производительности:
import pandas
import sqlalchemy
from pandas.io import sql
from datetime import datetime
import logging
pg_engine = sqlalchemy.create_engine('postgresql://test:test@localhost/vts')
report_date = datetime(2018, 10, 6, 6, 0, 0)
def pandas_pivot(report_date):
df = pandas.read_sql_query("""SELECT code,
CASE WHEN delay >= 24 THEN 24 ELSE delay END as delay,
COUNT(*) AS packages_count
FROM (SELECT code, nph_id, min(delay) as delay
FROM (SELECT code,
nph_id,
floor(extract(epoch from (receive_date - create_date)) / 3600) as delay
FROM test_table
WHERE create_date >= %(today)s :: timestamp without time zone - interval '2 day'
AND create_date < %(today)s :: timestamp without time zone - interval '1 day') as grouped
GROUP BY code, nph_id) as t
GROUP BY code, CASE WHEN delay >= 24 THEN 24 ELSE delay END
order by code;""", pg_engine, params={"today": report_date})
df.loc[df.delay < 0, 'delay'] = 0
return df.pivot_table('packages_count', ['code'], ['delay'], aggfunc=sum)
def postgresql_pivot(report_date):
df = pandas.read_sql_query("""select *
from crosstab('
SELECT code,
CASE WHEN delay > 24 THEN 24 WHEN delay < 0 THEN 0 ELSE delay END as delay,
COUNT(*) AS packages_count
FROM (SELECT code, nph_id, min(delay) as delay
FROM (SELECT code,
nph_id,
floor(extract(epoch from (receive_date - create_date)) / 3600) as delay
FROM test_table
WHERE create_date >= ' || quote_literal(%(today)s) || ' :: timestamp without time zone - interval ''2 day''
AND create_date < ' || quote_literal(%(today)s) || ' :: timestamp without time zone - interval ''1 day'') as grouped
GROUP BY code, nph_id) as t
GROUP BY code, CASE WHEN delay > 24 THEN 24 WHEN delay < 0 THEN 0 ELSE delay END
order by code', 'select h from generate_series(0,24) h') as r(
code varchar,
delay_0 int,
delay_1 int,
delay_2 int,
delay_3 int,
delay_4 int,
delay_5 int,
delay_6 int,
delay_7 int,
delay_8 int,
delay_9 int,
delay_10 int,
delay_11 int,
delay_12 int,
delay_13 int,
delay_14 int,
delay_15 int,
delay_16 int,
delay_17 int,
delay_18 int,
delay_19 int,
delay_20 int,
delay_21 int,
delay_22 int,
delay_23 int,
delay_24 int)""", pg_engine, params={"today": report_date})
return df
В данных запросах мне надо понять размер задержки получения информации ко каждому коду в разрезе часов.
Анализ результатов
Для замера я запустил данные функции 6 раз, замерил их время выполнения на каждой итерации и получил следующие результаты:
| Параметры | pandas | postgresql |
|---|---|---|
| count | 6.00 | 6.00 |
| mean | 2.65 | 2.68 |
| std | 0.04 | 0.05 |
| min | 2.58 | 2.61 |
| 25% | 2.61 | 2.65 |
| 50% | 2.66 | 2.68 |
| 75% | 2.67 | 2.73 |
| max | 2.70 | 2.75 |
По результам видно, что особой разницы в быстродействии между этими способами практически отсутствует. Но так как трафик при аггрегации на стороне PostgreSQL меньше, я выбрал его.
Заключение
Как показал экперимент оба варианта по скорости работают одинаково. При построении сводной в postgresql вам должны быть известны все варианты значений поля, идущего в столбцы иначе запрос завершится ошибкой, в pandas же это знает за вас. Однако pandas ограничен оперативной памятью на вашем пк, тогда как размер данных для таблицы в постгрес не ограничен.
Также надо отметить что pandas умеет строить сводные со сложными столбцами (из нескольких полей) в postgresql я не разобрался как это сделать.