将查询中的聚合值插入到SQL中的表中

Question

我是SQL新手，经历了多次尝试和错误以及搜索书籍和因特网。我不得不重复查询五年中每月数据的总和，我想在表中插入每月的结果作为列。我试图每月添加新的列

alter table add column, insert etc. 

但我不能说得对。下面是我用简码和feb07：它工作正常

CREATE TABLE "TVD_db"."lebendetiere" 
(nuar text, 
ak text, 
sex text, 
jan07 text, 
feb07 text, 
märz07 text, 
april07 text, 
mai07 text, 
juni07 text, 
juli07 text, 
aug07 text, 
sept07 text, 
okt07 text, 
nov07 text, 
dez07 text, 
jan08 text, 
.... 
dez11 text); 


INSERT INTO "TVD_db"."lebendetiere" (nuar, ak, sex, jan07) 
SELECT 
"AUFENTHALTE"."nuar", 
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END AS AK, 
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END AS sex, 
COUNT("AUFENTHALTE"."tierid") 
FROM "TVD_db"."AUFENTHALTE" 
WHERE DATE("AUFENTHALTE"."gueltigvon") <= DATE('2007-01-01') 
AND DATE("AUFENTHALTE"."gueltigbis") >= DATE('2007-01-01') 
GROUP BY "AUFENTHALTE"."nuar", 
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END, 
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END 
ORDER BY "AUFENTHALTE"."nuar", 
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' wWHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END, 
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END 
; 

--until这里

UPDATE "TVD_db"."lebendetiere" SET "feb07"= --this is the part I cant get right... 
(SELECT 
COUNT("AUFENTHALTE"."tierid") 
FROM "TVD_db"."AUFENTHALTE" 
WHERE DATE("AUFENTHALTE"."gueltigvon") <= DATE('2007-02-01') 
AND DATE("AUFENTHALTE"."gueltigbis") >= DATE('2007-02-01') 
GROUP BY "AUFENTHALTE"."nuar", 
CASE WHEN DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END, 
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END 
ORDER BY "AUFENTHALTE"."nuar", 
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' wWHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END, 
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END); 

有没有人溶液或做我必须做一个表，每个月，然后加入结果如何？

Answer 1

完全阅读您的文章后，这里是一个完整重新设计应该对SQL/PostgreSQL领域的初学者有一些洞察力。

• 我建议不要混合使用identifiers in PostgreSQL。只使用小写字母，那么您不必重复引用它们，并且您的代码更易于阅读。你也避免了很多可能的混淆。

• 使用table aliases使您的代码更具可读性。

• INSERT的SELECT语句中的列名是不相关的。这就是为什么我评论出来（避免可能的命名冲突）。

• 使用GROUP BY和ORDER BY中的序号进一步简化。

• 请勿为每个新月使用单独的列。使用标识月份的列并每月添加一行。
  如果您确实需要每月有一列的设计，那么您需要一个大的CASE语句或一个数据透视查询。请参阅tablefunc扩展。但对于一个SQL新手来说，这是一件复杂的事情。我真的觉得，你想每个月都有一排。

• 我用generate_series()至2007年1月和12月2011.

• 之间每月产生一个排，我改变了设计，你不需要额外的更新。这全部在一个INSERT中完成。

我简化了很多其他的东西。这是我会建议，而不是：

CREATE TABLE tvd_db.lebendetiere(
    nuar text, 
,alterskat integer 
,sex text 
,datum date 
,anzahl integer 
); 

INSERT INTO tvd_db.lebendetiere (nuar, alterskat, sex, datum, anzahl) 
SELECT a.nuar 
     ,CASE WHEN a.gebdat >= '2006-01-01'::date THEN 1 -- use >= ! 
      WHEN a.gebdat < '2005-01-01'::date THEN 3 
      ELSE          2 END -- AS alterskat 
     ,CASE WHEN a.isweiblich = 'T' THEN 'female' ELSE 'male' END -- AS sex 
     ,m.m 
     ,count(*) -- AS anzahl 
FROM tvd_db.aufenthalte a 
CROSS JOIN (
    SELECT generate_series('2007-01-01'::date 
          ,'2011-12-01'::date, interval '1 month')::date 
    ) m(m) 
WHERE a.gueltigvon <= m.m 
AND a.gueltigbis >= m.m 
GROUP BY a.nuar, 2, 3, m.m 
ORDER BY a.nuar, 2, 3, m.m;