sql:window関数で先月末日（最終）時点の値を取得したい

Question

bigquery を利用しています。
やりたいこと
window関数を利用して先月末日時点の値を取得したいです。
bigquery-public-dataにて試したコードが下記ですが、月単位で降順に並べた時の
最初の値は取得できますが、条件として先月末はどのように設定すれば良いでしょうか。

例ではtaxi_idとtrip_start_timestampを月単位したものをpartitonに指定し、
最初の値を取得することはできたのですが、ここから条件設定で詰まっております。
where句をtrip_start_timestampを基準に先月末を指定することになるではないかと考えますが、
アドバイスをいただますと助かります。。

bigquery standard sql

SELECT 
taxi_id
,trip_start_timestamp
,payment_type
,company
,FIRST_VALUE(payment_type) over (PARTITION BY taxi_id,extract(month from trip_start_timestamp)
ORDER BY trip_start_timestamp desc)
 FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips` 

INSERT INTO taxidata (taxi_id, trip_start_timestamp, payment_type, company) VALUES
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-03-14 00:45:00 UTC','Credit Card', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-03-14 00:45:00 UTC','Credit Card', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-03-12 23:30:00 UTC','Credit Card', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-03-12 19:45:00 UTC','Cash', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-03-14 00:45:00 UTC','Credit Card', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-02-24 08:15:00 UTC','Cash', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-02-23 08:15:00 UTC','Credit Card', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-02-28 08:15:00 UTC','Cash', 'Flash Cab'),
    ('bc3b6178088532bbcb1b98f3aaed977', '2020-01-24 08:15:00 UTC','Credit Card', 'Flash Cab');

期待する結果
2020-03-14 00:45:00UTC　レコード　に対しては2020-02-28 08:15:00UTCの type cash
2020-02-23 08:15:00UTC レコード　に対しては2020-01-24 08:15:00UTCの type Credit Card

Comments

[Orlofsky]

質問に現行のSELECT結果、CREATE TABLEや既に存在するデータをINSERTで何件か提示できた方がコメントが付きやすくなるのでは？

[manataku]

コメントありがとうございます。
現行の結果と期待する結果について補足致しました。

[neko_the_shadow]

やりたいことは「すべての行に対して、trip_start_timestampの前月末のデータのpayment_typeの値を付与したい」ということであっていますか?
- trip_start_timestamp = 2020-03-14 の場合 ... trip_start_timestamp = 2020-02-24のpayment_typeを付与する
- trip_start_timestamp = 2020-03-12 の場合 ... trip_start_timestamp = 2020-02-24のpayment_typeを付与する
- trip_start_timestamp = 2020-02-24 の場合 ... trip_start_timestamp = 2020-01-24のpayment_typeを付与する
- trip_start_timestamp = 2020-02-23 の場合 ... trip_start_timestamp = 2020-01-24のpayment_typeを付与する

[manataku]

neko_the_shadow さん
>「すべての行に対して、trip_start_timestampの前月末のデータのpayment_typeの値を付与したい」
はい、その通りです。

Answer 1

当月の行に先月末の値を出力するには、当月の行が含まれない範囲でグループ化した結果を出力することになるので、window関数での直接的な実現は無理な気がします。

window関数を使わなければ、以下のように先月末のみの行を持つ中間テーブルを作り、元のテーブルに結合する方法があると思います。（テーブル名はtaxidataにしています。）

SELECT * FROM taxidata t
LEFT JOIN
(
SELECT * FROM taxidata
WHERE trip_start_timestamp IN (
  SELECT MAX(trip_start_timestamp) FROM taxidata
  GROUP BY extract(month from trip_start_timestamp))
) u
ON extract(month from t.trip_start_timestamp)=
   extract(month from DATE_ADD(u.trip_start_timestamp, INTERVAL +1 MONTH))