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

qcbq4gxm  于 2023-01-17  发布在  PostgreSQL
关注(0)|答案(8)|浏览(134)

我在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

我想创建一个查询,它给出Sales列的SUM,并按月和年对结果进行分组,如下所示:

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

有没有简单的方法可以做到这一点?

goqiplq2

goqiplq21#

我不敢相信被接受的答案有这么多的赞成票--这是一个可怕的方法。
下面是使用date_trunc执行此操作的正确方法:

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

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

GROUP BY 1

一个非常简单的查询。
您也可以使用

GROUP BY date_trunc('month', txn_date)

如果你不想选择日期。

j5fpnvbx

j5fpnvbx2#

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

应拉杜的要求,我将解释这个问题:
to_char(date,'Mon') as mon,:将"date"属性转换为month缩写形式的定义格式。
extract(year from date) as yyyy:Postgresql的"extract"函数用于从"date"属性中提取YYYY年。
sum("Sales") as "Sales":SUM()函数将所有"Sales"值相加,并提供一个区分大小写的别名,通过使用双引号保持区分大小写。
group by 1,2:GROUP BY函数必须包含SELECT列表中不属于聚合的所有列(也就是不在SUM/AVG/MIN/MAX等函数中的所有列)。这告诉查询SUM()应用于每个唯一的列组合,在本例中是月列和年列。"1,2"部分是一个缩写,而不是使用列别名,尽管为了可读性,最好使用完整的"to_char(...)"和"extract(...)"表达式。

xxls0lw8

xxls0lw83#

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'

或者在上述用户示例的情况下:

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

hujrc8aj4#

还有另一种方法可以使用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)

谢谢

hfyxw5xn

hfyxw5xn5#

为什么不直接使用date_part函数。https://www.postgresql.org/docs/8.0/functions-datetime.html

SELECT date_part('year', txn_date) AS txn_year,
       date_part('month', txn_date) AS txn_month,
       sum(amount) as monthly_sum
FROM payment
GROUP BY txn_year, txn_month
order by txn_year;
zed5wv10

zed5wv106#

查看本教程的示例6)-〉https://www.postgresqltutorial.com/postgresql-group-by/
你需要调用GROUP BY上的函数,而不是调用你在select上创建的虚拟属性的名称。我正在做上面所有答案推荐的事情,我得到了一个column 'year_month' does not exist错误。
对我有效的是:

SELECT 
    date_trunc('month', created_at), 'MM/YYYY' AS month
FROM 
    "orders"  
GROUP BY 
    date_trunc('month', created_at)
deyfvvtc

deyfvvtc7#

Postgres有几种类型的时间戳:

不带时区的时间戳-(最好存储UTC时间戳)您可以在多国数据库存储中找到它。在这种情况下,客户端将负责每个国家的时区偏移。
带时区的时间戳-时间戳中已包含时区偏移量。

在某些情况下,您的数据库不使用时区,但您仍然需要根据本地时区和夏令时对记录进行分组(例如https://www.timeanddate.com/time/zone/romania/bucharest
要添加时区,您可以使用此示例并将时区偏移替换为您的时区偏移。

"your_date_column" at time zone '+03'

要添加特定于DST的+1夏令时偏移量,您需要检查您的时间戳是否福尔斯夏令时。由于这些间隔在1或2天内变化,我将使用不影响月末记录的近似值,因此在本例中,我可以忽略每年的确切间隔。
如果必须构建更精确的查询,那么您必须添加条件来创建更多的案例。但粗略地说,当您在数据库中找到不带时区的时间戳时,根据时区和SummerTime每月拆分数据时,这将工作得很好:

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)
ftf50wuq

ftf50wuq8#

我还需要查找按年和月分组的结果。当我按时间戳分组时,求和函数按日期和分钟分组,但这不是我想要的。使用此查询可能对您有帮助。

select sum(sum),
       concat(year, '-', month, '-', '01')::timestamp
from (select sum(t.final_price)               as sum,
             extract(year from t.created_at)  as year,
             extract(month from t.created_at) as month
      from transactions t
      where status = 'SUCCESS'
      group by t.created_at) t
group by year, month;

transactions tablequery result如图所示,在“2022-07-01”中,我在表中有两列,在查询结果中它们被分组在一起。

相关问题