/*会員の健康診断結果を記録している健康診断テーブル(HEALTH_CHECKUP)より、
会員毎に実施日(CHECKUP_DATE)が直近2回の健康診断結果で、
今回の体重(WEIGHT)から前回の体重を減算した結果が、5kg以上変動した会員を表示しなさい。
体重はkg単位で小数第一位まで登録されているものとする。
また、変動した体重を算出する際は丸め誤差が発生しないように注意すること。
ただし、小数点以下がゼロの場合は整数のみの表示とする。
表示項目は以下とする。(エイリアスを使用し→の項目名とする)
最新の実施日 → CK_DATE
MEMBER_CODE → CODE
LAST_NAMEとFIRST_NAMEを連結して表示 → NAME
変動した体重 → CHG_WT
表示順
変動した体重の降順
MEMBER_CODEの降順*/
--最新の実施日と前回との差を計算
WITH DIFF_TABLE AS
(SELECT 
FIRST_VALUE(CHECKUP_DATE)
OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE DESC) AS CK_DATE,
CHECKUP_DATE,
MEMBER_CODE,
WEIGHT,
(WEIGHT *10 - LAG(WEIGHT*10)
OVER(PARTITION BY MEMBER_CODE ORDER BY CHECKUP_DATE))/10 AS DIFF
FROM HEALTH_CHECKUP
ORDER BY MEMBER_CODE,CHECKUP_DATE)
--最新の実施日に5KG以上変化した人に絞る&まとめ
SELECT 
CK_DATE,
MEMBER_CODE AS CODE,
LAST_NAME||FIRST_NAME AS NAME,
DIFF AS CHG_WT
FROM DIFF_TABLE
JOIN MEMBER_MST
USING(MEMBER_CODE)
WHERE CK_DATE = CHECKUP_DATE AND ABS(DIFF) >= 5
ORDER BY CHG_WT DESC,MEMBER_CODE DESC
;