postgresql - Creating Grouped features in sql -
i have sample records particular trialid table below:
personid trial orgid orgtypeid 90 106 72001 4 682 106 188787 4 717 106 187761 4 830 106 64776 4 845 106 71795 3 1130 106 215441 22 1199 106 25148 22 1488 106 84256 4 1656 106 31456 22 2427 106 71864 22 2535 106 40687 3 2558 106 93306 4 2792 106 114207 18 3105 106 13967 22 3267 106 22634 18 3352 106 22 3516 106 9847 12 3981 106 2341 4 3993 106 126593 3 4025 106 29789 4 4395 106 75068 3 4433 106 17622 3 4662 106 45286 4 4887 106 18350 22 5272 106 46979 18 5381 106 82499 3 5518 106 72958 3 5593 106 93363 3 5634 106 49553 18 5686 106 82504 22 6036 106 79250 3 6047 106 46228 4 6260 106 52492 4 6365 106 31196 3 6443 106 1980 4 6492 106 72940 3 6539 106 45287 4 6641 106 124592 4 6659 106 184510 4 6768 106 3950 4 7172 106 93065 22 7183 106 71916 4 7285 106 181783 22 8030 106 31044 4 8071 106 84539 22 8350 106 73263 4 8395 106 14974 4 8612 106 33084 12 8785 106 52983 4 8837 106 18335 4 8852 106 182042 3 8902 106 191396 3 8909 106 44678 12 8982 106 19 9318 106 149996 4 9538 106 31827 4 9570 106 85130 4 10028 106 159192 18 10105 106 38784 4 10226 106 80419 4 10459 106 79827 22 10518 106 71836 12 10733 106 71876 4 10843 106 78913 4 10853 106 140548 22 10880 106 71923 4 11301 106 72020 4 11522 106 33167 3 11595 106 71624 22 11699 106 91917 4 11714 106 93150 3 12200 106 34207 4 12226 106 71649 4 12308 106 136005 4 12832 106 71841 4 13468 106 71580 4 13745 106 92433 4 13907 106 164068 3 13935 106 39943 3 14122 106 130995 3 14146 106 101833 18 14520 106 38050 4 14542 106 39616 4 14560 106 85695 3 14712 106 95460 4 14806 106 30621 22 15479 106 72778 4 15549 106 130557 3 15790 106 39652 4 15982 106 52193 3 16046 106 62592 4 16064 106 75808 18 16268 106 51957 12 16908 106 78731 4 17198 106 53941 3 17248 106 44436 18 17830 106 80244 4 18585 106 396 22 18621 106 77528 4 18723 106 35721 22 19112 106 4928 4 19413 106 172057 3 19729 106 10096 3 19772 106 181063 3 19774 106 9858 4 19849 106 213470 4 19880 106 184341 4 20051 106 10388 3 20330 106 964 22 20337 106 162349 4 20574 106 216255 4 20619 106 42352 4 20666 106 10830 4 20746 106 19308 3 21123 106 114302 22 21190 106 41878 22 21261 106 33084 4 21264 106 84340 3 21651 106 52210 4 21682 106 167293 3 21775 106 80419 4 21930 106 154783 4 22017 106 78213 12 22026 106 11815 4 22924 106 57492 4 23149 106 46928 4 23775 106 141288 22 23831 106 162443 3 24887 106 9831 3 25279 106 71598 3 25357 106 75448 22 25468 106 98249 4 25651 106 4 25807 106 93306 4 26318 106 44212 22 26347 106 1143 3 26551 106 53264 3 27414 106 18 27424 106 126205 4 27556 106 147586 3 27582 106 145836 3 27774 106 7471 3 28004 106 119457 22 28684 106 3058 22 28686 106 16625 3 30026 106 144890 3 30031 106 32934 3 30035 106 40801 4 30039 106 177508 22 30238 106 72646 4 30430 106 82810 4 30471 106 46299 3 30944 106 184183 4 31482 106 42368 4 32296 106 196312 4 33801 106 13704 3 34903 106 347 3 35332 106 206713 3 36386 106 71612 4 38208 106 36293 3 38749 106 77258 18 38827 106 72071 4 39561 106 140106 4 39722 106 46471 3 39849 106 125641 18 40599 106 53061 4 42614 106 28447 18 42691 106 183045 22 43152 106 73580 19 43638 106 10321 3 47110 106 29789 4 47112 106 98308 4 48608 106 45292 4 48856 106 6700 4 48947 106 185045 3 48948 106 73515 4 49642 106 164536 4 49735 106 78342 4 50352 106 75696 12 50353 106 33699 4 51985 106 82265 12 52671 106 142891 4 52777 106 84193 22 52778 106 18406 3 53561 106 11674 3 53563 106 81427 4 53564 106 28320 4 54409 106 27689 4 54411 106 33761 3 54412 106 11695 22 54470 106 39649 4 54858 106 91972 22 55129 106 73820 4 55130 106 11815 22 55566 106 203178 9 55569 106 157517 4 55570 106 124075 4 56871 106 63954 3 57726 106 13484 3 60131 106 75956 3 61074 106 21444 3 61335 106 24403 4 63049 106 42236 3 65447 106 43170 22 65450 106 39728 22 65453 106 22211 22 65454 106 46546 3 67232 106 27052 18 68494 106 47882 3 68495 106 816 22 68936 106 106618 22 70192 106 173595 3 70193 106 115501 22 70197 106 34341 3 72606 106 18418 4 73104 106 144374 19 73685 106 51520 22 74397 106 102254 3 76808 106 44391 18 81334 106 34705 4 82894 106 45223 4 89882 106 154988 3 93661 106 51278 4 100468 106 4 100694 106 45358 18 100722 106 22 100789 106 9 100853 106 3 101307 106 159932 12 101326 106 39675 3 103620 106 48956 3 103668 106 44771 22 104706 106 142662 4 105923 106 76883 10 105961 106 75959 10 106555 106 72111 10 112996 106 71618 10 128637 106 138211 22 170771 106 117977 4 199749 106 72703 22 202961 106 32966 4 348476 106 49553 4 420379 106 21642 22 421422 106 24547 4 here personid id of person. trailid id looking at, orgid id of organization, orgtypeid type of org.
now want each group of trialid(this example has 1 trailid base table has multiple trialids),i want create 6 new variables indicated below:
non academic_fraction_orgs academic_frac_orgs others_frac_orgs non academic_fraction_ppl academic_frac_ppl others_frac_ppl orgtypeid : 9, 18, 3 grouped academic, while 4,22,17 grouped non-academic while remaining grouped others. need above variables following values:
non academic_fraction_orgs: total orgids in non-academic in trialid/total orgids in trialid academic_fraction_orgs: total orgids in academic in trialid/total orgids in trailed others_frac_orgs: total orgids in other in trialid/total orgids in trailed non academic_fraction_ppl: total distinct personid in non-academic in trialid/total orgids in trailed academic_fraction_ppl: total distinct personid in academic in trialid/total orgids in trailed and on..
how done in sql.
edit:
strorganizationtype string of respective orgtypeid.
select trialid, count(case when strorganizationtype in ('academic institution - office','academic institution - research facility', 'academic hospital / clinic') 1 else 0 end) /(count(*)*1.0) academic_frac_orgs, count(case when strorganizationtype in ('medical practice','clinical trial center', 'rehabilitation/extended care facility','non-academic hospital / clinic') 1 else 0 end)/(count(*)*1.0) non_academic_fraction_orgs, count(case when strorganizationtype in ('company','other') 1 else 0 end)/(count(*)*1.0) other_fraction_orgs table group trailid order trialid the problem count(case when strorganizationtype in ('company','other') 1 else 0 end) counting records trial , not belong particular case statement. need variable fraction of records belong group of orgs total orgs belong group.
Comments
Post a Comment