[SQL]時系列データで前後のステータスから新たなラベル付けを行いたい

Question

時系列データに対して前後のステータス関係から新たなラベル付けを行いたいと考えています。
タクシーのプローブデータを用いていますが、乗車した瞬間だけを新たなに「乗車」というラベルに変更しようと考えています。

ラベル付けを行う理由としては乗車した場所を特定するために、乗車した瞬間だけラベルを変更しようと思っています。
降車した場所については「支払」というラベルが既に付与されているのでそのままで問題ないと考えています。

（プローブデータのデータ解析は初めてなのでこの前処理方法が正しいかどうかはわかりませんが、現状このようなやり方で行こうと思っています。プローブデータに対するデータ分析の知識がある方、併せてそのあたりの知識もご教授いただけると幸いです）

具体的には下図の４行目から実車に変わっているので、4行目の瞬間に「乗車」したと考えられます。ですので4行目だけ「乗車」ラベルにしたいです。
私が思いつく限りのやり方としては、実車の１つ前の行が「空車」のラベルであれば、「乗車」に変更、という風に考えています。
他のやり方でもいいのでどなたかご教授いただけると幸いです。SQLiteです。

Answer 1

Window関数の**LAG()**で前行を参照すれば判断できますね。

select *
     , case when VehicleStatus='実車' and lag_VehicleStatus='空車' 
       then '乗車' else VehicleStatus end as edit_VehicleStatus
from (
  select *, lag(VehicleStatus) over(order by StatusTime) as lag_VehicleStatus
  from テーブル
) t

※分かり易くする為にネストしているので、多分ネストしなくても大丈夫だと思います。
1. Introduction to Window Functions
SQLite LAG
SQLite3でWindow関数を試してみた！

Comments

[ha_horse]

ご回答ありがとうございます。
ただいまご教授いただいた通りに実行をしましたらシンタックスエラーが出てしまいました。
色々と試行錯誤して試してみたのですが、どうもover()の部分でエラーが出てしまうようです。

エラー文は下記です。

near "(": syntax error: select *
, case when VehicleStatus='実車' and lag_VehicleStatus='空車'
then '乗車' else VehicleStatus end as edit_VehicleStatus
from (
select *, lag(VehicleStatus) over(

[sazi]

select *, lag(VehicleStatus) over(order by StatusTime) as lag_VehicleStatus from テーブル
の部分だけでもエラーが出ますか？

[ha_horse]

ご対応ありがとうございます。

はい、そちらの既に試していましたが同様にエラーが出ます。

near "(": syntax error: select *, lag(VehicleStatus) over(

[sazi]

では、以下のように*ではなく修飾を付けてみてください。
select t.*, lag(VehicleStatus) over(order by StatusTime) as lag_VehicleStatus from テーブル t

[sazi]

因みにWindow関数が使えるのは、「SQLite 3.25」以降です。

[ha_horse]

ご対応ありがとうございます。
同様にエラーが出てしまいます。

near "(": syntax error: select t.*, lag(VehicleStatus) over(

関係ないかもしれませんが、DB Browser for SQLiteというGUIツールを用いてクエリを実行しています。

[sazi]

”over(”の所で切れているようにも見えますね。