Sequence Symmetry Analysis – (SQL script)

こちらの記事で使用したSQLのスクリプトです

コマンドフィールド名
クエリ名0010 work_csr q
説 明
select_into[0010 work_csr]
select[_drug].PERSON_ID
select[_drug].D_STD
select[_condition].C_STD
selectSwitch([D_STD]<[C_STD],"D->C",[D_STD]>[C_STD],"C->D",True,"UNK") AS FLG INTO [0010 work_csr]
from_drug INNER JOIN
from_conditiON
from[_drug].PERSON_ID = [_condition].PERSON_ID
group by[_drug].PERSON_ID
group by[_drug].D_STD
group by[_condition].C_STD
group bySwitch([D_STD]<[C_STD],"D->C",[D_STD]>[C_STD],"C->D",True,"UNK")
クエリ名0020 work_out q
説 明
select_into[0020 work_out]
select[0010 work_csr].FLG
selectCount([0010 work_csr].PERSON_ID) AS [Count] INTO [0020 work_out]
from[0010 work_csr]
group by[0010 work_csr].FLG
クエリ名0030 work_output1 q
説 明
select_into[0030 work_output1]
selectSum(IIf([0020 work_out].[FLG]="D->C",[0020 work_out].[Count],0)) AS DC
selectSum(IIf([0020 work_out].[FLG]="C->D",[0020 work_out].[COUNT],0)) AS CD
selectSum([0020 work_out].Count) AS TOTAL
select[DC]/[CD] AS CSR INTO [0030 work_output1]
from[0020 work_out]
group by[DC]/[CD]
クエリ名0040 D_TOTAL q
説 明
select_into[0040 D_TOTAL]
selectCount([_drug].[PERSON_ID]) AS D_TOTAL INTO [0040 D_TOTAL]
from_drug
クエリ名0050 C_TOTAL q
説 明
select_into[0050 C_TOTAL]
selectCount([_condition].PERSON_ID) AS C_TOTAL INTO [0050 C_TOTAL]
from_condition
クエリ名0060 D_COUNT q
説 明
select_into[0060 D_COUNT]
select[_drug].[D_STD]
selectCount([_drug].[PERSON_ID]) AS D_COUNT INTO [0060 D_COUNT]
from_drug
group by[_drug].[D_STD]
クエリ名0070 D_COUNT2 q
説 明
select_into[0070 D_COUNT2]
select[0060 D_COUNT].D_STD
select[0060 D_COUNT].D_COUNT
select[_condition].PERSON_ID
select[_condition].C_STD INTO [0070 D_COUNT2]
from[0060 D_COUNT], _condition
where((([C_STD]-[D_STD])>0))
order by[0060 D_COUNT].D_STD
クエリ名0080 D_COUNT3 q
説 明
select_into[0080 D_COUNT3]
select[0070 D_COUNT2].D_STD
selectCount([0070 D_COUNT2].D_COUNT) AS [COUNT] INTO [0080 D_COUNT3]
from[0070 D_COUNT2]
group by[0070 D_COUNT2].D_STD
order by[0070 D_COUNT2].D_STD
クエリ名0090 D_COUNT4 q
説 明
select_into[0090 D_COUNT4]
select[0080 D_COUNT3].D_STD
select[0080 D_COUNT3].COUNT
select[0060 D_COUNT].D_COUNT INTO [0090 D_COUNT4]
from[0080 D_COUNT3] INNER JOIN
from[0060 D_COUNT] ON
from[0080 D_COUNT3].D_STD = [0060 D_COUNT].D_STD
order by[0080 D_COUNT3].D_STD
クエリ名0100 D_COUNT4 q
説 明
select_into[0100 D_COUNT4]
select[EXP_C] AS TOTAL_SUM
select[0090 D_COUNT4].D_STD
select[0090 D_COUNT4].COUNT
select[0090 D_COUNT4].D_COUNT
select[0050 C_TOTAL].[C_TOTAL] AS N
select[COUNT]/[N] AS P
select[P]*[D_COUNT] AS EXP_C INTO [0100 D_COUNT4]
from[0090 D_COUNT4], [0050 C_TOTAL]
group by[EXP_C]
group by[0090 D_COUNT4].D_STD
group by[0090 D_COUNT4].COUNT
group by[0090 D_COUNT4].D_COUNT
group by[0050 C_TOTAL].[C_TOTAL]
group by[COUNT]/[N]
group by[P]*[D_COUNT]
クエリ名0140 D_EXP q
説 明
select_into[0140 D_EXP]
selectSum([0100 D_COUNT4].EXP_C) AS D_EXP INTO [0140 D_EXP]
from[0100 D_COUNT4]
クエリ名0150 OUTPUT2 q
説 明
select_into[0150 OUTPUT2]
select[0140 D_EXP].[D_EXP]
select[0040 D_TOTAL].D_TOTAL
select[D_EXP]/[D_TOTAL] AS A
select[A]/(1-[A]) AS NSR INTO [0150 OUTPUT2]
from[0140 D_EXP], [0040 D_TOTAL]
クエリ名0160 ASR q
説 明
select_into[0160 ASR]
select[0030 work_output1].CSR
select[0150 OUTPUT2].NSR
select[CSR]/[NSR] AS ASR
select[0030 work_output1].CD
select[0030 work_output1].DC INTO [0160 ASR]
from[0030 work_output1], [0150 OUTPUT2]

コメントする

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です