PostgreSQL 検索結果がnullの場合レコードを遡って取得したい

Question

前提・実現したいこと

ZZZ teble
ID　　計測日 身長 体重 BMI(体重/身長(m)²)
001 21/09/01 160 55 21.48
001 21/09/09 null 　56 null
001 21/09/15 161 55 21.22
002 21/07/18 188 88 24.90
002 21/09/15 189 null null
003 21/08/08 null null null
003 21/09/15 155 null null

select * from ZZZ where 計測日 = 21/09/15

001 21/09/15 161 55 21.22
002 21/09/15 189 null null
003 21/09/15 155 null null

やりたいこと

検索結果がnullだった場合に計測日を遡ってnullではない値を出したいです。

---

001 21/09/15 161 55 21.22
002 21/07/18 188 88 　24.90
003 21/09/15 155 null　null

皆様のお力添えを頂ければと思います宜しくお願い致します。

Comments

[takanaweb5]

>検索結果がnullだった場合に計測日を遡ってnullではない値を出したいです。

身長 体重 BMI いずれかの列がnullだった場合、対象外とするということですね。
ただし、対象のレコードがない場合は、遡って最大の日付のレコードを返すということですね。

[perosama]

BMIは体重/身長(m)²で計算しているので、体重、身長のどちらかがnullでもBMIはnullになります。

Answer 1

手書きで検証はしていません。
※BMIを条件にしていますので、身長・体重が条件の場合は適宜変更して下さい。

select * from ZZZ
where (ID, 計測日) in (
        select ID, coalesce(max(case when BMI is not Null then 計測日 end), max(計測日)) 
        from  ZZZ 
        where 計測日 <= 21/09/15
        group by ID
      )               

Answer 2

まだ出ていない方法として、ORDER BY句による指定でもご希望の結果になりそうですね。

CREATE TABLE ZZZ
(
    ID      varchar(10)  not null
  , 計測日  date         not null
  , 身長    float
  , 体重    float
  , BMI     float
);

INSERT INTO ZZZ
    ( ID, 計測日, 身長, 体重, BMI )
VALUES
    ( '001', '2021-09-01',  160,   55, 21.48 )
  , ( '001', '2021-09-09', Null,   56,  Null )
  , ( '001', '2021-09-15',  161,   55, 21.22 )
  , ( '002', '2021-07-18',  188,   88, 24.90 )
  , ( '002', '2021-09-15',  189, Null,  Null )
  , ( '003', '2021-08-08', Null, Null,  Null )
  , ( '003', '2021-09-15',  155, Null,  Null )
  , ( '004', '2021-09-01',  157,   45, 18.26 )
  , ( '004', '2021-09-15',  156,   43, 17.67 )
  , ( '005', '2021-09-01', Null,   88,  Null )
  , ( '005', '2021-09-15',  171, Null,  Null )
;

SELECT * FROM ZZZ
WHERE 計測日 <= '2021-09-15'::date
ORDER BY rank() 
             over( partition by ID
                       order by ( 身長 is not null )::int 
                                    +
                                ( 体重 is not null )::int desc
                              , 計測日 desc
             )
       , ID
   LIMIT ( 
             SELECT count( distinct( ID ) ) 
               FROM ZZZ
              WHERE 計測日 <= '2021-09-15'::date
         )
;

 id  |   計測日   | 身長 | 体重 |  bmi  
-----+------------+------+------+-------
 001 | 2021-09-15 |  161 |   55 | 21.22
 002 | 2021-07-18 |  188 |   88 |  24.9
 003 | 2021-09-15 |  155 |      |      
 004 | 2021-09-15 |  156 |   43 | 17.67
 005 | 2021-09-15 |  171 |      |      

　　
( 以下、仕様としては考えにくいですが参考までに )

もし、身長・体重どちらかの数値しか入力されていないレコードに
計測日より上位の優先度を設定する場合は、優先したい項目の入力に重みを付与します。仮に
( 体重 is not null )::int * 2
という記述にすると
データ例 ID:005 の表示対象は 2021-09-01 のレコードに変更されます。

Answer 3

入力の計測日から遡って
身長・体重 いずれかの列がnullだった場合は、それ以前のnullでないレコードを返し、
対象のレコードがない場合は、入力の計測日以前の最大の日付のレコードを返します。

CREATE TABLE ZZZ
    (ID int, 計測日 date, 身長 int, 体重 int, BMI float);
    
INSERT INTO ZZZ
VALUES
    (001, '2021-09-01', 160,  55,  21.48),
    (001, '2021-09-09', NULL, 56,  NULL),
    (001, '2021-09-15', 161,  55,  21.22),
    (002, '2021-07-18', 188,  88,  24.90),
    (002, '2021-09-15', 189,  NULL, NULL),
    (003, '2021-08-08', NULL, NULL, NULL),
    (003, '2021-09-15', 155,  NULL, NULL);

WINDOW関数を使用した例を紹介します。

WITH SUB AS (
SELECT *
-- 身長・体重が両方入力された最大の計測日を取得する
, MAX(CASE WHEN 身長 IS NOT NULL AND 
                体重 IS NOT NULL THEN 計測日 END) 
             OVER (PARTITION BY ID) AS 直前計測日
-- 入力の計測日以前の最大の計測日を取得する
, MAX(計測日) OVER (PARTITION BY ID) AS 最大日
FROM ZZZ
WHERE 計測日 <= '2021/09/15' -- 入力の計測日を指定する
)

SELECT ID, 計測日, 身長, 体重, BMI
FROM SUB
-- 対象のレコードがない場合は、入力の計測日以前の最大の日付のレコードを返す
WHERE 計測日 = COALESCE(直前計測日,最大日)

ID	計測日	身長	体重	BMI
1	2021/09/15	161	55	21.22
2	2021/07/18	188	88	24.9
3	2021/09/15	155	NULL	NULL

参考までにサブクエリの実行結果は以下の通り

SELECT * FROM SUB

ID	計測日	身長	体重	BMI	直前計測日	最大日
1	2021/09/01	160	55	21.48	2021-09-15	2021/09/15
1	2021/09/09	NULL	56	NULL	2021-09-15	2021/09/15
1	2021/09/15	161	55	21.22	2021-09-15	2021/09/15
2	2021/07/18	188	88	24.9	2021-07-18	2021/09/15
2	2021/09/15	189	NULL	NULL	2021-07-18	2021/09/15
3	2021/08/08	NULL	NULL	NULL	NULL	2021/09/15
3	2021/09/15	155	NULL	NULL	NULL	2021/09/15

追記：
@sousuke さんや @mayu- さんのアイデアを応用すれば、サブクエリなしのSQLでも実現可能です。

SELECT DISTINCT
  ID
-- BMIが設定されている行を優先して、計測日の昇順にソートした最後の行の値(=降順にソートした最初の行)を取得します
-- 単純に昇順にソートしてLAST_VALUE()を使用しても意図した結果が得られないので、トリッキーな方法を使用しています
, FIRST_VALUE(身長) OVER W AS 身長
, FIRST_VALUE(体重) OVER W AS 体重
, FIRST_VALUE(BMI)  OVER W AS BMI
FROM ZZZ
WINDOW W AS (PARTITION BY ID ORDER BY (BMI IS NULL), 計測日 DESC)
ORDER BY ID

Answer 4

IDをキーとするテーブル「IDs」がありさらにIDが
001
002
003
の行がある前提だと

select C.*
from IDs A
  left join ZZZ C on C."ID"=A."ID" and C."計測日"=(
    select B."計測日"
    from ZZZ B
    where B."ID"=A."ID"
   ORDER BY case when B."身長" is not null then 0 else 1 end
      ,case when B."体重" is not null then 0 else 1 end
      ,B."計測日" desc
   LIMIT 1 
  )

でいけるかも。検証はしてません。
ZZZに一意のキーがあるともう少しだけ楽ですね…

Answer 5

体重 is not null