Блог

Мне нужно получать user количество для каждого account каждый день. Accounts может создавать и удалять пользователей, поэтому я не думаю, что простая агрегатная функция будет работать, поскольку, если вчера у an account было 10 users , а сегодня он удаляет один user (так что сегодня у них 9 users ), я все равно хотел бы знать, что users вчера было 10.

Мое текущее решение — иметь таблицу подсчета пользователей, которая обновляется заданием cron каждый день (см. Реализацию ниже). Было интересно, есть ли лучший способ?

 CREATE TABLE account (
  account_id bigint primary key
);

CREATE TABLE person (
  person_id    bigint primary key,
  account_id   bigint,
  created_at   timestamptz default now(),

  foreign key (account_id) references account
);

CREATE TABLE person_count (
  primary key (account_id, day),
  account_id   bigint,
  person_count integer,
  day          timestamptz,

  constraint day_formatted_dates_only check (day = date_trunc('day', day)),

  foreign key (account_id) references account
);

-- cron job that runs everyday
CREATE FUNCTION count_person_by_account()
RETURNS void AS $$

  INSERT INTO person_count
              (account_id, person_count, day)
       SELECT p.account_id, COUNT(*) person_count, date_trunc('day', now())
         FROM person p
     GROUP BY p.account_id

$$ LANGUAGE SQL
   VOLATILE;
  

Подумав об этом еще немного, я решил не идти в этом направлении и вместо этого создать представление для person.

 CREATE TABLE hidden_schema.person (
  person_id    bigint primary key,
  account_id   bigint,
  created_at   timestamptz default now(),
  is_deleted   boolean,

  foreign key (account_id) references account
);

CREATE VIEW visible_schema.person AS
  SELECT person_id, account_id, created_at
    FROM hidden_schema.person
   WHERE is_deleted = false;

-- Then I'll run the aggregate function on hidden_schema.person
  

Редактировать:
Подумав об этом еще больше, я понял, что более простым способом было бы не использовать представление, а просто добавить флаг is_delete (как указано выше) и использовать безопасность на уровне строк, чтобы отфильтровать его по моей роли клиента

 CREATE TABLE person (
  person_id    bigint primary key,
  account_id   bigint,
  created_at   timestamptz default now(),
  is_deleted   boolean,

  foreign key (account_id) references account
);

ALTER TABLE person ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_for_public_facing_client_role
           ON person
          FOR SELECT
           TO public_facing_client_role
        USING (is_deleted = false);
  

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

 SELECT day, account_id, count
  FROM (
  SELECT generate_series AS day
    FROM generate_series('2020-10-14'::timestamptz, '2020-10-20'::timestamptz, '1d'::interval)
) d LEFT JOIN LATERAL (
  SELECT account_id, COUNT(*)
    FROM person
   WHERE created_at <= d.day
GROUP BY account_id
) c ON TRUE
ORDER BY d.day, c.account_id;
  

Вы не можете запустить это как public_facing_client_role, потому что они не могут видеть удаленных пользователей. Для авторизации можно использовать материализованное представление с предложением where.