从带有铅/滞后的select查询中创建表
从带有铅/滞后的select查询中创建表
提问于 2020-07-15 12:28:26
这是我手边的桌子：
SELECT * FROM smf_table LIMIT 20;
id | trip_id | segment_id | segment_start_timestamp | timestamp | lat | lon | travelmode
---------+---------+------------+-------------------------+------------+-------------+-------------+------------
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459467971 | 41.1523521 | -8.6097233 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468020 | 41.1523518 | -8.6097168 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468026 | 41.1524153 | -8.6097054 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468031 | 41.1524057 | -8.609701 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468036 | 41.1523647 | -8.6097146 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468041 | 41.1525607 | -8.6096725 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468046 | 41.1525077 | -8.6096843 | 0
5338113 | 533811 | 3 | 2016-04-01 00:47:16+01 | 1459468051 | 41.1524966 | -8.6096833 | 0
5338151 | 533815 | 1 | 2016-04-01 00:06:40+01 | 1459465282 | 41.14454009 | -8.56292593 | 3
5338151 | 533815 | 1 | 2016-04-01 00:06:40+01 | 1459465412 | 41.14454 | -8.5629259 | 3
5338151 | 533815 | 1 | 2016-04-01 00:06:40+01 | 1459465600 | 41.163172 | -8.5838214 | 3THis是一个包含100多行的大表。我想要创建新的表temp_table，其中包含来自smf_table的过滤结果，以便在新表中：
row2_timestamp - row1_timestamp = 0.
- 不包括
travelmode列IS NULL(有许多列)的行， - 不包括
travelmode列IS NULL的行
所以我想用这种方式使用子查询：
CREATE TABLE temp_table
AS
WITH cte AS
(SELECT LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp)
- LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp)
FROM smf_table
)
SELECT id,
lat,
lon,
timestamp,
travel mode
FROM smf_table
WHERE travelmode IS NOT NULL AND cte !=0;
ERROR: relation "smf_table" does not exist
LINE 13: FROM smf_table回答 3
Stack Overflow用户
回答已采纳
发布于 2020-07-15 12:30:05
您不应该得到没有定义smf_table的错误。您可能会收到其他错误-- cte没有定义，CTE中的列没有名称，travel没有定义。
您需要从CTE中选择以使用其中的列。CTE类似于表/视图，而不是列：
WITH cte AS (
SELECT s.*,
LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp) - LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp) as diff
FROM smf_table s
)
SELECT id lat, lon, timestamp, travelmode
FROM cte
WHERE travelmode IS NOT NULL AND diff <> 0;Stack Overflow用户
发布于 2020-07-15 12:33:09
您必须从表中选择必需的列并执行操作，在选择最终结果时，必须从cte中选择而不是原始表。您还可以直接创建表，如下所示，无需递归查询。
CREATE TABLE temp_table as
SELECT SELECT LEAD(timestamp) OVER (PARTITION BY id ORDER BY timestamp)
- LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp) as date_time , id,
lat,
lon,
timestamp,
travel mode
FROM smf_table
WHERE travelmode IS NOT NULL AND cte !=0;Stack Overflow用户
发布于 2020-07-15 12:44:38
你为什么要从LAG()中减去LEAD()？您是否真的打算在忽略当前记录的同时，将以下记录与前面的记录进行比较？
另外，如果是timestamp1 - timestamp2 = 0，那么timestamp1 = timestamp2，所以这可以用group by来解决。
CREATE TABLE temp_table
AS
SELECT id,
max(lat) as lat,
max(lon) as lon,
timestamp,
max(travelmode) as travelmode
FROM smf_table
WHERE travelmode IS NOT NULL
GROUP by id, timestamp
HAVING count(*) = 1 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62914852
复制相关文章
相似问题
腾讯云开发者
