SQLにおいて比率の推移を算出する方法について

Question

例えば、以下のようなDBのテーブルがあって、3レコード毎にステータスの比率を出すにはどうしたら良いのでしょうか？

ID	ステータス
1	生存
2	病気
3	死亡
4	死亡
5	死亡
6	生存
7	病気
8	生存
9	生存
10	病気
11	病気
12	病気

上記アウトプットとしては
|ID|生存|病気|死亡|
|:--|:--:|--:|
|1|0.33|0.33|0.33|
|2|0.33|0|0.66|
|3|0.66|0.33|0|
|4|0|1|0|

のような感じを想定しています。
どのようなSQLを書けば実現できますでしょうか？どなたかご回答いただけますと幸いです。

環境なのですが、
Re:dashを更新してAWS上でMysqlにSQLを投げる認識です。

よろしくお願いいたします。

Comments

[sazi]

DBの明示をタグで行って下さい。バージョンは質問内に追記して下さい。

[yambejp]

結果のIDがおかしいです
結果のID=4は10/11/12を取るのでは？
そうなると病気=1/死亡=0ではないのですか？

[sazi]

IDは連番でしょうか？
また、欠番はありますか？
3レコードの定義は？

[yambejp]

できればご使用のSQLの種類も指定されたほうがよいかも。

[Orlofsky]

0.33 などの割合を導くための情報を持っているテーブルが必要では？

[Orlofsky]

テーブル定義とデータは CREATE TABLEとINSERTに直した方が動作確認できるので、適切なコメントが付き易いです。

Answer 1

引用テキストのような感じを想定しています

の表の4番が怪しいですが、Postgresで作成しています。

select ID+1 as ID,
  substr(cast(avg(生存) as text),1,4) as 生存,
  substr(cast(avg(病気) as text),1,4) as 病気,
  substr(cast(avg(死亡) as text),1,4) as 死亡
  from (
    select (ID-1) / 3 ID,
    case when ステータス='生存' then 1 else 0 end as 生存,
    case when ステータス='病気' then 1 else 0 end as 病気,
    case when ステータス='死亡' then 1 else 0 end as 死亡
  from tblA) as t1
group by ID
order by 1;

Answer 2

ざっくりこんな感じで？

Comments

[yambejp]

create table tbl(id int primary key,status varchar(10));
insert into tbl values
(1,'生存'),
(2,'病気'),
(3,'死亡'),
(4,'死亡'),
(5,'死亡'),
(6,'生存'),
(7,'病気'),
(8,'生存'),
(9,'生存'),
(10,'病気'),
(11,'病気'),
(12,'病気');

[yambejp]

select CEIL(id/3) as newid,
sum(status='生存')/count(*) as alive,
sum(status='病気')/count(*) as ill,
sum(status='死亡')/count(*) as dead
from tbl
group by newid;

[yambejp]

あ、たしかにavgでよさげですね
select CEIL(id/3) as newid,
avg(status='生存') as alive,
avg(status='病気') as ill,
avg(status='死亡') as dead
from tbl
group by newid;