首页 / 技术问答 / Sql问答列表 / 问答详情

Sql 添加一列并根据其他列值进行填充

我有下表:

CREATE TABLE trajectory(
    user_id int, 
    session_id int, 
    timestamp timestamp with time zone,
    lat double precision, 
    lon double precision
);

INSERT INTO trajectory(user_id, session_id, timestamp, lat, lon) VALUES 
(1, 25304,'2008-10-23 02:53:04+01', 39.984702, 116.318417),
(1, 25304, '2008-10-23 02:53:10+01', 39.984683, 116.31845), 
(1, 25304, '2008-10-23 02:53:15+01', 39.984686, 116.318417),
(1, 25304, '2008-10-23 02:53:20+01', 39.984688, 116.318385), 
(1, 20959,'2008-10-24 02:09:59+01', 40.008304, 116.319876),
(1, 20959,'2008-10-24 02:10:04+01', 40.008413, 116.319962), 
(1, 20959,'2008-10-24 02:10:14+01', 40.007171, 116.319458),
(2, 55305, '2008-10-23 05:53:05+01', 39.984094, 116.319236), 
(2, 55305, '2008-10-23 05:53:11+01', 39.984198, 116.319322), 
(2, 55305, '2008-10-23 05:53:21+01', 39.984224, 116.319402), 
(2, 34104, '2008-10-23 23:41:04+01', 40.013867, 116.306473),
(2, 34104, '2008-10-23 23:41:16+01', 40.013907, 116.306488);

Question:个

我想在这个表中添加一个基于user_id和session_id列的trip_id列，这样当用户的会话id更改时，我知道用户正在进行新的旅行，所以我将该id添加到新的trip列中.

所需输出:

user_id |session_id  |timestamp              |    lat       |   lon     | trip_id
--------|------------|-----------------------|--------------|-----------|-----------
  1     |     25304  |2008-10-23 02:53:04+01 | 39.984702    |116.318417 |       1
  1     |     25304  |2008-10-23 02:53:10+01 | 39.984683    |116.31845  |       1
  1     |     25304  |2008-10-23 02:53:15+01 | 39.984686    |116.318417 |       1
  1     |     25304  |2008-10-23 02:53:20+01 | 39.984688    |116.318385 |       1
  1     |     20959  |2008-10-24 02:09:59+01 |40.008304     |116.319876 |       2
  1     |     20959  |2008-10-24 02:10:04+01 |40.008413     |116.319962 |       2
  1     |     20959  |2008-10-24 02:10:14+01 |40.007171     |116.319458 |       2
  2     |     55305  |2008-10-23 05:53:05+01 |39.984094     |116.319236 |       1
  2     |     55305  |2008-10-23 05:53:11+01 |39.984198     |116.319322 |       1
  2     |     55305  |2008-10-23 05:53:21+01 |39.984224     |116.319402 |       1
  2     |     34104  |2008-10-23 23:41:04+01 |40.013867     |116.306473 |       2
  2     |     34104  |2008-10-23 23:41:16+01 |40.013907     |116.306488 |       2

How can I do this?

EDIT个

感谢您的这些精彩回答，但收到的所有回答都是检索表值，并不修改表.此外，我添加了timestamp栏，我最初忽略了这一点，认为这会简化我的问题.

推荐答案

这可以通过使用窗口函数lag()来检索前一行并确定它是否已经改变，然后使用窗口函数sum()来检索trip_id:

with cte as (
  select *, case when 
                 session_id - lag(session_id, 1, session_id) 
                              over (partition by user_id order by timestamp) = 0 
                 then 0 else 1 end as diff
  from trajectory
)
select user_id, session_id, timestamp,  lat, lon, 1 + sum(diff) over (partition by user_id order by timestamp) as trip_id
from cte
order by user_id, timestamp

假设用户ID/时间戳是唯一的，则更新可以是:

ALTER TABLE trajectory ADD COLUMN trip_id int;

with cte as (
  select *, case when session_id - lag(session_id, 1, session_id) over (partition by user_id order by timestamp) = 0 then 0 else 1 end as diff
  from trajectory
),
cte2 as (
  select user_id,   session_id, timestamp,  lat, lon, 1 + sum(diff) over (partition by user_id order by timestamp) as trip_id
  from cte
  order by user_id, timestamp
)
UPDATE trajectory 
SET trip_id = cte2.trip_id
FROM cte2
WHERE trajectory.timestamp = cte2.timestamp and trajectory.user_id = cte2.user_id

结果:

user_id session_id timestamp lat lon trip_id
1 25304 2008-10-23 02:53:04+01 39.984702 116.318417 1
1 25304 2008-10-23 02:53:10+01 39.984683 116.31845 1
1 25304 2008-10-23 02:53:15+01 39.984686 116.318417 1
1 25304 2008-10-23 02:53:20+01 39.984688 116.318385 1
1 20959 2008-10-24 02:09:59+01 40.008304 116.319876 2
1 20959 2008-10-24 02:10:04+01 40.008413 116.319962 2
1 20959 2008-10-24 02:10:14+01 40.007171 116.319458 2
2 55305 2008-10-23 05:53:05+01 39.984094 116.319236 1
2 55305 2008-10-23 05:53:11+01 39.984198 116.319322 1
2 55305 2008-10-23 05:53:21+01 39.984224 116.319402 1
2 34104 2008-10-23 23:41:04+01 40.013867 116.306473 2
2 34104 2008-10-23 23:41:16+01 40.013907 116.306488 2

Demo here个

Sql相关问答推荐

如何以"% m—% d"格式对生日列表进行排序，以查找与今天最近的日期？

当交叉联接3个或更多表时，实体框架中是否会传输冗余的行数据并占用数据库带宽？

如何在postgres函数中插入后返回布尔值？

在查询Oracle SQL中创建替代ID

数据库SQL-CTE命名空间(错误？)使用临时视图

比较SQL中以逗号分隔的字符串

排除具有部分匹配条件的记录

如何在 golang squirrel lib 中添加 postgreSQL 的distinct on

将时间戳四舍五入到最近 10 分钟的查询

如何为 ActiveRecord 联接应用附加条件

将一名成员金额分配给群组内的其他成员

SQL 多个不满足的条件失败

计数时如何为所有时间间隔返回 0 而不是什么都不返回

为 sqlite 全文搜索 (fts) 创建触发器时出现虚拟表的不安全使用

SQL Select 最大并获取列名

在 MS Access 中连接相关记录

Postgres 条件求和函数

当没有任何行存在时，将一个表中的行插入到另一个表中的更好方法

CURRENT_ROW 窗口框架上的 SQL 滞后

如何在 Trino/Presto 中过滤掉 map 中的某些键？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630