Как будет выглядеть схема для таблицы с отчетами за месяц?

Question

[Benderm]

Здравствуйте!

Есть несколько таблиц в postgresql, в которых содержатся по дням заказы, продажи и т.д. Даты в этих таблицах вида 2022-02-06.
Предполагается сделать таблицу в которой будет по месяцам содержаться подсчитанные данные из нескольких таблиц и также некоторые данные будут рассчитываться в коде по формулам и записываться в эту таблицу.

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

Как в такой таблице лучше всего сделать колонки для дат? Отдельная колонка под месяц и год или лучше сделать 2 колонки startDate и endDate в которых будут начальная дата периода и конечная (2022-02-01-2022-02-28)?

Для примера с месяцем и годом:

id (uuid) | orderedTotal (int) | soldAmountTotal (decimal) | month (int) | year (int)

Для примера с startDate и endDate:

id (uuid) | orderedTotal (int) | soldAmountTotal (decimal) | startDate (date) | endDate (date)

Comments

[キム ファイブプラス]

Как в такой таблице лучше всего сделать колонки для дат?

В дальнейшем, в зависимости от этих данных будет разный функционал у пользователей.

Также надо будет показывать данные по каждому пользователю в разных периодах.

или лучше сделать 2 колонки startDate и endDate в которых будут начальная дата периода и конечная

Answer 1

[Melkij]

create table aggregate_montly (
    user_id bigint not null references users(id),
    month date not null check (date_trunc('month', date_period) = date_period),
    orders_count bigint not null default 0,
    -- прочие предаггрегированные данные
    primary key (user_id, month)
);

Дату писать всегда как первое число месяца, check constraint гарантирует, что вы не запишете по ошибке данные за 8 февраля вместо месяца.
id выкинут за бессмысленностью. Тем более uuid.

Answer 2

[nApoBo3]

Мы у себя сделали, год, тип периода, период с начала года. Сейчас как тип периода используется день, неделя, месяц, квартал, год.

Comments

[nApoBo3]

Да, сурогатный id тоже используем, поскольку данные могут меняться и нужно обновить соответствующие бизнес процессы.