{"id":826,"date":"2017-11-28T22:25:54","date_gmt":"2017-11-28T13:25:54","guid":{"rendered":"https:\/\/plaza.umin.ac.jp\/~OIO\/?p=826"},"modified":"2017-11-28T22:25:54","modified_gmt":"2017-11-28T13:25:54","slug":"sequence-symmetry-analysis-sql-script","status":"publish","type":"post","link":"https:\/\/plaza.umin.ac.jp\/~OIO\/?p=826","title":{"rendered":"Sequence Symmetry Analysis – (SQL script)"},"content":{"rendered":"

\u3053\u3061\u3089\u306e\u8a18\u4e8b<\/a>\u3067\u4f7f\u7528\u3057\u305fSQL\u306e\u30b9\u30af\u30ea\u30d7\u30c8\u3067\u3059<\/p>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
\u30b3\u30de\u30f3\u30c9<\/th>\u30d5\u30a3\u30fc\u30eb\u30c9\u540d<\/th>\n<\/tr>\n<\/thead>\n
\u30af\u30a8\u30ea\u540d<\/td>0010 work_csr q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0010 work_csr]<\/td>\n<\/tr>\n
select<\/td>[_drug].PERSON_ID<\/td>\n<\/tr>\n
select<\/td>[_drug].D_STD<\/td>\n<\/tr>\n
select<\/td>[_condition].C_STD<\/td>\n<\/tr>\n
select<\/td>Switch([D_STD]<[C_STD],\"D->C\",[D_STD]>[C_STD],\"C->D\",True,\"UNK\") AS FLG INTO [0010 work_csr]<\/td>\n<\/tr>\n
from<\/td>_drug INNER JOIN <\/td>\n<\/tr>\n
from<\/td>_conditiON <\/td>\n<\/tr>\n
from<\/td>[_drug].PERSON_ID = [_condition].PERSON_ID<\/td>\n<\/tr>\n
group by<\/td>[_drug].PERSON_ID<\/td>\n<\/tr>\n
group by<\/td>[_drug].D_STD<\/td>\n<\/tr>\n
group by<\/td>[_condition].C_STD<\/td>\n<\/tr>\n
group by<\/td>Switch([D_STD]<[C_STD],\"D->C\",[D_STD]>[C_STD],\"C->D\",True,\"UNK\")<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0020 work_out q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0020 work_out]<\/td>\n<\/tr>\n
select<\/td>[0010 work_csr].FLG<\/td>\n<\/tr>\n
select<\/td>Count([0010 work_csr].PERSON_ID) AS [Count] INTO [0020 work_out]<\/td>\n<\/tr>\n
from<\/td>[0010 work_csr]<\/td>\n<\/tr>\n
group by<\/td>[0010 work_csr].FLG<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0030 work_output1 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0030 work_output1]<\/td>\n<\/tr>\n
select<\/td>Sum(IIf([0020 work_out].[FLG]=\"D->C\",[0020 work_out].[Count],0)) AS DC<\/td>\n<\/tr>\n
select<\/td>Sum(IIf([0020 work_out].[FLG]=\"C->D\",[0020 work_out].[COUNT],0)) AS CD<\/td>\n<\/tr>\n
select<\/td>Sum([0020 work_out].Count) AS TOTAL<\/td>\n<\/tr>\n
select<\/td>[DC]\/[CD] AS CSR INTO [0030 work_output1]<\/td>\n<\/tr>\n
from<\/td>[0020 work_out]<\/td>\n<\/tr>\n
group by<\/td>[DC]\/[CD]<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0040 D_TOTAL q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0040 D_TOTAL]<\/td>\n<\/tr>\n
select<\/td>Count([_drug].[PERSON_ID]) AS D_TOTAL INTO [0040 D_TOTAL]<\/td>\n<\/tr>\n
from<\/td>_drug<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0050 C_TOTAL q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0050 C_TOTAL]<\/td>\n<\/tr>\n
select<\/td>Count([_condition].PERSON_ID) AS C_TOTAL INTO [0050 C_TOTAL]<\/td>\n<\/tr>\n
from<\/td>_condition<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0060 D_COUNT q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0060 D_COUNT]<\/td>\n<\/tr>\n
select<\/td>[_drug].[D_STD]<\/td>\n<\/tr>\n
select<\/td>Count([_drug].[PERSON_ID]) AS D_COUNT INTO [0060 D_COUNT]<\/td>\n<\/tr>\n
from<\/td>_drug<\/td>\n<\/tr>\n
group by<\/td>[_drug].[D_STD]<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0070 D_COUNT2 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0070 D_COUNT2]<\/td>\n<\/tr>\n
select<\/td>[0060 D_COUNT].D_STD<\/td>\n<\/tr>\n
select<\/td>[0060 D_COUNT].D_COUNT<\/td>\n<\/tr>\n
select<\/td>[_condition].PERSON_ID<\/td>\n<\/tr>\n
select<\/td>[_condition].C_STD INTO [0070 D_COUNT2]<\/td>\n<\/tr>\n
from<\/td>[0060 D_COUNT], _condition<\/td>\n<\/tr>\n
where<\/td>((([C_STD]-[D_STD])>0))<\/td>\n<\/tr>\n
order by<\/td>[0060 D_COUNT].D_STD<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0080 D_COUNT3 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0080 D_COUNT3]<\/td>\n<\/tr>\n
select<\/td>[0070 D_COUNT2].D_STD<\/td>\n<\/tr>\n
select<\/td>Count([0070 D_COUNT2].D_COUNT) AS [COUNT] INTO [0080 D_COUNT3]<\/td>\n<\/tr>\n
from<\/td>[0070 D_COUNT2]<\/td>\n<\/tr>\n
group by<\/td>[0070 D_COUNT2].D_STD<\/td>\n<\/tr>\n
order by<\/td>[0070 D_COUNT2].D_STD<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0090 D_COUNT4 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0090 D_COUNT4]<\/td>\n<\/tr>\n
select<\/td>[0080 D_COUNT3].D_STD<\/td>\n<\/tr>\n
select<\/td>[0080 D_COUNT3].COUNT<\/td>\n<\/tr>\n
select<\/td>[0060 D_COUNT].D_COUNT INTO [0090 D_COUNT4]<\/td>\n<\/tr>\n
from<\/td>[0080 D_COUNT3] INNER JOIN <\/td>\n<\/tr>\n
from<\/td>[0060 D_COUNT] ON <\/td>\n<\/tr>\n
from<\/td>[0080 D_COUNT3].D_STD = [0060 D_COUNT].D_STD<\/td>\n<\/tr>\n
order by<\/td>[0080 D_COUNT3].D_STD<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0100 D_COUNT4 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0100 D_COUNT4]<\/td>\n<\/tr>\n
select<\/td>[EXP_C] AS TOTAL_SUM<\/td>\n<\/tr>\n
select<\/td>[0090 D_COUNT4].D_STD<\/td>\n<\/tr>\n
select<\/td>[0090 D_COUNT4].COUNT<\/td>\n<\/tr>\n
select<\/td>[0090 D_COUNT4].D_COUNT<\/td>\n<\/tr>\n
select<\/td>[0050 C_TOTAL].[C_TOTAL] AS N<\/td>\n<\/tr>\n
select<\/td>[COUNT]\/[N] AS P<\/td>\n<\/tr>\n
select<\/td>[P]*[D_COUNT] AS EXP_C INTO [0100 D_COUNT4]<\/td>\n<\/tr>\n
from<\/td>[0090 D_COUNT4], [0050 C_TOTAL]<\/td>\n<\/tr>\n
group by<\/td>[EXP_C]<\/td>\n<\/tr>\n
group by<\/td>[0090 D_COUNT4].D_STD<\/td>\n<\/tr>\n
group by<\/td>[0090 D_COUNT4].COUNT<\/td>\n<\/tr>\n
group by<\/td>[0090 D_COUNT4].D_COUNT<\/td>\n<\/tr>\n
group by<\/td>[0050 C_TOTAL].[C_TOTAL]<\/td>\n<\/tr>\n
group by<\/td>[COUNT]\/[N]<\/td>\n<\/tr>\n
group by<\/td>[P]*[D_COUNT]<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0140 D_EXP q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0140 D_EXP]<\/td>\n<\/tr>\n
select<\/td>Sum([0100 D_COUNT4].EXP_C) AS D_EXP INTO [0140 D_EXP]<\/td>\n<\/tr>\n
from<\/td>[0100 D_COUNT4]<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0150 OUTPUT2 q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0150 OUTPUT2]<\/td>\n<\/tr>\n
select<\/td>[0140 D_EXP].[D_EXP]<\/td>\n<\/tr>\n
select<\/td>[0040 D_TOTAL].D_TOTAL<\/td>\n<\/tr>\n
select<\/td>[D_EXP]\/[D_TOTAL] AS A<\/td>\n<\/tr>\n
select<\/td>[A]\/(1-[A]) AS NSR INTO [0150 OUTPUT2]<\/td>\n<\/tr>\n
from<\/td>[0140 D_EXP], [0040 D_TOTAL]<\/td>\n<\/tr>\n
\u30af\u30a8\u30ea\u540d<\/td>0160 ASR q<\/td>\n<\/tr>\n
\u8aac\u3000\u660e<\/td><\/td>\n<\/tr>\n
select_into<\/td>[0160 ASR]<\/td>\n<\/tr>\n
select<\/td>[0030 work_output1].CSR<\/td>\n<\/tr>\n
select<\/td>[0150 OUTPUT2].NSR<\/td>\n<\/tr>\n
select<\/td>[CSR]\/[NSR] AS ASR<\/td>\n<\/tr>\n
select<\/td>[0030 work_output1].CD<\/td>\n<\/tr>\n
select<\/td>[0030 work_output1].DC INTO [0160 ASR]<\/td>\n<\/tr>\n
from<\/td>[0030 work_output1], [0150 OUTPUT2]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n","protected":false},"excerpt":{"rendered":"

\u3053\u3061\u3089\u306e\u8a18\u4e8b\u3067\u4f7f\u7528\u3057\u305fSQL\u306e\u30b9\u30af\u30ea\u30d7\u30c8\u3067\u3059<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-826","post","type-post","status-publish","format-standard","hentry","category-miscellaneous"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p9b6zl-dk","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=\/wp\/v2\/posts\/826","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=826"}],"version-history":[{"count":0,"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=\/wp\/v2\/posts\/826\/revisions"}],"wp:attachment":[{"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=826"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/plaza.umin.ac.jp\/~OIO\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}