sql - Postgresql - 按时间戳和 car_id 分组

Question

我有一张汽车记录表：

table: cars

columns:
car_id | timestamp | km_driven

我在跑步PostgreSQL 9.6。

我该怎么做：

SELECT timestamp, car_id, km_driven_within_the_hour FROM cars

我想GROUP BY我的timestamp所以每小时记录的所有记录都按每辆车分组，然后km_driven_within_the_hour SUM在那一小时内合并。

我试过了：

SELECT timestamp, car_id, SUM(km_driven) AS km_driven_within_the_hour
FROM cars
GROUP BY trunc(EXTRACT(hour from timestamp)), car_id

给我错误：

ERROR:  function pg_catalog.date_part(unknown, text) does not exist

和铸造时间戳：

SELECT timestamp, car_id, SUM(km_driven) AS km_driven_within_the_hour
FROM cars
GROUP BY trunc(EXTRACT(hour from timestamp::timestamp)), car_id

给我：

ERROR:  column "cars.timestamp" must appear in the GROUP BY clause or be used in an aggregate function

标签： sqlpostgresql

Answer 1

您快到了。timestamp按小时替换，如下所示：

SELECT
    trunc(EXTRACT(hour from timestamp::timestamp)), 
    car_id, 
    SUM(km_driven) AS km_driven_within_the_hour
  FROM cars
  GROUP BY trunc(EXTRACT(hour from timestamp::timestamp)), car_id