从带有lead/lag的select查询创建表

wlwcrazw 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(303)

这就是我手头的table：

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 |          3

这是一个包含100多行的大表。我要创建新表 temp_table 从中筛选结果 smf_table 在新表中：
不包括行，其中 travelmode 柱 IS NULL （有很多）
不包括行，其中 row2_timestamp - row1_timestamp = 0 .
所以我想到了这样使用子查询：

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

sql postgresql aggregate-functions sql-function

来源：https://stackoverflow.com/questions/62914852/create-a-table-from-a-select-query-with-lead-lag

3条答案

按热度按时间

u91tlkcl1#

你不应该得到错误 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;

赞(0）回复(0）举报 2021-07-26

qeeaahzv2#

必须从表中选择所需的列并执行操作，在选择最终结果时，必须从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;

赞(0）回复(0）举报 2021-07-26

vx6bjr1n3#

你为什么要减法 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

赞(0）回复(0）举报 2021-07-26

我来回答

从带有lead/lag的select查询创建表

3条答案

相关问题

热门标签

最新问答