写SQL语句。
设关系数据库有到访城市记录表visit,表结构为:
字段名称 字段类型 字段含义
name varchar 姓名
date date 到访日期
city varchar 到访城市
该表每人每天生成一条记录,示例数据如:
name date city
张三 2022-10-01 广州
李四 2022-10-01 广州
王五 2022-10-01 深圳
张三 2022-10-02 广州
张三 2022-10-03 广州
张三 2022-10-04 深圳
张三 2022-10-05 东莞
张三 2022-10-06 东莞
张三 2022-10-07 广州
李四 2022-10-02 广州
请使用一条SQL语句,整理表数据,对于同一个人连续多日在同一个城市停留,仅保留第一天和最后一天两条记录,如果仅在该城市停留一天,则仅保留当天一条记录。例如,对于上述示例数据,您的SQL语句运行后输出结果应该是:
name date city
张三 2022-10-01 广州
张三 2022-10-03 广州
张三 2022-10-04 深圳
张三 2022-10-05 东莞
张三 2022-10-06 东莞
张三 2022-10-07 广州
李四 2022-10-01 广州
李四 2022-10-02 广州
王五 2022-10-01 深圳
1、PGSQL
WITH visit AS (
SELECT *
FROM ( VALUES ( '张三', '2022-10-01', '广州' ), ( '李四', '2022-10-01', '广州' ), ( '王五', '2022-10-01', '深圳' ), ( '张三', '2022-10-02', '广州' ), ( '张三', '2022-10-03', '广州' )
, ( '张三', '2022-10-04', '深圳' ), ( '张三', '2022-10-05', '东莞' ), ( '张三', '2022-10-06', '东莞' ), ( '张三', '2022-10-07', '广州' ), ( '李四', '2022-10-02', '广州' )
, ( '张三', '2022-10-08', '广州' ), ( '张三', '2022-10-09', '广州' ) ) AS visit( name, date, city ) )
, t1 AS (
SELECT *
, LAG( city ) OVER( PARTITION BY name ORDER BY date ) city_prev
, LEAD( city ) OVER( PARTITION BY name ORDER BY date ) city_next
FROM visit )
SELECT name, date, city FROM t1 WHERE COALESCE( city_prev, '1' ) != COALESCE( city_next, '2' )
2、Oracle(类似)
WITH visit AS (
SELECT '张三' "name", '2022-10-01' "date", '广州' city FROM dual UNION ALL
SELECT '李四', '2022-10-01', '广州' FROM dual UNION ALL
SELECT '王五', '2022-10-01', '深圳' FROM dual UNION ALL
SELECT '张三', '2022-10-02', '广州' FROM dual UNION ALL
SELECT '张三', '2022-10-03', '广州' FROM dual UNION ALL
SELECT '张三', '2022-10-04', '深圳' FROM dual UNION ALL
SELECT '张三', '2022-10-05', '东莞' FROM dual UNION ALL
SELECT '张三', '2022-10-06', '东莞' FROM dual UNION ALL
SELECT '张三', '2022-10-07', '广州' FROM dual UNION ALL
SELECT '李四', '2022-10-02', '广州' FROM dual UNION ALL
SELECT '张三', '2022-10-08', '广州' FROM dual UNION ALL
SELECT '张三', '2022-10-09', '广州' FROM dual )
, t1 AS (
SELECT "name", "date", city
, LAG( city ) OVER( PARTITION BY "name" ORDER BY "date" ) city_prev
, LEAD( city ) OVER( PARTITION BY "name" ORDER BY "date" ) city_next
FROM visit )
SELECT "name", "date", city FROM t1 WHERE COALESCE( city_prev, '1' ) != COALESCE( city_next, '2' )