Postgresql问题

huangapple go评论113阅读模式
英文:

Postgresql problems

问题

我需要执行以下查询以返回所需结果:

  1. SELECT
  2. sum(used) as total,
  3. time as tm
  4. FROM table
  5. GROUP BY tm

请注意,您的原始查询中使用了DISTINCT(time),但这会导致您获得重复的时间值。在上面的查询中,我删除了DISTINCT,并将time用作tm列的别名,以获得您所需的结果。

英文:

i have a table like this:

  1. name used time
  2. asd 10 15:00
  3. bsf 15 15:00
  4. asd 20 14:55
  5. bsf 0 14:55

i need to make a query that returns values like this:
the result i need for the grafana timeseries is:

  1. total tm
  2. 25 15:00
  3. 20 14:55

i've tried using:

  1. SELECT
  2. DISTINCT(time) as tm,
  3. sum(used) as total
  4. FROM table
  5. GROUP BY tm

but that doesnt works everything i try gives me the repeated time values

答案1

得分: 0

回顾PostgreSQL文档是开始使用时间戳的好方法。以下是按HH:MI:SS分组和聚合的一种方法:

  1. with my_table as (
  2. select current_timestamp as time_column, 20 as used union all
  3. select current_timestamp, 5 union all
  4. select current_timestamp - INTERVAL '10 Seconds', 15
  5. )
  6. select to_char(time_column,'HH24:MI:SS') as time_col, sum(used) as used
  7. from my_table
  8. group by 1
  9. order by 1;
time_col used
19:43:35 15
19:43:45 25

基本上,这种类型的转换是您的朋友:

  1. to_char(time_column,'HH24:MI:SS')
英文:

Reviewing the postgres docs is a great place to start when working with timestamps. Here's a way to group by HH:MI:SS and aggregate:


  1. with my_table as (
  2. select current_timestamp as time_column, 20 as used union all
  3. select current_timestamp, 5 union all
  4. select current_timestamp - INTERVAL '10 Seconds', 15
  5. )
  6. select to_char(time_column,'HH24:MI:SS') as time_col, sum(used) as used
  7. from my_table
  8. group by 1
  9. order by 1;
time_col used
19:43:35 15
19:43:45 25

Basically, this type of casting is your friend:

  1. to_char(time_column,'HH24:MI:SS')

答案2

得分: 0

我使用date_trunc()函数成功解决了这个问题,以选择时间值,因为列的类型是timestamptz,所以查询最终如下所示:

  1. select
  2. date_trunc('minute', time) as tm,
  3. sum(used) as total
  4. from
  5. table
  6. group by
  7. tm
英文:

I managed to solve the problem by using the date_trunc() function to select the time value since the column was of type timestamptz the query ended up looking like this

  1. select
  2. date_trunc('minute', time) as tm,
  3. sum(used) as total
  4. from
  5. table
  6. group by
  7. tm

答案3

得分: 0

以下是翻译好的部分:

  1. 创建表Table1
  2. ("name" varchar(3), "used" int, "time" varchar(5))
  3. ;
  4. Table1插入数据
  5. ("name", "used", "time")
  6. VALUES
  7. ('asd', 10, '15:00'),
  8. ('bsf', 15, '15:00'),
  9. ('asd', 20, '14:55'),
  10. ('bsf', 0, '14:55')
  11. ;
  1. 选择
  2. SUM("used"), "time"
  3. Table1
  4. "time"分组
英文:

You are looking for a simple GROUP BY

  1. CREATE TABLE Table1
  2. ("name" varchar(3), "used" int, "time" varchar(5))
  3. ;
  4. INSERT INTO Table1
  5. ("name", "used", "time")
  6. VALUES
  7. ('asd', 10, '15:00'),
  8. ('bsf', 15, '15:00'),
  9. ('asd', 20, '14:55'),
  10. ('bsf', 0, '14:55')
  11. ;
  1. SELECT
  2. SUM("used"), "time"
  3. FROM Table1
  4. GROUP BY "time"
sum time
25 15:00
20 14:55
> ``` status
> SELECT 2
> ```

fiddle

huangapple
  • 本文由 发表于 2023年8月4日 03:26:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76831099.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定