在PostgreSQL中计算每日总和

Question

我在Postgres中还比较新，我的目标是为每个月的每一天计算总和值（即每日总和值）。根据分散的信息，我想到了以下内容：

CREATE OR REPLACE FUNCTION sumvalues() RETURNS double precision AS
$BODY$
BEGIN
   FOR i IN 0..31 LOOP
   SELECT SUM("Energy") 
   FROM "public"."EnergyWh" e
   WHERE  e."DateTime" = day('01-01-2005 00:00:00'+ INTERVAL 'i' DAY);
   END LOOP;

END
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.sumvalues()
  OWNER TO postgres;

查询已成功返回，所以我认为我已经做对了。然而，当我试图将函数的值插入到一个表中时（这可能是错误的）：

INSERT INTO "SumValues"
("EnergyDC") 

    (
     SELECT sumvalues()
     ); 

我得到了这个错误信息:

ERROR: 无效的时间间隔输入语法: "01-01-2005 00:00:00" 第3行: WHERE e."DateTime" = day('01-01-2005 00:00:00'+ INTERVAL...

我尝试自己调试，但仍不确定我做错了哪一个（或两个），以及为什么会出错。

这是一个示例：EnergyWh。

（我使用系统ID和日期时间作为复合主键，但应该不重要。）

- Elena_K

---

请提供表结构和一些示例数据，或者在SQL FIDDLE上创建相应的数据库模型。 - Ilesh Patel

Answer 1

请查看GROUP BY子句：http://www.postgresql.org/docs/9.2/static/tutorial-agg.html。

SELECT EXTRACT(day FROM e."DateTime"), EXTRACT(month FROM e."DateTime"), 
       EXTRACT(year FROM e."DateTime"), sum("Energy")
   FROM "public"."EnergyWh" e
  GROUP BY 1,2,3

但是以下查询也应该可以工作：

SELECT e."DateTime"::date, sum("Energy")
   FROM "public"."EnergyWh" e
  GROUP BY 1

我正在使用一个简短的语法来对 GROUP BY 进行分组 ~ GROUP BY 1 .. 按照第一列进行分组。

- Pavel Stehule

---

非常感谢您的回答，这种方法似乎更容易 :). 第一段代码确实有效（但第二段不行）。 - Elena_K

当然，它需要转换为日期 - 已修复。 - Pavel Stehule

Answer 2

这里有一个简单的例子可以帮助你：

表格：

create table demo (value double precision);

函数

CREATE OR REPLACE FUNCTION sumvalues() RETURNS void AS
$BODY$
DECLARE
    inte text;
BEGIN
   FOR i IN 0..31 LOOP
    inte := 'INSERT INTO demo SELECT EXTRACT (DAY FROM TIMESTAMP ''01-01-2005 00:00:00''+ INTERVAL '''||i||' Days'')';
    EXECUTE inte;
   END LOOP;

END
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.sumvalues()
  OWNER TO postgres;

函数调用

SELECT sumvalues();

输出

 SELECT * FROM demo;

如果你想在 SQL 查询中使用一些变量的值，那么你必须使用一些动态查询。

参考资料: pgsql中的动态查询

- Ilesh Patel

---

请留下负数的原因是什么？ - Ilesh Patel

我的猜测是有人对此进行了负面评价，因为您并没有真正解释您所做的更改及其原因；只是简单地转储了修改后的代码。但这只是我的猜测。 - Andrew Barber

动态SQL应该是最后的选择 - 主要是针对初学者 - 使用它会更容易写出性能或安全问题的代码。 - Pavel Stehule

@Pavel Stehule：除了上述方法，我是否可以仅选择显示特定年份的结果，例如2006年？我仍然想要每天的总和，但仅限于2006年。这将如何进行？时间戳让我很难处理... - Elena_K

@Elena_K 确定，使用 WHERE 子句 - WHERE EXTRACT(year FROM e."DateTime") = 2006 类似于经典查询。 - Pavel Stehule