汇总Postgres联盟结果

debugcn 发表于 Dev

现在

我正在尝试使用postgres DB中的单个表在SQL中每个日期获取一个singel结果行。我尝试使用Union，但是我认为这不是正确的方法。可以帮助我构造正确的SQL。

表内容的样本数据列：id，creationdate，contenttype

1 |2016-04-02|PAGE
2 |2016-04-02|ATTACHMENT
3 |2016-04-02|PAGE
4 |2016-04-03|ATTACHMENT
5 |2016-04-03|PAGE
6 |2016-04-03|ATTACHMENT
7 |2016-04-03|PAGE
8 |2016-04-04|ATTACHMENT
9 |2016-04-04|ATTACHMENT
10|2016-04-04|ATTACHMENT

如果使用此SQL查询：

SELECT 
  date(creationdate) AS create_date,
  COUNT(*) AS PAGE,0 AS ATTACHMENT 
FROM 
  content 
WHERE 
  contenttype='PAGE' GROUP BY content.creationdate 
UNION
SELECT 
  date(creationdate) AS create_date,
  0 AS PAGE,COUNT(*) AS ATTACHMENT
FROM 
  content 
WHERE 
  contenttype='ATTACHMENT' GROUP BY content.creationdate 
ORDER BY create_date ASC;

我得到结果

|create_date|PAGE|ATTACHMENT|
|2016-04-02|0|2|
|2016-04-02|1|0|
|2016-04-03|0|2|
|2016-04-03|1|0|
|2016-04-04|3|0|

我想要的是：

|create_date|PAGE|ATTACHMENT|
|2016-04-02|1|2|
|2016-04-03|2|2|
|2016-04-04|3|0|

萨加斯

您可以通过从表中选择一次来进行条件聚合：

SELECT date(creationdate) AS create_date,
       count(CASE WHEN contenttype='PAGE' then 1 end) as PAGE,
       count(CASE WHEN contenttype='ATTACHMENT' then 1 end) as ATTACHMENT
FROM 
  content 
GROUP BY content.creationdate 
ORDER BY create_date ASC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。