【SQL全般】SQLを使って条件に一致した１つ前のレコードを収集したい。

Question

前提

Google Apps Script(GAS)を使ってます。GASのQuery関数を使ってシートにあるデータから意図するレコードを抜き出したいです。ただ聞きたい事は一般的なSQL文の作り方の話なのでGASのQuery関数をSQLServerなど通常のSQLでの話と捉えて大丈夫です。

実現したいこと

「num列が2以上の場合その１つ前のレコードを抽出したい」

下記の例だとidが4と5のnumが2以上なのでidが3と4のレコードを取得したいです。
※先頭はカラム名です。

id／name／num
1／AAA／2.1
2／BBB／1.8
3／CCC／1.5
4／DDD／2.4
5／EEE／3
・・／・・／・・
100000／あああ／1.1

補足説明
・idは1から連番です。
・idが1もnum2以上ですが、その場合(その上にレコードが無いので)は取得しない想定です。
・全件取得後forでループさせれば取得できそうですが、実際は数万件以上データがありループだと処理に時間がかかりそうなので可能ならばSQLでデータを取得できればと思います。実現可能でしょうか？

Comments

[YAmaGNZ]

idは１からの連番なのですか？
また、id=1もnumが2以上ですがこの場合どうなるのですか？

[musyoku]

>idは１からの連番なのですか？
idは1から連番です。
>また、id=1もnumが2以上ですがこの場合どうなるのですか？
その上のレコードが無いので取得しないです。

質問内容を質問文に記載いたします。

[KOZ6.0]

ウインドウ関数の LAG が使えれば前のレコードを参照できます。
https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/lag

[YAmaGNZ]

実行するのは「スプレッドシートのQuery関数を使って」なのですよね？
そうなるとSQLServer等で使えるSQLとだいぶ差があると思うのですが一般的なSQLでいいんですかね？

[KOZ6.0]

んー？と思ってググったら Cloud SQL というのがあって、SQLServer や MySQL, PostgreSQL が使えるようです。

[YAmaGNZ]

質問者さんがどういう意味で「GASのQuery関数をSQLServerなど通常のSQLでの話と捉えて大丈夫です」と書かれているのか分かりませんが
スプレッドシートのQuery関数なのであれば
https://support.google.com/docs/answer/3093343?hl=ja
https://developers.google.com/chart/interactive/docs/querylanguage
とのことです。

[musyoku]

具体的に通常のSQLとQuery関数でできる差は把握していないですが、Query関数が無料でSQLっぽく扱える関数なので利用したかったんです。Cloud SQLもありますが従量課金で使用する形のなのでQuery関数で代替できないかなと思い質問しました。

Answer 1

ざっくり

create table tbl (id int ,name varchar(10),num float);
insert into tbl values
(1,'AAA',2.1),
(2,'BBB',1.8),
(3,'CCC',1.5),
(4,'DDD',2.4),
(5,'EEE',3);


select t1.* from tbl as t1
inner join tbl as t2
on t2.num>=2 and t1.id=t2.id-1

Comments

[yambejp]

連番が担保されている前提

[musyoku]

SQLならこれで対象のデータを抽出できそうです!!INNER JOINでID-1の考えはありませんでした。これをQUERY関数で実現できるか実装してみます。ありがとうございました。

Answer 2

SELECT A-1 WHERE C >= 2
みたいな感じで一旦numが2以上のidから1引いた値を列挙して
"SELECT * WHERE A MATCHES '^("& JOIN("|",E1:E11)&")$'"
と列挙したidを検索してはどうでしょう？

Comments

[musyoku]

QUERY関数での実装方法を書いて下さりありがとうございます。MATCHES以下の記載がまだ理解できていませんが、この書き方を踏まえて実装してみます。ありがとうございました。