本月平均每月（需月数天数） [英] Daily average for the month (needs number of days in month)

问题描述

我有一张表如下：

CREATE TABLE counts
(
    T TIMESTAMP NOT NULL,
    C INTEGER NOT NULL
);

我从中创建以下视图：

CREATE VIEW micounts AS 
SELECT DATE_TRUNC('minute',t) AS t,SUM(c) AS c FROM counts GROUP BY 1;

CREATE VIEW hrcounts AS
SELECT DATE_TRUNC('hour',t) AS t,SUM(c) AS c,SUM(c)/60 AS a
FROM micounts GROUP BY 1;

CREATE VIEW dycounts AS
SELECT DATE_TRUNC('day',t) AS t,SUM(c) AS c,SUM(c)/24 AS a
FROM hrcounts GROUP BY 1;

当我想创建每月计数以知道分割每日金额时，现在出现了问题通过获取平均列a即特定月份的天数。

The problem now comes in when I want to create the monthly counts to know what to divide the daily sums by to get the average column a i.e. the number of days in the specific month.

我知道可以在PostgreSQL中获得日期：

I know to get the days in PostgreSQL you can do:

SELECT DATE_PART('days',DATE_TRUNC('month',now())+'1 MONTH'::INTERVAL-DATE_TRUNC('month',now()))

但是我现在不能使用 / code>，我必须以某种方式让它知道分组完成后的月份是什么。任何建议，即在此视图中应该替换？的建议：

But I can't use now(), I have to somehow let it know what the month is when the grouping gets done. Any suggestions i.e. what should replace ??? in this view:

CREATE VIEW mocounts AS
SELECT DATE_TRUNC('month',t) AS t,SUM(c) AS c,SUM(c)/(???) AS a
FROM dycounts
GROUP BY 1;

推荐答案

稍微快一点，你得到的数量天而不是间隔：

A bit shorter and faster and you get the number of days instead of an interval:

SELECT EXTRACT(day FROM date_trunc('month', now()) + interval '1 month'
                                                   - interval '1 day')

可以在单个间隔值中组合多个单位。所以我们可以使用'1 mon-1 day'：

It's possible to combine multiple units in a single interval value . So we can use '1 mon - 1 day':

SELECT EXTRACT(day FROM date_trunc('month', now()) + interval '1 mon - 1 day')

（ mon ， month 或 months 将每日总和除以当前月份的天数（原始问题）：

(mon, month or months work all the same for month units.)

/ p>

To divide the daily sum by the number of days in the current month (orig. question):

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;

将每月总额除以当月的天数（更新的问题）：

To divide monthly sum by the number of days in the current month (updated question):

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 表达式如果您要使用单个查询级别。

You have to repeat the GROUP BY expression if you want to use a single query level.

或使用子查询：

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;

这篇关于本月平均每月（需月数天数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！