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.     
  5. 向Table1插入数据
  6.     ("name", "used", "time")
  7. VALUES
  8.     ('asd', 10, '15:00'),
  9.     ('bsf', 15, '15:00'),
  10.     ('asd', 20, '14:55'),
  11.     ('bsf', 0, '14:55')
  12. ;
  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.     
  5. INSERT INTO Table1
  6.     ("name", "used", "time")
  7. VALUES
  8.     ('asd', 10, '15:00'),
  9.     ('bsf', 15, '15:00'),
  10.     ('asd', 20, '14:55'),
  11.     ('bsf', 0, '14:55')
  12. ;
  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
  • gaps-and-islands
  • postgresql
  • sql
  • window-functions
匿名

发表评论

匿名网友

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

确定