用于年份提取和求和的 SQL (postgresql) 查询
我尝试在表上收集一些简单的统计信息,假设简化的结构如下:
ID | CREATIONDATE | VALUE |
------------------------------------------------
1 | 2007-01-06 13:54:00.000 | 7 |
2 | 2007-03-07 15:03:00.000 | 8 |
3 | 2008-07-02 18:55:00.000 | 12 |
4 | 2008-09-10 19:12:00.000 | 1 |
5 | 2010-01-06 13:54:00.000 | 4 |
6 | 2010-01-07 07:13:00.000 | 5 |
我想根据创建日期的年份对值进行求和,因此当前我执行以下命令:
select extract('year' from i.creationdate) as d1,
sum(i.value)
from items i
group d1
order by d1;
它将输出
2007;15
2008;13
2010;9
Is there any我可以改进查询,让它自动为缺失的 2009 年输出 0,即使表中没有包含 2009 年日期的行?
2007;15
2008;13
2009;0
2010;9
I try to collect some simple statistics on a table, let's say the simplified structure is the following:
ID | CREATIONDATE | VALUE |
------------------------------------------------
1 | 2007-01-06 13:54:00.000 | 7 |
2 | 2007-03-07 15:03:00.000 | 8 |
3 | 2008-07-02 18:55:00.000 | 12 |
4 | 2008-09-10 19:12:00.000 | 1 |
5 | 2010-01-06 13:54:00.000 | 4 |
6 | 2010-01-07 07:13:00.000 | 5 |
I'd like to sum the values based on the year of the creation date, so currently I execute the following:
select extract('year' from i.creationdate) as d1,
sum(i.value)
from items i
group d1
order by d1;
which will output
2007;15
2008;13
2010;9
Is there any way I can improve the query to have it automatically outputs 0 for the missing 2009 year, even if there is no row in the table with a date in 2009?
2007;15
2008;13
2009;0
2010;9
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我会在执行查询的软件中执行此操作,而不是在查询本身中执行此操作。
我已经通过在 SQL 中使用 for 循环完成了这样的事情(例如在 Oracle 中)(毫无疑问在 Postgres 中也有类似的事情),但是我得出的结论是这是错误的方法。该解决方案比我简单地用应用程序的编程语言编写代码更难维护。
SQL 返回存在的数据,并对存在的数据进行操作,在这种情况下,相关年份没有数据。这就是为什么尝试让 SQL 来执行此操作不太合适。只需向 SQL 询问您拥有的数据,当您用编程语言为用户生成输出(例如 HTML 表)时,然后添加 for 循环以在第一年和最后一年之间进行迭代,如果没有结果则打印零。
I would do this in the software which is doing the query, as opposed to in the query itself.
I have done such things (e.g. in Oracle) by using for loops in SQL (no doubt there are similar things in Postgres) however I came to the conclusion that was the wrong approach. The solution was more difficult to maintain than if I had simply written the code in the programming language of the application.
SQL returns data that exists, and operates on data that exists, and in that case there is no data for the year in question. That's why it's not a good fit to try and get SQL to do this. Just ask SQL for the data you do have, and when you generate your output in your programming language for the user (e.g. HTML table) then add the for loop to iterate between the first and the last year and print zero if there is no result.
一种简单(但不是很好)的方法是创建一个(临时)表,将所有年份保存在一列中。然后您只需将数据表连接到新表即可。
An easy (but not a nice) way is to create a (temporary) table holding all years in one column. Then you just join the data table to your new table.
使用生成系列中的左外连接
以避免对生成系列中的年份进行硬编码,并在年份部分替换 select min() 和 max()。
Use a left outer join from a generate series
to avoid hard coding the years in the generate_series substitute a select min() and max() on the year part.
在 JOIN 中使用generate_series:
Use generate_series in a JOIN: