时间戳

首页 > 解决方案 > 我应该将每日用户计数存储在由 cron 作业更新的表中吗？

postgresql - 我应该将每日用户计数存储在由 cron 作业更新的表中吗？

问题描述

我需要每天user计算account每一天。Accounts可以创建和删除用户，所以我不认为一个简单的聚合函数会起作用，因为如果昨天account有 10 个users并且今天删除了一个user（所以他们users今天有 9 个），我仍然想知道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;

标签： postgresqlcron

解决方案

在考虑了更多之后，我决定不朝那个方向走，而是为人创建一个视图。

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 子句的物化视图进行授权。

推荐阅读