sql - 时间差值作为整数
问题描述
这是我的一张桌子:
CREATE TABLE trajectories_splitted
(
user_id integer,
session_id bigint NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
lat double precision NOT NULL,
lon double precision NOT NULL,
alt double precision,
PRIMARY KEY (session_id, "timestamp")
)
样本数据:
INSERT INTO trajectories_splitted (user_id, session_id,timestamp,lat,lon,alt)
VALUES (1,20081023025304,'2008-10-23 02:53:04+01',39.984702,116.318417,492),
(1,20081023025304,'2008-10-23 02:53:10+01',39.984683,116.31845,492),
(1,20081023025304,'2008-10-23 02:53:15+01',39.984686,116.318417,492),
(1,20081023025304,'2008-10-23 02:53:20+01',39.984688,116.318385,492),
(1,20081023025304,'2008-10-23 02:53:25+01',39.984655,116.318263,492),
(1,20081023025304,'2008-10-23 02:53:30+01',39.984611,116.318026,493),
(1,20081023025305,'2008-10-23 04:08:07+01',39.995777,116.286798,716),
(1,20081023025305,'2008-10-23 04:08:12+01',39.996832,116.285446,276 ),
(1,20081023025305,'2008-10-23 04:08:42+01',39.984397,116.299292,931),
(1,20081023025305,'2008-10-23 04:08:47+01',39.984426,116.299329,959),
(1,20081023025305,'2008-10-23 04:08:52+01',39.984499,116.299413,983),
(1,20081023025305,'2008-10-23 04:08:57+01',39.984424,116.299467,990),
(1,20081023025305,'2008-10-23 04:09:02+01',39.98441,116.299477,940),
(1,20081023025305,'2008-10-23 04:09:07+01',39.984421,116.299569,928),
(1,20081023025305,'2008-10-23 04:09:12+01',39.984518,116.29953,902),
(1,20081023025305,'2008-10-23 04:09:17+01',39.984488,116.299645,897)
session_id
由于列数据类型时间戳,因此获取行程总时间 ( ) 是一个问题:
select session_id,
(count(*)-1) / nullif((max(timestamp) - min(timestamp)), 0) as sampling_rate
from trajectories_splitted
group by session_id;
ERROR: operator does not exist: interval = integer
LINE 2: (count(*)-1) / nullif((max(timestamp) - min(timestamp...
^
HINT: No
operator matches the given name and argument type(s). You might need to add explicit type casts.
进而:
select session_id,
count(*) / nullif((max(timestamp) - min(timestamp))::int, 0) as sampling_rate
from trajectories_splitted
group by session_id;
ERROR: cannot cast type interval to integer
LINE 2: ...ount(*) / nullif((max(timestamp) - min(timestamp))::int, 0) ...
解决方案
根据建议:
select session_id,
count(*) / nullif(extract('epoch' from (max(timestamp) - min(timestamp))), 0) as sampling_rate
from trajectories_splitted
group by session_id;
session_id | sampling_rate
----------------+---------------------
20081023025304 | 0.23076923076923078
20081023025305 | 0.14285714285714285
推荐阅读
- postgresql - 如何使用 NFS 卷在 Kubernetes 上部署 Postgresql
- sql - 如何在 case 语句中使用 is null
- c++ - CMake 不链接 C 和 C++ 静态库(未定义对函数的引用)
- c++ - 在源文件中包含额外的调试信息,以便在 Code::Blocks 中使用 gdb 进行调试
- groovy - Spring Cloud 合约消费者 | 模拟 OkHttp
- api - 一句话打印API响应值面临一些问题
- javascript - JavaScript / Ajax:一个脚本的多种形式。如何只考虑提交的?
- javascript - Angular 5:*ngFor 的数据在单击单选按钮后发生变化
- android - 重复条目:android/support/design/widget/CoordinatorLayout$1.class
- javascript - Highcharts yAxis 标题点击