使用一个带有优化的查询获取天、月、年、生存期的总记录

Question

我有一个运行7.4的Postgres DB（是的，我们正在升级中）

我有四个单独的查询来获得每日、每月、每年和终身记录计数

SELECT COUNT(field)
FROM database
WHERE date_field
    BETWEEN DATE_TRUNC('DAY' LOCALTIMESTAMP) 
    AND DATE_TRUNC('DAY' LOCALTIMESTAMP) + INTERVAL '1 DAY'

对于Month，只需将查询中的单词DAY替换为MONTH，依此类推。

寻找如何通过一个查询获得所有所需结果的想法，以及建议的任何优化。

提前感谢！

注：date_field是不带时区的时间戳

更新：

很抱歉，我确实过滤掉了带有额外查询约束的记录，只是想给出日期字段比较的要点。很抱歉造成任何混淆

Answer 1

我有一些想法，可以使用准备好的语句和简单的统计信息（record_count_t）表：

-- DROP TABLE IF EXISTS record_count_t;
-- DEALLOCATE record_count;
-- DROP FUNCTION updateRecordCounts();
CREATE TABLE record_count_t (type char, count bigint);
INSERT INTO record_count_t (type) VALUES ('d'), ('m'), ('y'), ('l');
PREPARE record_count (text) AS
UPDATE record_count_t SET count =
(SELECT COUNT(field)
FROM database
WHERE
CASE WHEN $1 <> 'l' THEN
    DATE_TRUNC($1, date_field) = DATE_TRUNC($1, LOCALTIMESTAMP)
ELSE TRUE END)
WHERE type = $1;
CREATE FUNCTION updateRecordCounts() RETURNS void AS
$$
    EXECUTE record_count('d');
    EXECUTE record_count('m');
    EXECUTE record_count('y');
    EXECUTE record_count('l');
$$
LANGUAGE SQL;
SELECT updateRecordCounts();
SELECT type,count FROM record_count_t;

每当需要更新统计信息时，请使用updateRecordCounts（）函数。

Answer 2

我想这是不可能进一步优化的。

如果你正在收集每日/每月/每年的统计数据，正如我假设你正在做的那样，一个选项（当然是在升级后）是with语句和相关联接，例如：

with daily_stats as (
(what you posted)
),
monthly_stats as (
(what you posted monthly)
),
etc.
select daily_stats.stats,
       monthly_stats.stats,
       etc.
stats
left join yearly_stats on ...
left join monthly_stats on ...
left join daily_stats on ...

然而，这实际上不如在生产环境中单独运行每个查询执行得好，因为您将在数据库中引入左联接，这在中间件中也可以很好地完成（即，显示每日、每月、每年，最后显示生存期统计数据）。（如果不是更好的话，因为你将避免全表扫描。）

通过保持原样，您将节省宝贵的数据库资源来处理实际数据的读写操作。这种权衡（减少数据库和应用程序之间的网络流量）几乎肯定是不值得的

哎呀！不要这样！！！不是因为你不能按照你的要求去做，而是因为你可能不应该这样做。我猜您在示例中使用date_field的原因是因为您将date_field附加到用户或其他一些元数据上。

想想看：您要求PostgreSQL扫描与给定用户相关的100%记录。除非这是一次性操作，否则您几乎肯定不想这样做。如果这是一次性操作，并且您计划将此值缓存为元数据，那么谁关心优化呢？空间很便宜，可以节省大量的执行时间。

您应该为每个用户（或其他任何用户）添加4个元数据字段，以帮助汇总数据。你有两个选项，我会让你弄清楚如何使用它，这样你就可以保留历史计数，但这里有一个简单的版本：

CREATE TABLE user_counts_only_keep_current (
  user_id , -- Your user_id
  lifetime INT DEFAULT 0,
  yearly INT DEFAULT 0,
  monthly INT DEFAULT 0,
  daily INT DEFAULT 0,
  last_update_utc TIMESTAMP WITH  TIME ZONE,
  FOREIGN KEY(user_id) REFERENCES "user"(id)
);
CREATE UNIQUE INDEX this_tbl_user_id_udx ON user_counts_only_keep_current(user_id);

设置一些存储过程，如果last_update_utc与NOW()中的当前日期不匹配，则将各个列清零。你可以从这里获得创意，但像这样不断增加的记录将是一条路。

任何关系数据库中时间序列数据的处理都需要特殊的处理和维护。如果您想要良好的时态数据管理，请查看PostgreSQL的表继承。。。。但实际上，不要对你的应用程序做任何你要做的事情，因为这几乎肯定会导致糟糕的事情（tm）。