WINDOW関数（LEAD）１レコード後の項目取得

-- テーブル作成
create table Test(id integer, day integer, among integer, limited integer);

-- データ登録
-- Date型　→　文字列　DATE_FORMAT(current_date,'%Y%m%d')
-- 文字列　→　数値型　CAST(対象 as signed)
insert into Test(id, day, among, limited) values(1, CAST(DATE_FORMAT(current_date,'%Y%m%d') as signed), 500, 800);
insert into Test(id, day, among, limited) values(2, CAST(DATE_FORMAT(current_date - 1,'%Y%m%d') as signed), 600, 800);
insert into Test(id, day, among, limited) values(3, CAST(DATE_FORMAT(current_date - 2,'%Y%m%d') as signed), 700, 600);
insert into Test(id, day, among, limited) values(3, CAST(DATE_FORMAT(current_date - 3,'%Y%m%d') as signed), 400, 700);
-- select * from Test;

-- WINDOW関数　LEAD　前行項目の取得
select * from (
select
  id
, day
, among
, lead(limited, 1) over (partition by id order by day desc) as zenLimit
, limited
from Test
) a
-- 金額と前日（なければ同日）限界値の比較
where a.among < coalesce(a.zenLimit, a.limited, 0)
-- 実行日のデータのみ
and a.day = CAST(DATE_FORMAT(current_date,'%Y%m%d') as signed)
;
-- 結果
id day among zenLimit limited
1 20231216 500 NULL 800