如何DRY up一个PostgreSQL查询

Question

我不经常写原始SQL，但最近写的时候，我忍不住认为这个查询不是很DRY。有没有办法把它拧紧？

SELECT
COUNT(*) as "Users", DATE_TRUNC('day', created_at) AS "Date"
FROM
users
WHERE created_at > now() - interval '1 year'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY DATE_TRUNC('day', created_at);

目前返回：

Users  Date
175    2019-10-01 00:00:00
54     2019-10-02 00:00:00
142    2019-10-03 00:00:00
...

我想要的是按created_at日期对所有新用户进行分组，但只能追溯到一年前(或我们选择的任意日期(。不确定DATE_TRUNC是解决这一问题的最佳方式。更确切地说，重复3次可能不会。

Answer 1

您可以在group by和order by中使用列别名。

SELECT
count(*) as "Users",
date_trunc('day', created_at) AS "Date"
FROM
users
WHERE
created_at > now() - interval '1 year'
GROUP BY "Date"
ORDER BY "Date"

Answer 2

date_trunc()是将时间戳截断为日期的正确工具。另一种选择是铸造。对于group by和order by子句，可以使用位置参数或列别名。

因此：

SELECT COUNT(*) as cnt_users, created_at::date AS created_date
FROM users
WHERE created_at > current_date - interval '1 year'
GROUP BY created_date  -- or ORDER BY 2
ORDER BY created_date  -- or ORDER BY 2

注：

• 我调整了where子句，使其在整个天上进行筛选

• 我使用了未加引号的标识符；引用标识符一开始并不是DRY，它还使它们区分大小写