用于年份提取和求和的 SQL (postgresql) 查询

发布于 2024-09-30 23:05:57 字数 816 浏览 3 评论 0原文

我尝试在表上收集一些简单的统计信息,假设简化的结构如下:

  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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

丶视觉 2024-10-07 23:05:58

我会在执行查询的软件中执行此操作,而不是在查询本身中执行此操作。

我已经通过在 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.

清醇 2024-10-07 23:05:58

一种简单(但不是很好)的方法是创建一个(临时)表,将所有年份保存在一列中。然后您只需将数据表连接到新表即可。

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.

掀纱窥君容 2024-10-07 23:05:57

使用生成系列中的左外连接

select year_list.year,
       coalesce(item_list.val,0)
 from (select generate_series(2007,2010) as year) AS year_list
       left outer join
      (select date_part('year',creationdate) item_year,sum(value) as val from i group by item_year) AS item_list 
      on item_year = year_list.year;     

以避免对生成系列中的年份进行硬编码,并在年份部分替换 select min() 和 max()。

Use a left outer join from a generate series

select year_list.year,
       coalesce(item_list.val,0)
 from (select generate_series(2007,2010) as year) AS year_list
       left outer join
      (select date_part('year',creationdate) item_year,sum(value) as val from i group by item_year) AS item_list 
      on item_year = year_list.year;     

to avoid hard coding the years in the generate_series substitute a select min() and max() on the year part.

浸婚纱 2024-10-07 23:05:57

在 JOIN 中使用generate_series:

SELECT 
    generate_series as d1, 
    COALESCE(sum(i.value) , 0)
FROM 
    generate_series(2005, 2010) 
        LEFT JOIN items i ON generate_series = extract('year' from i.creationdate)
GROUP BY
    d1 
ORDER BY
    d1;

Use generate_series in a JOIN:

SELECT 
    generate_series as d1, 
    COALESCE(sum(i.value) , 0)
FROM 
    generate_series(2005, 2010) 
        LEFT JOIN items i ON generate_series = extract('year' from i.creationdate)
GROUP BY
    d1 
ORDER BY
    d1;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文