更快,更短一点,您得到的是天数,而不是interval
:
SELECT EXTRACT(day FROM date_trunc('month', Now()) + interval '1 month'
- interval '1 day')
可以将多个单位合并为一个interval
值。因此我们可以使用'1 mon - 1 day'
:
SELECT EXTRACT(day FROM date_trunc('month', Now()) + interval '1 mon - 1 day')
(mon
,month
或months
对月份单位使用相同的方法。)
将 除以当月的天数(原始问题):
SELECT t::date AS the_date
, SUM(c) AS c
, SUM(c) / EXTRACT(day FROM date_trunc('month', t::date)
+ interval '1 mon - 1 day') AS a
FROM dycounts
GROUP BY 1;
将 除以 的天数(更新的问题):
SELECT DATE_TRUNC('month', t)::date AS t
,SUM(c) AS c
,SUM(c) / EXTRACT(day FROM date_trunc('month', t)::date
+ interval '1 mon - 1 day') AS a
FROM dycounts
GROUP BY 1;
GROUP BY
如果要使用单个查询级别,则必须重复该表达式。
或使用 :
SELECT *, c / EXTRACT(day FROM t + interval '1 mon - 1 day') AS a
FROM (
SELECT date_trunc('month', t)::date AS t, SUM(c) AS c
FROM dycounts
GROUP BY 1
) sub;