代码之家 › 专栏 › 技术社区 › Alfred Balle

postgresql,select输出结果+1小时

postgresql sql

Alfred Balle · 技术社区 · 7 年前

我有以下的 SQL 它的作用是 GROUP BY 时间:

SELECT
CONCAT(TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), ':00:00') as time_from,
car_id,
SUM(km) as driven from car_data
group by TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), car_id
order by time_from, car_id;

我想要一个额外的输出,它是 time_from +间隔1小时。我试过:

SELECT
CONCAT(TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), ':00:00') as time_from,
CONCAT(TO_CHAR(timestamp::timestamp + interval '1 hour', 'YYYY-MM-DD HH'), ':00:00') as time_to,
car_id,
SUM(km) as driven from car_data
group by TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), car_id
order by time_from, car_id;

但那报告我需要 分组依据 该领域还包括:

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

,我不想这么做。我怎样才能最好地存档?

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

您只需将这两列添加到 group by 以下内容:

SELECT CONCAT(TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH'), ':00:00') as time_from,
       CONCAT(TO_CHAR(timestamp::timestamp + interval '1 hour', 'YYYY-MM-DD HH'), ':00:00') as time_to,
       car_id,
       SUM(km) as driven 
FROM car_data
GROUP BY time_from, time_to, car_id 
ORDER BY time_from, car_id;

但是,我不会将时间戳转换为字符串。相反,使用 date_trunc() 以下内容:

SELECT date_trunc('hour', timestamp::timestamp) as time_from,
       date_trunc('hour', timestamp::timestamp) + interval '1 hour' as time_to,
       car_id,
       SUM(km) as driven 
FROM car_data
GROUP BY date_trunc('hour', timestamp::timestamp), car_id 
ORDER BY time_from, car_id;

Tim Biegeleisen 7 年前

一种选择是将当前工作查询放入CTE,然后使用它生成所需的输出:

WITH cte AS (
    SELECT
        timestamp::timestamp AS time_from,
        car_id,
        SUM(km) AS driven
    FROM car_data
    GROUP BY timestamp::timestamp, car_id
)

SELECT
    CONCAT(TO_CHAR(time_from, 'YYYY-MM-DD HH'), ':00:00') AS time_from,
    CONCAT(TO_CHAR(time_from + interval '1 hour', 'YYYY-MM-DD HH'), ':00:00') AS time_to,
    car_id,
    driven
FROM cte
ORDER BY
    time_from, car_id;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

9 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

10 月前

John D · 需要为NULL或NOT NULL的WHERE子句

10 月前

ojek · 如何对SQL结果进行分组和编号?

10 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

10 月前

Sax · 规范化Google表格(第一步)

10 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

10 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

10 月前

Andrus · 如何在sql中查找第二个匹配项

10 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

10 月前