我有一张桌子如下:

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;

最佳答案

短一点,快一点,你得到的是天数而不是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')

monmonthmonths对于月单位,工作方式都相同。)
将每日总和除以当前月份的天数(orig)。问题):
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;

关于sql - 该月的每日平均值(需要每月的天数),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/19240571/

10-12 16:32