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

Question

我需要每天获取每个account的user计数。Accounts可以创建和删除用户，所以我认为一个简单的聚合函数不起作用，因为如果一个account昨天有10个users，而今天删除了一个user(所以他们今天有9个users(，我仍然想知道昨天有10个子users。

我目前的解决方案是拥有一个用户计数表，该表每天由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;

Answer 1

经过进一步思考，我决定不朝这个方向发展，而是为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子句的物化视图进行授权。