在postgresql中按月和年对查询结果进行分组

I have the following database table on a Postgres server:

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

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

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

Is there a simple way to do that?

有没有一种简单的方法?

6 个解决方案

#1

120

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

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

应Radu的要求，我将解释这个问题:

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

to_char(date，'Mon')作为Mon，:将“date”属性转换为月份短形式的定义格式。

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

extract(from date)为yyyy: Postgresql的“extract”函数用于从“date”属性中提取yyy年。

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.

sum(“Sales”)作为“Sales”:sum()函数将所有“Sales”值相加，并提供区分大小写的别名，用双引号保持大小写的敏感性。

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.

group by 1,2: group by函数必须包含SELECT列表中不属于聚合的所有列(即，不属于SUM/AVG/MIN/MAX等函数的所有列)。这告诉查询，SUM()应该应用于每个列的唯一组合，在本例中是月列和年列。“1,2”部分是一个简写，而不是使用列别名，尽管它可能最好使用完整的“to_char(…)”和“extract(…)”表达式来表示可读性。

#2

151

I can't believe the accepted answer has so many upvotes -- it's a horrible method.

我不敢相信这个被接受的答案会有这么多的支持——这是一个可怕的方法。

Here's the correct way to do it, with date_trunc:

下面是使用date_trunc的正确方法:

   SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
     FROM yourtable
 GROUP BY txn_month

It's bad practice but you might be forgiven if you use

这是不好的做法，但如果你使用它，你可能会被原谅

 GROUP BY 1

in a very simple query.

在一个非常简单的查询中。

You can also use

您还可以使用

 GROUP BY date_trunc('month', txn_date)

if you don't want to select the date.

如果你不想选择日期。

#3

to_char actually lets you pull out the Year and month in one fell swoop!

to_char实际上让你在一年又一个月的猛攻中脱颖而出!

select to_char(date('2014-05-10'),'Mon-YY') as year_month; --'May-14'
select to_char(date('2014-05-10'),'YYYY-MM') as year_month; --'2014-05'

or in the case of the user's example above:

或以上用户的例子:

select to_char(date,'YY-Mon') as year_month
       sum("Sales") as "Sales"
from some_table
group by 1;

#4

bma answer is great! I have used it with ActiveRecords, here it is if anybody needs it in Rails:

bma回答是伟大的!我曾经用过它与ActiveRecords，如果有人需要它在Rails中:

Model.find_by_sql(
  "SELECT TO_CHAR(created_at, 'Mon') AS month,
   EXTRACT(year from created_at) as year,
   SUM(desired_value) as desired_value
   FROM desired_table
   GROUP BY 1,2
   ORDER BY 1,2"
)

#5

Postgress has few tipes for timestamps in postgress:

后辈很少有时间戳的印迹:

timestamp without timezone - (Preferable to store UTC timestamps) You find it in multinational database storage. The client in this case will take care of the timezone offset for each country.

没有时区的时间戳(最好存储UTC时间戳)，您可以在多国数据库存储中找到它。在这种情况下，客户机将负责每个国家的时区偏移。

timestamp with timezone - The timezone offset is already included in the timestamp.

带有时区的时间戳——时区偏移量已经包含在时间戳中。

In some cases, your database does not use the timezone but you still need to group records in respect with local timezone and Daylight Saving Time (e.g. https://www.timeanddate.com/time/zone/romania/bucharest)

在某些情况下，数据库不使用时区，但仍然需要根据本地时区和夏令时对记录进行分组(例如https://www.timeanddate.com/time/zone/romania/bucharest)

To add timezone you can use this example and replace the timezone offset with yours.

要添加时区，可以使用这个示例并将时区偏移量替换为您的时区偏移量。

"your_date_column" at time zone '+03'

To add the +1 Summer Time offset specific to DST you need to check if your timestamp falls into a Summer DST. As those intervals varies with 1 or 2 days, I will use an aproximation that does not affect the end of month records, so in this case i can ignore each year exact interval.

要添加特定于DST的+1夏季时间偏移量，需要检查您的时间戳是否属于夏季DST。由于这些间隔随着1或2天的变化而变化，我将使用不影响月末记录的近似方法，因此在这种情况下，我可以忽略每年的精确间隔。

If more precise query has to be build, then you have to add conditions to create more cases. But roughly, this will work fine in splitting data per month in respect with timezone and SummerTime when you find timestamp without timezone in your database:

如果要构建更精确的查询，则必须添加条件来创建更多的案例。但粗略地说，当您在数据库中找到没有时区的时间戳时，就时区和夏季时间而言，这可以很好地分割每个月的数据:

SELECT 
    "id", "Product", "Sale",
    date_trunc('month', 
        CASE WHEN 
            Extract(month from t."date") > 03 AND
            Extract(day from t."date") > 26 AND
            Extract(hour from t."date") > 3 AND
            Extract(month from t."date") < 10 AND
            Extract(day from t."date") < 29 AND
            Extract(hour from t."date") < 4
        THEN 
            t."date" at time zone '+03' -- Romania TimeZone offset + DST
        ELSE
            t."date" at time zone '+02' -- Romania TimeZone offset 
        END) as "date"
FROM 
    public."Table" AS t
WHERE 1=1
    AND t."date" >= '01/07/2015 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
    AND t."date" < '01/07/2017 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
GROUP BY date_trunc('month', 
    CASE WHEN 
        Extract(month from t."date") > 03 AND
        Extract(day from t."date") > 26 AND
        Extract(hour from t."date") > 3 AND
        Extract(month from t."date") < 10 AND
        Extract(day from t."date") < 29 AND
        Extract(hour from t."date") < 4
    THEN 
        t."date" at time zone '+03' -- Romania TimeZone offset + DST
    ELSE
        t."date" at time zone '+02' -- Romania TimeZone offset 
    END)

#6

There is another way to achieve the result using the date_part() function in postgres.

还有一种方法可以使用postgres中的date_part()函数来实现结果。

 SELECT date_part('month', txn_date) AS txn_month, date_part('year', txn_date) AS txn_year, sum(amount) as monthly_sum
     FROM yourtable
 GROUP BY date_part('month', txn_date)

Thanks

谢谢

#1

120

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