在PostgreSQL中按月和年分组查询结果 [英] Group query results by month and year in postgresql

问题描述

我在Postgres服务器上有以下数据库表：

I have the following database table on a Postgres server:

id      date          Product Sales
1245    01/04/2013    Toys    1000     
1245    01/04/2013    Toys    2000
1231    01/02/2013    Bicycle 50000
456461  01/01/2014    Bananas 4546

我想创建一个查询，该查询给出 SUM >销售列，并按月和年对结果进行分组，如下所示：

I would like to create a query that gives the SUM of the Sales column and groups the results by month and year as follows:

Apr    2013    3000     Toys
Feb    2013    50000    Bicycle
Jan    2014    4546     Bananas

有一种简单的方法可以

推荐答案

select to_char(date,'Mon') as mon,
       extract(year from date) as yyyy,
       sum("Sales") as "Sales"
from yourtable
group by 1,2

应Radu的要求，我将解释该查询：

At the request of Radu, I will explain that query:

to_char（日期，'Mon'）as mon，：将 date属性转换为月的简短形式的定义格式。

to_char(date,'Mon') as mon, : converts the "date" attribute into the defined format of the short form of month.

extract（从日期开始的年）为yyyy ：Postgresql的 extract函数用于从 date属性中提取YYYY年。

extract(year from date) as yyyy : Postgresql's "extract" function is used to extract the YYYY year from the "date" attribute.

sum（ Sales）as Sales ：SUM（）函数将所有 Sales值相加，并提供区分大小写的别名，

sum("Sales") as "Sales" : The SUM() function adds up all the "Sales" values, and supplies a case-sensitive alias, with the case sensitivity maintained by using double-quotes.

group by 1,2 ：GROUP BY函数必须包含SELECT列表中不属于聚合的所有列（也就是，不在SUM / AVG / MIN / MAX等函数内的所有列）。这告诉查询应该将SUM（）应用于每个唯一的列组合，在这种情况下为月和年列。尽管可能最好使用完整的 to_char（...）和 extract（...）表达式，但 1,2部分是简化的方式，而不是使用列别名。

group by 1,2 : The GROUP BY function must contain all columns from the SELECT list that are not part of the aggregate (aka, all columns not inside SUM/AVG/MIN/MAX etc functions). This tells the query that the SUM() should be applied for each unique combination of columns, which in this case are the month and year columns. The "1,2" part is a shorthand instead of using the column aliases, though it is probably best to use the full "to_char(...)" and "extract(...)" expressions for readability.

这篇关于在PostgreSQL中按月和年分组查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！