该月的每日平均值（需要每月的天数）

小编典典

该月的每日平均值（需要每月的天数）

sql

我有一张表，如下所示：

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（即特定月份中的天数）时，就会出现问题。

我知道要在PostgreSQL中获得成功，您可以：

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

但是我不能使用now()，我必须以某种方式让它知道分组完成的月份。任何建议，即什么应该取代 ？？？ 在此视图中：

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

阅读 357

2021-04-28

共1个答案

小编典典

更快，更短一点，您得到的是天数，而不是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;

2021-04-28