extras/NarrowEffectSpark.md

In WinVector/rquery: Relational Query Generator for Data Manipulation at Scale

NarrowEffect

Win-Vector LLC

For some time we have been teaching R users "when working with wide tables on Spark or on databases: narrow to the columns you really want to work with early in your analysis."

This issue arises because wide tables (200 to 1000 columns) are quite common in big-data analytics projects. Often these are "denormalized marts" that are used to drive many different projects. For any one project only a small subset of the columns may be relevant in a calculation.

The idea behind the advice is: working with fewer columns makes for quicker queries.

Let's set up our experiment. The data is a larger version of the problem from "Let’s Have Some Sympathy For The Part-time R User". We have expanded the number of subjects to 50000 and added 500 irrelevant columns to the example. We define a new function that uses dplyr and Sparklyr to compute the diagnoses. We vary if the table is first limited to columns of interest and if the results are brought back to R.

scale <- 0.237

dT %>%
  select(subjectID, surveyCategory, assessmentTotal) %>%
  group_by(subjectID) %>%
  mutate(probability =
           exp(assessmentTotal * scale)/
           sum(exp(assessmentTotal * scale), na.rm = TRUE)) %>%
  arrange(probability, surveyCategory) %>%
  filter(row_number() == n()) %>%
  ungroup() %>%
  rename(diagnosis = surveyCategory) %>%
  select(subjectID, diagnosis, probability) %>%
  arrange(subjectID) %>%
  dbplyr::remote_query(.) %>%
  cat

## SELECT `subjectID`, `diagnosis`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory` AS `diagnosis`, `assessmentTotal`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, `probability`, row_number() OVER (PARTITION BY `subjectID` ORDER BY `probability`, `surveyCategory`) AS `zzz3`, COUNT(*) OVER (PARTITION BY `subjectID`) AS `zzz4`
## FROM (SELECT *
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, EXP(`assessmentTotal` * 0.237) / sum(EXP(`assessmentTotal` * 0.237)) OVER (PARTITION BY `subjectID`) AS `probability`
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`
## FROM `d`) `wipkzwdcjq`) `gpqwmquprh`
## ORDER BY `probability`, `surveyCategory`) `dahbahfywm`) `japepfskyh`
## WHERE (`zzz3` = `zzz4`)) `rtjooploux`) `gputwewead`
## ORDER BY `subjectID`

dT %>%
  group_by(subjectID) %>%
  mutate(probability =
           exp(assessmentTotal * scale)/
           sum(exp(assessmentTotal * scale), na.rm = TRUE)) %>%
  arrange(probability, surveyCategory) %>%
  filter(row_number() == n()) %>%
  ungroup() %>%
  rename(diagnosis = surveyCategory) %>%
  select(subjectID, diagnosis, probability) %>%
  arrange(subjectID) %>%
  dbplyr::remote_query(.) %>%
  cat

## SELECT `subjectID`, `diagnosis`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory` AS `diagnosis`, `assessmentTotal`, `irrelevantCol_0000001`, `irrelevantCol_0000002`, `irrelevantCol_0000003`, `irrelevantCol_0000004`, `irrelevantCol_0000005`, `irrelevantCol_0000006`, `irrelevantCol_0000007`, `irrelevantCol_0000008`, `irrelevantCol_0000009`, `irrelevantCol_0000010`, `irrelevantCol_0000011`, `irrelevantCol_0000012`, `irrelevantCol_0000013`, `irrelevantCol_0000014`, `irrelevantCol_0000015`, `irrelevantCol_0000016`, `irrelevantCol_0000017`, `irrelevantCol_0000018`, `irrelevantCol_0000019`, `irrelevantCol_0000020`, `irrelevantCol_0000021`, `irrelevantCol_0000022`, `irrelevantCol_0000023`, `irrelevantCol_0000024`, `irrelevantCol_0000025`, `irrelevantCol_0000026`, `irrelevantCol_0000027`, `irrelevantCol_0000028`, `irrelevantCol_0000029`, `irrelevantCol_0000030`, `irrelevantCol_0000031`, `irrelevantCol_0000032`, `irrelevantCol_0000033`, `irrelevantCol_0000034`, `irrelevantCol_0000035`, `irrelevantCol_0000036`, `irrelevantCol_0000037`, `irrelevantCol_0000038`, `irrelevantCol_0000039`, `irrelevantCol_0000040`, `irrelevantCol_0000041`, `irrelevantCol_0000042`, `irrelevantCol_0000043`, `irrelevantCol_0000044`, `irrelevantCol_0000045`, `irrelevantCol_0000046`, `irrelevantCol_0000047`, `irrelevantCol_0000048`, `irrelevantCol_0000049`, `irrelevantCol_0000050`, `irrelevantCol_0000051`, `irrelevantCol_0000052`, `irrelevantCol_0000053`, `irrelevantCol_0000054`, `irrelevantCol_0000055`, `irrelevantCol_0000056`, `irrelevantCol_0000057`, `irrelevantCol_0000058`, `irrelevantCol_0000059`, `irrelevantCol_0000060`, `irrelevantCol_0000061`, `irrelevantCol_0000062`, `irrelevantCol_0000063`, `irrelevantCol_0000064`, `irrelevantCol_0000065`, `irrelevantCol_0000066`, `irrelevantCol_0000067`, `irrelevantCol_0000068`, `irrelevantCol_0000069`, `irrelevantCol_0000070`, `irrelevantCol_0000071`, `irrelevantCol_0000072`, `irrelevantCol_0000073`, `irrelevantCol_0000074`, `irrelevantCol_0000075`, `irrelevantCol_0000076`, `irrelevantCol_0000077`, `irrelevantCol_0000078`, `irrelevantCol_0000079`, `irrelevantCol_0000080`, `irrelevantCol_0000081`, `irrelevantCol_0000082`, `irrelevantCol_0000083`, `irrelevantCol_0000084`, `irrelevantCol_0000085`, `irrelevantCol_0000086`, `irrelevantCol_0000087`, `irrelevantCol_0000088`, `irrelevantCol_0000089`, `irrelevantCol_0000090`, `irrelevantCol_0000091`, `irrelevantCol_0000092`, `irrelevantCol_0000093`, `irrelevantCol_0000094`, `irrelevantCol_0000095`, `irrelevantCol_0000096`, `irrelevantCol_0000097`, `irrelevantCol_0000098`, `irrelevantCol_0000099`, `irrelevantCol_0000100`, `irrelevantCol_0000101`, `irrelevantCol_0000102`, `irrelevantCol_0000103`, `irrelevantCol_0000104`, `irrelevantCol_0000105`, `irrelevantCol_0000106`, `irrelevantCol_0000107`, `irrelevantCol_0000108`, `irrelevantCol_0000109`, `irrelevantCol_0000110`, `irrelevantCol_0000111`, `irrelevantCol_0000112`, `irrelevantCol_0000113`, `irrelevantCol_0000114`, `irrelevantCol_0000115`, `irrelevantCol_0000116`, `irrelevantCol_0000117`, `irrelevantCol_0000118`, `irrelevantCol_0000119`, `irrelevantCol_0000120`, `irrelevantCol_0000121`, `irrelevantCol_0000122`, `irrelevantCol_0000123`, `irrelevantCol_0000124`, `irrelevantCol_0000125`, `irrelevantCol_0000126`, `irrelevantCol_0000127`, `irrelevantCol_0000128`, `irrelevantCol_0000129`, `irrelevantCol_0000130`, `irrelevantCol_0000131`, `irrelevantCol_0000132`, `irrelevantCol_0000133`, `irrelevantCol_0000134`, `irrelevantCol_0000135`, `irrelevantCol_0000136`, `irrelevantCol_0000137`, `irrelevantCol_0000138`, `irrelevantCol_0000139`, `irrelevantCol_0000140`, `irrelevantCol_0000141`, `irrelevantCol_0000142`, `irrelevantCol_0000143`, `irrelevantCol_0000144`, `irrelevantCol_0000145`, `irrelevantCol_0000146`, `irrelevantCol_0000147`, `irrelevantCol_0000148`, `irrelevantCol_0000149`, `irrelevantCol_0000150`, `irrelevantCol_0000151`, `irrelevantCol_0000152`, `irrelevantCol_0000153`, `irrelevantCol_0000154`, `irrelevantCol_0000155`, `irrelevantCol_0000156`, `irrelevantCol_0000157`, `irrelevantCol_0000158`, `irrelevantCol_0000159`, `irrelevantCol_0000160`, `irrelevantCol_0000161`, `irrelevantCol_0000162`, `irrelevantCol_0000163`, `irrelevantCol_0000164`, `irrelevantCol_0000165`, `irrelevantCol_0000166`, `irrelevantCol_0000167`, `irrelevantCol_0000168`, `irrelevantCol_0000169`, `irrelevantCol_0000170`, `irrelevantCol_0000171`, `irrelevantCol_0000172`, `irrelevantCol_0000173`, `irrelevantCol_0000174`, `irrelevantCol_0000175`, `irrelevantCol_0000176`, `irrelevantCol_0000177`, `irrelevantCol_0000178`, `irrelevantCol_0000179`, `irrelevantCol_0000180`, `irrelevantCol_0000181`, `irrelevantCol_0000182`, `irrelevantCol_0000183`, `irrelevantCol_0000184`, `irrelevantCol_0000185`, `irrelevantCol_0000186`, `irrelevantCol_0000187`, `irrelevantCol_0000188`, `irrelevantCol_0000189`, `irrelevantCol_0000190`, `irrelevantCol_0000191`, `irrelevantCol_0000192`, `irrelevantCol_0000193`, `irrelevantCol_0000194`, `irrelevantCol_0000195`, `irrelevantCol_0000196`, `irrelevantCol_0000197`, `irrelevantCol_0000198`, `irrelevantCol_0000199`, `irrelevantCol_0000200`, `irrelevantCol_0000201`, `irrelevantCol_0000202`, `irrelevantCol_0000203`, `irrelevantCol_0000204`, `irrelevantCol_0000205`, `irrelevantCol_0000206`, `irrelevantCol_0000207`, `irrelevantCol_0000208`, `irrelevantCol_0000209`, `irrelevantCol_0000210`, `irrelevantCol_0000211`, `irrelevantCol_0000212`, `irrelevantCol_0000213`, `irrelevantCol_0000214`, `irrelevantCol_0000215`, `irrelevantCol_0000216`, `irrelevantCol_0000217`, `irrelevantCol_0000218`, `irrelevantCol_0000219`, `irrelevantCol_0000220`, `irrelevantCol_0000221`, `irrelevantCol_0000222`, `irrelevantCol_0000223`, `irrelevantCol_0000224`, `irrelevantCol_0000225`, `irrelevantCol_0000226`, `irrelevantCol_0000227`, `irrelevantCol_0000228`, `irrelevantCol_0000229`, `irrelevantCol_0000230`, `irrelevantCol_0000231`, `irrelevantCol_0000232`, `irrelevantCol_0000233`, `irrelevantCol_0000234`, `irrelevantCol_0000235`, `irrelevantCol_0000236`, `irrelevantCol_0000237`, `irrelevantCol_0000238`, `irrelevantCol_0000239`, `irrelevantCol_0000240`, `irrelevantCol_0000241`, `irrelevantCol_0000242`, `irrelevantCol_0000243`, `irrelevantCol_0000244`, `irrelevantCol_0000245`, `irrelevantCol_0000246`, `irrelevantCol_0000247`, `irrelevantCol_0000248`, `irrelevantCol_0000249`, `irrelevantCol_0000250`, `irrelevantCol_0000251`, `irrelevantCol_0000252`, `irrelevantCol_0000253`, `irrelevantCol_0000254`, `irrelevantCol_0000255`, `irrelevantCol_0000256`, `irrelevantCol_0000257`, `irrelevantCol_0000258`, `irrelevantCol_0000259`, `irrelevantCol_0000260`, `irrelevantCol_0000261`, `irrelevantCol_0000262`, `irrelevantCol_0000263`, `irrelevantCol_0000264`, `irrelevantCol_0000265`, `irrelevantCol_0000266`, `irrelevantCol_0000267`, `irrelevantCol_0000268`, `irrelevantCol_0000269`, `irrelevantCol_0000270`, `irrelevantCol_0000271`, `irrelevantCol_0000272`, `irrelevantCol_0000273`, `irrelevantCol_0000274`, `irrelevantCol_0000275`, `irrelevantCol_0000276`, `irrelevantCol_0000277`, `irrelevantCol_0000278`, `irrelevantCol_0000279`, `irrelevantCol_0000280`, `irrelevantCol_0000281`, `irrelevantCol_0000282`, `irrelevantCol_0000283`, `irrelevantCol_0000284`, `irrelevantCol_0000285`, `irrelevantCol_0000286`, `irrelevantCol_0000287`, `irrelevantCol_0000288`, `irrelevantCol_0000289`, `irrelevantCol_0000290`, `irrelevantCol_0000291`, `irrelevantCol_0000292`, `irrelevantCol_0000293`, `irrelevantCol_0000294`, `irrelevantCol_0000295`, `irrelevantCol_0000296`, `irrelevantCol_0000297`, `irrelevantCol_0000298`, `irrelevantCol_0000299`, `irrelevantCol_0000300`, `irrelevantCol_0000301`, `irrelevantCol_0000302`, `irrelevantCol_0000303`, `irrelevantCol_0000304`, `irrelevantCol_0000305`, `irrelevantCol_0000306`, `irrelevantCol_0000307`, `irrelevantCol_0000308`, `irrelevantCol_0000309`, `irrelevantCol_0000310`, `irrelevantCol_0000311`, `irrelevantCol_0000312`, `irrelevantCol_0000313`, `irrelevantCol_0000314`, `irrelevantCol_0000315`, `irrelevantCol_0000316`, `irrelevantCol_0000317`, `irrelevantCol_0000318`, `irrelevantCol_0000319`, `irrelevantCol_0000320`, `irrelevantCol_0000321`, `irrelevantCol_0000322`, `irrelevantCol_0000323`, `irrelevantCol_0000324`, `irrelevantCol_0000325`, `irrelevantCol_0000326`, `irrelevantCol_0000327`, `irrelevantCol_0000328`, `irrelevantCol_0000329`, `irrelevantCol_0000330`, `irrelevantCol_0000331`, `irrelevantCol_0000332`, `irrelevantCol_0000333`, `irrelevantCol_0000334`, `irrelevantCol_0000335`, `irrelevantCol_0000336`, `irrelevantCol_0000337`, `irrelevantCol_0000338`, `irrelevantCol_0000339`, `irrelevantCol_0000340`, `irrelevantCol_0000341`, `irrelevantCol_0000342`, `irrelevantCol_0000343`, `irrelevantCol_0000344`, `irrelevantCol_0000345`, `irrelevantCol_0000346`, `irrelevantCol_0000347`, `irrelevantCol_0000348`, `irrelevantCol_0000349`, `irrelevantCol_0000350`, `irrelevantCol_0000351`, `irrelevantCol_0000352`, `irrelevantCol_0000353`, `irrelevantCol_0000354`, `irrelevantCol_0000355`, `irrelevantCol_0000356`, `irrelevantCol_0000357`, `irrelevantCol_0000358`, `irrelevantCol_0000359`, `irrelevantCol_0000360`, `irrelevantCol_0000361`, `irrelevantCol_0000362`, `irrelevantCol_0000363`, `irrelevantCol_0000364`, `irrelevantCol_0000365`, `irrelevantCol_0000366`, `irrelevantCol_0000367`, `irrelevantCol_0000368`, `irrelevantCol_0000369`, `irrelevantCol_0000370`, `irrelevantCol_0000371`, `irrelevantCol_0000372`, `irrelevantCol_0000373`, `irrelevantCol_0000374`, `irrelevantCol_0000375`, `irrelevantCol_0000376`, `irrelevantCol_0000377`, `irrelevantCol_0000378`, `irrelevantCol_0000379`, `irrelevantCol_0000380`, `irrelevantCol_0000381`, `irrelevantCol_0000382`, `irrelevantCol_0000383`, `irrelevantCol_0000384`, `irrelevantCol_0000385`, `irrelevantCol_0000386`, `irrelevantCol_0000387`, `irrelevantCol_0000388`, `irrelevantCol_0000389`, `irrelevantCol_0000390`, `irrelevantCol_0000391`, `irrelevantCol_0000392`, `irrelevantCol_0000393`, `irrelevantCol_0000394`, `irrelevantCol_0000395`, `irrelevantCol_0000396`, `irrelevantCol_0000397`, `irrelevantCol_0000398`, `irrelevantCol_0000399`, `irrelevantCol_0000400`, `irrelevantCol_0000401`, `irrelevantCol_0000402`, `irrelevantCol_0000403`, `irrelevantCol_0000404`, `irrelevantCol_0000405`, `irrelevantCol_0000406`, `irrelevantCol_0000407`, `irrelevantCol_0000408`, `irrelevantCol_0000409`, `irrelevantCol_0000410`, `irrelevantCol_0000411`, `irrelevantCol_0000412`, `irrelevantCol_0000413`, `irrelevantCol_0000414`, `irrelevantCol_0000415`, `irrelevantCol_0000416`, `irrelevantCol_0000417`, `irrelevantCol_0000418`, `irrelevantCol_0000419`, `irrelevantCol_0000420`, `irrelevantCol_0000421`, `irrelevantCol_0000422`, `irrelevantCol_0000423`, `irrelevantCol_0000424`, `irrelevantCol_0000425`, `irrelevantCol_0000426`, `irrelevantCol_0000427`, `irrelevantCol_0000428`, `irrelevantCol_0000429`, `irrelevantCol_0000430`, `irrelevantCol_0000431`, `irrelevantCol_0000432`, `irrelevantCol_0000433`, `irrelevantCol_0000434`, `irrelevantCol_0000435`, `irrelevantCol_0000436`, `irrelevantCol_0000437`, `irrelevantCol_0000438`, `irrelevantCol_0000439`, `irrelevantCol_0000440`, `irrelevantCol_0000441`, `irrelevantCol_0000442`, `irrelevantCol_0000443`, `irrelevantCol_0000444`, `irrelevantCol_0000445`, `irrelevantCol_0000446`, `irrelevantCol_0000447`, `irrelevantCol_0000448`, `irrelevantCol_0000449`, `irrelevantCol_0000450`, `irrelevantCol_0000451`, `irrelevantCol_0000452`, `irrelevantCol_0000453`, `irrelevantCol_0000454`, `irrelevantCol_0000455`, `irrelevantCol_0000456`, `irrelevantCol_0000457`, `irrelevantCol_0000458`, `irrelevantCol_0000459`, `irrelevantCol_0000460`, `irrelevantCol_0000461`, `irrelevantCol_0000462`, `irrelevantCol_0000463`, `irrelevantCol_0000464`, `irrelevantCol_0000465`, `irrelevantCol_0000466`, `irrelevantCol_0000467`, `irrelevantCol_0000468`, `irrelevantCol_0000469`, `irrelevantCol_0000470`, `irrelevantCol_0000471`, `irrelevantCol_0000472`, `irrelevantCol_0000473`, `irrelevantCol_0000474`, `irrelevantCol_0000475`, `irrelevantCol_0000476`, `irrelevantCol_0000477`, `irrelevantCol_0000478`, `irrelevantCol_0000479`, `irrelevantCol_0000480`, `irrelevantCol_0000481`, `irrelevantCol_0000482`, `irrelevantCol_0000483`, `irrelevantCol_0000484`, `irrelevantCol_0000485`, `irrelevantCol_0000486`, `irrelevantCol_0000487`, `irrelevantCol_0000488`, `irrelevantCol_0000489`, `irrelevantCol_0000490`, `irrelevantCol_0000491`, `irrelevantCol_0000492`, `irrelevantCol_0000493`, `irrelevantCol_0000494`, `irrelevantCol_0000495`, `irrelevantCol_0000496`, `irrelevantCol_0000497`, `irrelevantCol_0000498`, `irrelevantCol_0000499`, `irrelevantCol_0000500`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, `irrelevantCol_0000001`, `irrelevantCol_0000002`, `irrelevantCol_0000003`, `irrelevantCol_0000004`, `irrelevantCol_0000005`, `irrelevantCol_0000006`, `irrelevantCol_0000007`, `irrelevantCol_0000008`, `irrelevantCol_0000009`, `irrelevantCol_0000010`, `irrelevantCol_0000011`, `irrelevantCol_0000012`, `irrelevantCol_0000013`, `irrelevantCol_0000014`, `irrelevantCol_0000015`, `irrelevantCol_0000016`, `irrelevantCol_0000017`, `irrelevantCol_0000018`, `irrelevantCol_0000019`, `irrelevantCol_0000020`, `irrelevantCol_0000021`, `irrelevantCol_0000022`, `irrelevantCol_0000023`, `irrelevantCol_0000024`, `irrelevantCol_0000025`, `irrelevantCol_0000026`, `irrelevantCol_0000027`, `irrelevantCol_0000028`, `irrelevantCol_0000029`, `irrelevantCol_0000030`, `irrelevantCol_0000031`, `irrelevantCol_0000032`, `irrelevantCol_0000033`, `irrelevantCol_0000034`, `irrelevantCol_0000035`, `irrelevantCol_0000036`, `irrelevantCol_0000037`, `irrelevantCol_0000038`, `irrelevantCol_0000039`, `irrelevantCol_0000040`, `irrelevantCol_0000041`, `irrelevantCol_0000042`, `irrelevantCol_0000043`, `irrelevantCol_0000044`, `irrelevantCol_0000045`, `irrelevantCol_0000046`, `irrelevantCol_0000047`, `irrelevantCol_0000048`, `irrelevantCol_0000049`, `irrelevantCol_0000050`, `irrelevantCol_0000051`, `irrelevantCol_0000052`, `irrelevantCol_0000053`, `irrelevantCol_0000054`, `irrelevantCol_0000055`, `irrelevantCol_0000056`, `irrelevantCol_0000057`, `irrelevantCol_0000058`, `irrelevantCol_0000059`, `irrelevantCol_0000060`, `irrelevantCol_0000061`, `irrelevantCol_0000062`, `irrelevantCol_0000063`, `irrelevantCol_0000064`, `irrelevantCol_0000065`, `irrelevantCol_0000066`, `irrelevantCol_0000067`, `irrelevantCol_0000068`, `irrelevantCol_0000069`, `irrelevantCol_0000070`, `irrelevantCol_0000071`, `irrelevantCol_0000072`, `irrelevantCol_0000073`, `irrelevantCol_0000074`, `irrelevantCol_0000075`, `irrelevantCol_0000076`, `irrelevantCol_0000077`, `irrelevantCol_0000078`, `irrelevantCol_0000079`, `irrelevantCol_0000080`, `irrelevantCol_0000081`, `irrelevantCol_0000082`, `irrelevantCol_0000083`, `irrelevantCol_0000084`, `irrelevantCol_0000085`, `irrelevantCol_0000086`, `irrelevantCol_0000087`, `irrelevantCol_0000088`, `irrelevantCol_0000089`, `irrelevantCol_0000090`, `irrelevantCol_0000091`, `irrelevantCol_0000092`, `irrelevantCol_0000093`, `irrelevantCol_0000094`, `irrelevantCol_0000095`, `irrelevantCol_0000096`, `irrelevantCol_0000097`, `irrelevantCol_0000098`, `irrelevantCol_0000099`, `irrelevantCol_0000100`, `irrelevantCol_0000101`, `irrelevantCol_0000102`, `irrelevantCol_0000103`, `irrelevantCol_0000104`, `irrelevantCol_0000105`, `irrelevantCol_0000106`, `irrelevantCol_0000107`, `irrelevantCol_0000108`, `irrelevantCol_0000109`, `irrelevantCol_0000110`, `irrelevantCol_0000111`, `irrelevantCol_0000112`, `irrelevantCol_0000113`, `irrelevantCol_0000114`, `irrelevantCol_0000115`, `irrelevantCol_0000116`, `irrelevantCol_0000117`, `irrelevantCol_0000118`, `irrelevantCol_0000119`, `irrelevantCol_0000120`, `irrelevantCol_0000121`, `irrelevantCol_0000122`, `irrelevantCol_0000123`, `irrelevantCol_0000124`, `irrelevantCol_0000125`, `irrelevantCol_0000126`, `irrelevantCol_0000127`, `irrelevantCol_0000128`, `irrelevantCol_0000129`, `irrelevantCol_0000130`, `irrelevantCol_0000131`, `irrelevantCol_0000132`, `irrelevantCol_0000133`, `irrelevantCol_0000134`, `irrelevantCol_0000135`, `irrelevantCol_0000136`, `irrelevantCol_0000137`, `irrelevantCol_0000138`, `irrelevantCol_0000139`, `irrelevantCol_0000140`, `irrelevantCol_0000141`, `irrelevantCol_0000142`, `irrelevantCol_0000143`, `irrelevantCol_0000144`, `irrelevantCol_0000145`, `irrelevantCol_0000146`, `irrelevantCol_0000147`, `irrelevantCol_0000148`, `irrelevantCol_0000149`, `irrelevantCol_0000150`, `irrelevantCol_0000151`, `irrelevantCol_0000152`, `irrelevantCol_0000153`, `irrelevantCol_0000154`, `irrelevantCol_0000155`, `irrelevantCol_0000156`, `irrelevantCol_0000157`, `irrelevantCol_0000158`, `irrelevantCol_0000159`, `irrelevantCol_0000160`, `irrelevantCol_0000161`, `irrelevantCol_0000162`, `irrelevantCol_0000163`, `irrelevantCol_0000164`, `irrelevantCol_0000165`, `irrelevantCol_0000166`, `irrelevantCol_0000167`, `irrelevantCol_0000168`, `irrelevantCol_0000169`, `irrelevantCol_0000170`, `irrelevantCol_0000171`, `irrelevantCol_0000172`, `irrelevantCol_0000173`, `irrelevantCol_0000174`, `irrelevantCol_0000175`, `irrelevantCol_0000176`, `irrelevantCol_0000177`, `irrelevantCol_0000178`, `irrelevantCol_0000179`, `irrelevantCol_0000180`, `irrelevantCol_0000181`, `irrelevantCol_0000182`, `irrelevantCol_0000183`, `irrelevantCol_0000184`, `irrelevantCol_0000185`, `irrelevantCol_0000186`, `irrelevantCol_0000187`, `irrelevantCol_0000188`, `irrelevantCol_0000189`, `irrelevantCol_0000190`, `irrelevantCol_0000191`, `irrelevantCol_0000192`, `irrelevantCol_0000193`, `irrelevantCol_0000194`, `irrelevantCol_0000195`, `irrelevantCol_0000196`, `irrelevantCol_0000197`, `irrelevantCol_0000198`, `irrelevantCol_0000199`, `irrelevantCol_0000200`, `irrelevantCol_0000201`, `irrelevantCol_0000202`, `irrelevantCol_0000203`, `irrelevantCol_0000204`, `irrelevantCol_0000205`, `irrelevantCol_0000206`, `irrelevantCol_0000207`, `irrelevantCol_0000208`, `irrelevantCol_0000209`, `irrelevantCol_0000210`, `irrelevantCol_0000211`, `irrelevantCol_0000212`, `irrelevantCol_0000213`, `irrelevantCol_0000214`, `irrelevantCol_0000215`, `irrelevantCol_0000216`, `irrelevantCol_0000217`, `irrelevantCol_0000218`, `irrelevantCol_0000219`, `irrelevantCol_0000220`, `irrelevantCol_0000221`, `irrelevantCol_0000222`, `irrelevantCol_0000223`, `irrelevantCol_0000224`, `irrelevantCol_0000225`, `irrelevantCol_0000226`, `irrelevantCol_0000227`, `irrelevantCol_0000228`, `irrelevantCol_0000229`, `irrelevantCol_0000230`, `irrelevantCol_0000231`, `irrelevantCol_0000232`, `irrelevantCol_0000233`, `irrelevantCol_0000234`, `irrelevantCol_0000235`, `irrelevantCol_0000236`, `irrelevantCol_0000237`, `irrelevantCol_0000238`, `irrelevantCol_0000239`, `irrelevantCol_0000240`, `irrelevantCol_0000241`, `irrelevantCol_0000242`, `irrelevantCol_0000243`, `irrelevantCol_0000244`, `irrelevantCol_0000245`, `irrelevantCol_0000246`, `irrelevantCol_0000247`, `irrelevantCol_0000248`, `irrelevantCol_0000249`, `irrelevantCol_0000250`, `irrelevantCol_0000251`, `irrelevantCol_0000252`, `irrelevantCol_0000253`, `irrelevantCol_0000254`, `irrelevantCol_0000255`, `irrelevantCol_0000256`, `irrelevantCol_0000257`, `irrelevantCol_0000258`, `irrelevantCol_0000259`, `irrelevantCol_0000260`, `irrelevantCol_0000261`, `irrelevantCol_0000262`, `irrelevantCol_0000263`, `irrelevantCol_0000264`, `irrelevantCol_0000265`, `irrelevantCol_0000266`, `irrelevantCol_0000267`, `irrelevantCol_0000268`, `irrelevantCol_0000269`, `irrelevantCol_0000270`, `irrelevantCol_0000271`, `irrelevantCol_0000272`, `irrelevantCol_0000273`, `irrelevantCol_0000274`, `irrelevantCol_0000275`, `irrelevantCol_0000276`, `irrelevantCol_0000277`, `irrelevantCol_0000278`, `irrelevantCol_0000279`, `irrelevantCol_0000280`, `irrelevantCol_0000281`, `irrelevantCol_0000282`, `irrelevantCol_0000283`, `irrelevantCol_0000284`, `irrelevantCol_0000285`, `irrelevantCol_0000286`, `irrelevantCol_0000287`, `irrelevantCol_0000288`, `irrelevantCol_0000289`, `irrelevantCol_0000290`, `irrelevantCol_0000291`, `irrelevantCol_0000292`, `irrelevantCol_0000293`, `irrelevantCol_0000294`, `irrelevantCol_0000295`, `irrelevantCol_0000296`, `irrelevantCol_0000297`, `irrelevantCol_0000298`, `irrelevantCol_0000299`, `irrelevantCol_0000300`, `irrelevantCol_0000301`, `irrelevantCol_0000302`, `irrelevantCol_0000303`, `irrelevantCol_0000304`, `irrelevantCol_0000305`, `irrelevantCol_0000306`, `irrelevantCol_0000307`, `irrelevantCol_0000308`, `irrelevantCol_0000309`, `irrelevantCol_0000310`, `irrelevantCol_0000311`, `irrelevantCol_0000312`, `irrelevantCol_0000313`, `irrelevantCol_0000314`, `irrelevantCol_0000315`, `irrelevantCol_0000316`, `irrelevantCol_0000317`, `irrelevantCol_0000318`, `irrelevantCol_0000319`, `irrelevantCol_0000320`, `irrelevantCol_0000321`, `irrelevantCol_0000322`, `irrelevantCol_0000323`, `irrelevantCol_0000324`, `irrelevantCol_0000325`, `irrelevantCol_0000326`, `irrelevantCol_0000327`, `irrelevantCol_0000328`, `irrelevantCol_0000329`, `irrelevantCol_0000330`, `irrelevantCol_0000331`, `irrelevantCol_0000332`, `irrelevantCol_0000333`, `irrelevantCol_0000334`, `irrelevantCol_0000335`, `irrelevantCol_0000336`, `irrelevantCol_0000337`, `irrelevantCol_0000338`, `irrelevantCol_0000339`, `irrelevantCol_0000340`, `irrelevantCol_0000341`, `irrelevantCol_0000342`, `irrelevantCol_0000343`, `irrelevantCol_0000344`, `irrelevantCol_0000345`, `irrelevantCol_0000346`, `irrelevantCol_0000347`, `irrelevantCol_0000348`, `irrelevantCol_0000349`, `irrelevantCol_0000350`, `irrelevantCol_0000351`, `irrelevantCol_0000352`, `irrelevantCol_0000353`, `irrelevantCol_0000354`, `irrelevantCol_0000355`, `irrelevantCol_0000356`, `irrelevantCol_0000357`, `irrelevantCol_0000358`, `irrelevantCol_0000359`, `irrelevantCol_0000360`, `irrelevantCol_0000361`, `irrelevantCol_0000362`, `irrelevantCol_0000363`, `irrelevantCol_0000364`, `irrelevantCol_0000365`, `irrelevantCol_0000366`, `irrelevantCol_0000367`, `irrelevantCol_0000368`, `irrelevantCol_0000369`, `irrelevantCol_0000370`, `irrelevantCol_0000371`, `irrelevantCol_0000372`, `irrelevantCol_0000373`, `irrelevantCol_0000374`, `irrelevantCol_0000375`, `irrelevantCol_0000376`, `irrelevantCol_0000377`, `irrelevantCol_0000378`, `irrelevantCol_0000379`, `irrelevantCol_0000380`, `irrelevantCol_0000381`, `irrelevantCol_0000382`, `irrelevantCol_0000383`, `irrelevantCol_0000384`, `irrelevantCol_0000385`, `irrelevantCol_0000386`, `irrelevantCol_0000387`, `irrelevantCol_0000388`, `irrelevantCol_0000389`, `irrelevantCol_0000390`, `irrelevantCol_0000391`, `irrelevantCol_0000392`, `irrelevantCol_0000393`, `irrelevantCol_0000394`, `irrelevantCol_0000395`, `irrelevantCol_0000396`, `irrelevantCol_0000397`, `irrelevantCol_0000398`, `irrelevantCol_0000399`, `irrelevantCol_0000400`, `irrelevantCol_0000401`, `irrelevantCol_0000402`, `irrelevantCol_0000403`, `irrelevantCol_0000404`, `irrelevantCol_0000405`, `irrelevantCol_0000406`, `irrelevantCol_0000407`, `irrelevantCol_0000408`, `irrelevantCol_0000409`, `irrelevantCol_0000410`, `irrelevantCol_0000411`, `irrelevantCol_0000412`, `irrelevantCol_0000413`, `irrelevantCol_0000414`, `irrelevantCol_0000415`, `irrelevantCol_0000416`, `irrelevantCol_0000417`, `irrelevantCol_0000418`, `irrelevantCol_0000419`, `irrelevantCol_0000420`, `irrelevantCol_0000421`, `irrelevantCol_0000422`, `irrelevantCol_0000423`, `irrelevantCol_0000424`, `irrelevantCol_0000425`, `irrelevantCol_0000426`, `irrelevantCol_0000427`, `irrelevantCol_0000428`, `irrelevantCol_0000429`, `irrelevantCol_0000430`, `irrelevantCol_0000431`, `irrelevantCol_0000432`, `irrelevantCol_0000433`, `irrelevantCol_0000434`, `irrelevantCol_0000435`, `irrelevantCol_0000436`, `irrelevantCol_0000437`, `irrelevantCol_0000438`, `irrelevantCol_0000439`, `irrelevantCol_0000440`, `irrelevantCol_0000441`, `irrelevantCol_0000442`, `irrelevantCol_0000443`, `irrelevantCol_0000444`, `irrelevantCol_0000445`, `irrelevantCol_0000446`, `irrelevantCol_0000447`, `irrelevantCol_0000448`, `irrelevantCol_0000449`, `irrelevantCol_0000450`, `irrelevantCol_0000451`, `irrelevantCol_0000452`, `irrelevantCol_0000453`, `irrelevantCol_0000454`, `irrelevantCol_0000455`, `irrelevantCol_0000456`, `irrelevantCol_0000457`, `irrelevantCol_0000458`, `irrelevantCol_0000459`, `irrelevantCol_0000460`, `irrelevantCol_0000461`, `irrelevantCol_0000462`, `irrelevantCol_0000463`, `irrelevantCol_0000464`, `irrelevantCol_0000465`, `irrelevantCol_0000466`, `irrelevantCol_0000467`, `irrelevantCol_0000468`, `irrelevantCol_0000469`, `irrelevantCol_0000470`, `irrelevantCol_0000471`, `irrelevantCol_0000472`, `irrelevantCol_0000473`, `irrelevantCol_0000474`, `irrelevantCol_0000475`, `irrelevantCol_0000476`, `irrelevantCol_0000477`, `irrelevantCol_0000478`, `irrelevantCol_0000479`, `irrelevantCol_0000480`, `irrelevantCol_0000481`, `irrelevantCol_0000482`, `irrelevantCol_0000483`, `irrelevantCol_0000484`, `irrelevantCol_0000485`, `irrelevantCol_0000486`, `irrelevantCol_0000487`, `irrelevantCol_0000488`, `irrelevantCol_0000489`, `irrelevantCol_0000490`, `irrelevantCol_0000491`, `irrelevantCol_0000492`, `irrelevantCol_0000493`, `irrelevantCol_0000494`, `irrelevantCol_0000495`, `irrelevantCol_0000496`, `irrelevantCol_0000497`, `irrelevantCol_0000498`, `irrelevantCol_0000499`, `irrelevantCol_0000500`, `probability`
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, `irrelevantCol_0000001`, `irrelevantCol_0000002`, `irrelevantCol_0000003`, `irrelevantCol_0000004`, `irrelevantCol_0000005`, `irrelevantCol_0000006`, `irrelevantCol_0000007`, `irrelevantCol_0000008`, `irrelevantCol_0000009`, `irrelevantCol_0000010`, `irrelevantCol_0000011`, `irrelevantCol_0000012`, `irrelevantCol_0000013`, `irrelevantCol_0000014`, `irrelevantCol_0000015`, `irrelevantCol_0000016`, `irrelevantCol_0000017`, `irrelevantCol_0000018`, `irrelevantCol_0000019`, `irrelevantCol_0000020`, `irrelevantCol_0000021`, `irrelevantCol_0000022`, `irrelevantCol_0000023`, `irrelevantCol_0000024`, `irrelevantCol_0000025`, `irrelevantCol_0000026`, `irrelevantCol_0000027`, `irrelevantCol_0000028`, `irrelevantCol_0000029`, `irrelevantCol_0000030`, `irrelevantCol_0000031`, `irrelevantCol_0000032`, `irrelevantCol_0000033`, `irrelevantCol_0000034`, `irrelevantCol_0000035`, `irrelevantCol_0000036`, `irrelevantCol_0000037`, `irrelevantCol_0000038`, `irrelevantCol_0000039`, `irrelevantCol_0000040`, `irrelevantCol_0000041`, `irrelevantCol_0000042`, `irrelevantCol_0000043`, `irrelevantCol_0000044`, `irrelevantCol_0000045`, `irrelevantCol_0000046`, `irrelevantCol_0000047`, `irrelevantCol_0000048`, `irrelevantCol_0000049`, `irrelevantCol_0000050`, `irrelevantCol_0000051`, `irrelevantCol_0000052`, `irrelevantCol_0000053`, `irrelevantCol_0000054`, `irrelevantCol_0000055`, `irrelevantCol_0000056`, `irrelevantCol_0000057`, `irrelevantCol_0000058`, `irrelevantCol_0000059`, `irrelevantCol_0000060`, `irrelevantCol_0000061`, `irrelevantCol_0000062`, `irrelevantCol_0000063`, `irrelevantCol_0000064`, `irrelevantCol_0000065`, `irrelevantCol_0000066`, `irrelevantCol_0000067`, `irrelevantCol_0000068`, `irrelevantCol_0000069`, `irrelevantCol_0000070`, `irrelevantCol_0000071`, `irrelevantCol_0000072`, `irrelevantCol_0000073`, `irrelevantCol_0000074`, `irrelevantCol_0000075`, `irrelevantCol_0000076`, `irrelevantCol_0000077`, `irrelevantCol_0000078`, `irrelevantCol_0000079`, `irrelevantCol_0000080`, `irrelevantCol_0000081`, `irrelevantCol_0000082`, `irrelevantCol_0000083`, `irrelevantCol_0000084`, `irrelevantCol_0000085`, `irrelevantCol_0000086`, `irrelevantCol_0000087`, `irrelevantCol_0000088`, `irrelevantCol_0000089`, `irrelevantCol_0000090`, `irrelevantCol_0000091`, `irrelevantCol_0000092`, `irrelevantCol_0000093`, `irrelevantCol_0000094`, `irrelevantCol_0000095`, `irrelevantCol_0000096`, `irrelevantCol_0000097`, `irrelevantCol_0000098`, `irrelevantCol_0000099`, `irrelevantCol_0000100`, `irrelevantCol_0000101`, `irrelevantCol_0000102`, `irrelevantCol_0000103`, `irrelevantCol_0000104`, `irrelevantCol_0000105`, `irrelevantCol_0000106`, `irrelevantCol_0000107`, `irrelevantCol_0000108`, `irrelevantCol_0000109`, `irrelevantCol_0000110`, `irrelevantCol_0000111`, `irrelevantCol_0000112`, `irrelevantCol_0000113`, `irrelevantCol_0000114`, `irrelevantCol_0000115`, `irrelevantCol_0000116`, `irrelevantCol_0000117`, `irrelevantCol_0000118`, `irrelevantCol_0000119`, `irrelevantCol_0000120`, `irrelevantCol_0000121`, `irrelevantCol_0000122`, `irrelevantCol_0000123`, `irrelevantCol_0000124`, `irrelevantCol_0000125`, `irrelevantCol_0000126`, `irrelevantCol_0000127`, `irrelevantCol_0000128`, `irrelevantCol_0000129`, `irrelevantCol_0000130`, `irrelevantCol_0000131`, `irrelevantCol_0000132`, `irrelevantCol_0000133`, `irrelevantCol_0000134`, `irrelevantCol_0000135`, `irrelevantCol_0000136`, `irrelevantCol_0000137`, `irrelevantCol_0000138`, `irrelevantCol_0000139`, `irrelevantCol_0000140`, `irrelevantCol_0000141`, `irrelevantCol_0000142`, `irrelevantCol_0000143`, `irrelevantCol_0000144`, `irrelevantCol_0000145`, `irrelevantCol_0000146`, `irrelevantCol_0000147`, `irrelevantCol_0000148`, `irrelevantCol_0000149`, `irrelevantCol_0000150`, `irrelevantCol_0000151`, `irrelevantCol_0000152`, `irrelevantCol_0000153`, `irrelevantCol_0000154`, `irrelevantCol_0000155`, `irrelevantCol_0000156`, `irrelevantCol_0000157`, `irrelevantCol_0000158`, `irrelevantCol_0000159`, `irrelevantCol_0000160`, `irrelevantCol_0000161`, `irrelevantCol_0000162`, `irrelevantCol_0000163`, `irrelevantCol_0000164`, `irrelevantCol_0000165`, `irrelevantCol_0000166`, `irrelevantCol_0000167`, `irrelevantCol_0000168`, `irrelevantCol_0000169`, `irrelevantCol_0000170`, `irrelevantCol_0000171`, `irrelevantCol_0000172`, `irrelevantCol_0000173`, `irrelevantCol_0000174`, `irrelevantCol_0000175`, `irrelevantCol_0000176`, `irrelevantCol_0000177`, `irrelevantCol_0000178`, `irrelevantCol_0000179`, `irrelevantCol_0000180`, `irrelevantCol_0000181`, `irrelevantCol_0000182`, `irrelevantCol_0000183`, `irrelevantCol_0000184`, `irrelevantCol_0000185`, `irrelevantCol_0000186`, `irrelevantCol_0000187`, `irrelevantCol_0000188`, `irrelevantCol_0000189`, `irrelevantCol_0000190`, `irrelevantCol_0000191`, `irrelevantCol_0000192`, `irrelevantCol_0000193`, `irrelevantCol_0000194`, `irrelevantCol_0000195`, `irrelevantCol_0000196`, `irrelevantCol_0000197`, `irrelevantCol_0000198`, `irrelevantCol_0000199`, `irrelevantCol_0000200`, `irrelevantCol_0000201`, `irrelevantCol_0000202`, `irrelevantCol_0000203`, `irrelevantCol_0000204`, `irrelevantCol_0000205`, `irrelevantCol_0000206`, `irrelevantCol_0000207`, `irrelevantCol_0000208`, `irrelevantCol_0000209`, `irrelevantCol_0000210`, `irrelevantCol_0000211`, `irrelevantCol_0000212`, `irrelevantCol_0000213`, `irrelevantCol_0000214`, `irrelevantCol_0000215`, `irrelevantCol_0000216`, `irrelevantCol_0000217`, `irrelevantCol_0000218`, `irrelevantCol_0000219`, `irrelevantCol_0000220`, `irrelevantCol_0000221`, `irrelevantCol_0000222`, `irrelevantCol_0000223`, `irrelevantCol_0000224`, `irrelevantCol_0000225`, `irrelevantCol_0000226`, `irrelevantCol_0000227`, `irrelevantCol_0000228`, `irrelevantCol_0000229`, `irrelevantCol_0000230`, `irrelevantCol_0000231`, `irrelevantCol_0000232`, `irrelevantCol_0000233`, `irrelevantCol_0000234`, `irrelevantCol_0000235`, `irrelevantCol_0000236`, `irrelevantCol_0000237`, `irrelevantCol_0000238`, `irrelevantCol_0000239`, `irrelevantCol_0000240`, `irrelevantCol_0000241`, `irrelevantCol_0000242`, `irrelevantCol_0000243`, `irrelevantCol_0000244`, `irrelevantCol_0000245`, `irrelevantCol_0000246`, `irrelevantCol_0000247`, `irrelevantCol_0000248`, `irrelevantCol_0000249`, `irrelevantCol_0000250`, `irrelevantCol_0000251`, `irrelevantCol_0000252`, `irrelevantCol_0000253`, `irrelevantCol_0000254`, `irrelevantCol_0000255`, `irrelevantCol_0000256`, `irrelevantCol_0000257`, `irrelevantCol_0000258`, `irrelevantCol_0000259`, `irrelevantCol_0000260`, `irrelevantCol_0000261`, `irrelevantCol_0000262`, `irrelevantCol_0000263`, `irrelevantCol_0000264`, `irrelevantCol_0000265`, `irrelevantCol_0000266`, `irrelevantCol_0000267`, `irrelevantCol_0000268`, `irrelevantCol_0000269`, `irrelevantCol_0000270`, `irrelevantCol_0000271`, `irrelevantCol_0000272`, `irrelevantCol_0000273`, `irrelevantCol_0000274`, `irrelevantCol_0000275`, `irrelevantCol_0000276`, `irrelevantCol_0000277`, `irrelevantCol_0000278`, `irrelevantCol_0000279`, `irrelevantCol_0000280`, `irrelevantCol_0000281`, `irrelevantCol_0000282`, `irrelevantCol_0000283`, `irrelevantCol_0000284`, `irrelevantCol_0000285`, `irrelevantCol_0000286`, `irrelevantCol_0000287`, `irrelevantCol_0000288`, `irrelevantCol_0000289`, `irrelevantCol_0000290`, `irrelevantCol_0000291`, `irrelevantCol_0000292`, `irrelevantCol_0000293`, `irrelevantCol_0000294`, `irrelevantCol_0000295`, `irrelevantCol_0000296`, `irrelevantCol_0000297`, `irrelevantCol_0000298`, `irrelevantCol_0000299`, `irrelevantCol_0000300`, `irrelevantCol_0000301`, `irrelevantCol_0000302`, `irrelevantCol_0000303`, `irrelevantCol_0000304`, `irrelevantCol_0000305`, `irrelevantCol_0000306`, `irrelevantCol_0000307`, `irrelevantCol_0000308`, `irrelevantCol_0000309`, `irrelevantCol_0000310`, `irrelevantCol_0000311`, `irrelevantCol_0000312`, `irrelevantCol_0000313`, `irrelevantCol_0000314`, `irrelevantCol_0000315`, `irrelevantCol_0000316`, `irrelevantCol_0000317`, `irrelevantCol_0000318`, `irrelevantCol_0000319`, `irrelevantCol_0000320`, `irrelevantCol_0000321`, `irrelevantCol_0000322`, `irrelevantCol_0000323`, `irrelevantCol_0000324`, `irrelevantCol_0000325`, `irrelevantCol_0000326`, `irrelevantCol_0000327`, `irrelevantCol_0000328`, `irrelevantCol_0000329`, `irrelevantCol_0000330`, `irrelevantCol_0000331`, `irrelevantCol_0000332`, `irrelevantCol_0000333`, `irrelevantCol_0000334`, `irrelevantCol_0000335`, `irrelevantCol_0000336`, `irrelevantCol_0000337`, `irrelevantCol_0000338`, `irrelevantCol_0000339`, `irrelevantCol_0000340`, `irrelevantCol_0000341`, `irrelevantCol_0000342`, `irrelevantCol_0000343`, `irrelevantCol_0000344`, `irrelevantCol_0000345`, `irrelevantCol_0000346`, `irrelevantCol_0000347`, `irrelevantCol_0000348`, `irrelevantCol_0000349`, `irrelevantCol_0000350`, `irrelevantCol_0000351`, `irrelevantCol_0000352`, `irrelevantCol_0000353`, `irrelevantCol_0000354`, `irrelevantCol_0000355`, `irrelevantCol_0000356`, `irrelevantCol_0000357`, `irrelevantCol_0000358`, `irrelevantCol_0000359`, `irrelevantCol_0000360`, `irrelevantCol_0000361`, `irrelevantCol_0000362`, `irrelevantCol_0000363`, `irrelevantCol_0000364`, `irrelevantCol_0000365`, `irrelevantCol_0000366`, `irrelevantCol_0000367`, `irrelevantCol_0000368`, `irrelevantCol_0000369`, `irrelevantCol_0000370`, `irrelevantCol_0000371`, `irrelevantCol_0000372`, `irrelevantCol_0000373`, `irrelevantCol_0000374`, `irrelevantCol_0000375`, `irrelevantCol_0000376`, `irrelevantCol_0000377`, `irrelevantCol_0000378`, `irrelevantCol_0000379`, `irrelevantCol_0000380`, `irrelevantCol_0000381`, `irrelevantCol_0000382`, `irrelevantCol_0000383`, `irrelevantCol_0000384`, `irrelevantCol_0000385`, `irrelevantCol_0000386`, `irrelevantCol_0000387`, `irrelevantCol_0000388`, `irrelevantCol_0000389`, `irrelevantCol_0000390`, `irrelevantCol_0000391`, `irrelevantCol_0000392`, `irrelevantCol_0000393`, `irrelevantCol_0000394`, `irrelevantCol_0000395`, `irrelevantCol_0000396`, `irrelevantCol_0000397`, `irrelevantCol_0000398`, `irrelevantCol_0000399`, `irrelevantCol_0000400`, `irrelevantCol_0000401`, `irrelevantCol_0000402`, `irrelevantCol_0000403`, `irrelevantCol_0000404`, `irrelevantCol_0000405`, `irrelevantCol_0000406`, `irrelevantCol_0000407`, `irrelevantCol_0000408`, `irrelevantCol_0000409`, `irrelevantCol_0000410`, `irrelevantCol_0000411`, `irrelevantCol_0000412`, `irrelevantCol_0000413`, `irrelevantCol_0000414`, `irrelevantCol_0000415`, `irrelevantCol_0000416`, `irrelevantCol_0000417`, `irrelevantCol_0000418`, `irrelevantCol_0000419`, `irrelevantCol_0000420`, `irrelevantCol_0000421`, `irrelevantCol_0000422`, `irrelevantCol_0000423`, `irrelevantCol_0000424`, `irrelevantCol_0000425`, `irrelevantCol_0000426`, `irrelevantCol_0000427`, `irrelevantCol_0000428`, `irrelevantCol_0000429`, `irrelevantCol_0000430`, `irrelevantCol_0000431`, `irrelevantCol_0000432`, `irrelevantCol_0000433`, `irrelevantCol_0000434`, `irrelevantCol_0000435`, `irrelevantCol_0000436`, `irrelevantCol_0000437`, `irrelevantCol_0000438`, `irrelevantCol_0000439`, `irrelevantCol_0000440`, `irrelevantCol_0000441`, `irrelevantCol_0000442`, `irrelevantCol_0000443`, `irrelevantCol_0000444`, `irrelevantCol_0000445`, `irrelevantCol_0000446`, `irrelevantCol_0000447`, `irrelevantCol_0000448`, `irrelevantCol_0000449`, `irrelevantCol_0000450`, `irrelevantCol_0000451`, `irrelevantCol_0000452`, `irrelevantCol_0000453`, `irrelevantCol_0000454`, `irrelevantCol_0000455`, `irrelevantCol_0000456`, `irrelevantCol_0000457`, `irrelevantCol_0000458`, `irrelevantCol_0000459`, `irrelevantCol_0000460`, `irrelevantCol_0000461`, `irrelevantCol_0000462`, `irrelevantCol_0000463`, `irrelevantCol_0000464`, `irrelevantCol_0000465`, `irrelevantCol_0000466`, `irrelevantCol_0000467`, `irrelevantCol_0000468`, `irrelevantCol_0000469`, `irrelevantCol_0000470`, `irrelevantCol_0000471`, `irrelevantCol_0000472`, `irrelevantCol_0000473`, `irrelevantCol_0000474`, `irrelevantCol_0000475`, `irrelevantCol_0000476`, `irrelevantCol_0000477`, `irrelevantCol_0000478`, `irrelevantCol_0000479`, `irrelevantCol_0000480`, `irrelevantCol_0000481`, `irrelevantCol_0000482`, `irrelevantCol_0000483`, `irrelevantCol_0000484`, `irrelevantCol_0000485`, `irrelevantCol_0000486`, `irrelevantCol_0000487`, `irrelevantCol_0000488`, `irrelevantCol_0000489`, `irrelevantCol_0000490`, `irrelevantCol_0000491`, `irrelevantCol_0000492`, `irrelevantCol_0000493`, `irrelevantCol_0000494`, `irrelevantCol_0000495`, `irrelevantCol_0000496`, `irrelevantCol_0000497`, `irrelevantCol_0000498`, `irrelevantCol_0000499`, `irrelevantCol_0000500`, `probability`, row_number() OVER (PARTITION BY `subjectID` ORDER BY `probability`, `surveyCategory`) AS `zzz5`, COUNT(*) OVER (PARTITION BY `subjectID`) AS `zzz6`
## FROM (SELECT *
## FROM (SELECT `subjectID`, `surveyCategory`, `assessmentTotal`, `irrelevantCol_0000001`, `irrelevantCol_0000002`, `irrelevantCol_0000003`, `irrelevantCol_0000004`, `irrelevantCol_0000005`, `irrelevantCol_0000006`, `irrelevantCol_0000007`, `irrelevantCol_0000008`, `irrelevantCol_0000009`, `irrelevantCol_0000010`, `irrelevantCol_0000011`, `irrelevantCol_0000012`, `irrelevantCol_0000013`, `irrelevantCol_0000014`, `irrelevantCol_0000015`, `irrelevantCol_0000016`, `irrelevantCol_0000017`, `irrelevantCol_0000018`, `irrelevantCol_0000019`, `irrelevantCol_0000020`, `irrelevantCol_0000021`, `irrelevantCol_0000022`, `irrelevantCol_0000023`, `irrelevantCol_0000024`, `irrelevantCol_0000025`, `irrelevantCol_0000026`, `irrelevantCol_0000027`, `irrelevantCol_0000028`, `irrelevantCol_0000029`, `irrelevantCol_0000030`, `irrelevantCol_0000031`, `irrelevantCol_0000032`, `irrelevantCol_0000033`, `irrelevantCol_0000034`, `irrelevantCol_0000035`, `irrelevantCol_0000036`, `irrelevantCol_0000037`, `irrelevantCol_0000038`, `irrelevantCol_0000039`, `irrelevantCol_0000040`, `irrelevantCol_0000041`, `irrelevantCol_0000042`, `irrelevantCol_0000043`, `irrelevantCol_0000044`, `irrelevantCol_0000045`, `irrelevantCol_0000046`, `irrelevantCol_0000047`, `irrelevantCol_0000048`, `irrelevantCol_0000049`, `irrelevantCol_0000050`, `irrelevantCol_0000051`, `irrelevantCol_0000052`, `irrelevantCol_0000053`, `irrelevantCol_0000054`, `irrelevantCol_0000055`, `irrelevantCol_0000056`, `irrelevantCol_0000057`, `irrelevantCol_0000058`, `irrelevantCol_0000059`, `irrelevantCol_0000060`, `irrelevantCol_0000061`, `irrelevantCol_0000062`, `irrelevantCol_0000063`, `irrelevantCol_0000064`, `irrelevantCol_0000065`, `irrelevantCol_0000066`, `irrelevantCol_0000067`, `irrelevantCol_0000068`, `irrelevantCol_0000069`, `irrelevantCol_0000070`, `irrelevantCol_0000071`, `irrelevantCol_0000072`, `irrelevantCol_0000073`, `irrelevantCol_0000074`, `irrelevantCol_0000075`, `irrelevantCol_0000076`, `irrelevantCol_0000077`, `irrelevantCol_0000078`, `irrelevantCol_0000079`, `irrelevantCol_0000080`, `irrelevantCol_0000081`, `irrelevantCol_0000082`, `irrelevantCol_0000083`, `irrelevantCol_0000084`, `irrelevantCol_0000085`, `irrelevantCol_0000086`, `irrelevantCol_0000087`, `irrelevantCol_0000088`, `irrelevantCol_0000089`, `irrelevantCol_0000090`, `irrelevantCol_0000091`, `irrelevantCol_0000092`, `irrelevantCol_0000093`, `irrelevantCol_0000094`, `irrelevantCol_0000095`, `irrelevantCol_0000096`, `irrelevantCol_0000097`, `irrelevantCol_0000098`, `irrelevantCol_0000099`, `irrelevantCol_0000100`, `irrelevantCol_0000101`, `irrelevantCol_0000102`, `irrelevantCol_0000103`, `irrelevantCol_0000104`, `irrelevantCol_0000105`, `irrelevantCol_0000106`, `irrelevantCol_0000107`, `irrelevantCol_0000108`, `irrelevantCol_0000109`, `irrelevantCol_0000110`, `irrelevantCol_0000111`, `irrelevantCol_0000112`, `irrelevantCol_0000113`, `irrelevantCol_0000114`, `irrelevantCol_0000115`, `irrelevantCol_0000116`, `irrelevantCol_0000117`, `irrelevantCol_0000118`, `irrelevantCol_0000119`, `irrelevantCol_0000120`, `irrelevantCol_0000121`, `irrelevantCol_0000122`, `irrelevantCol_0000123`, `irrelevantCol_0000124`, `irrelevantCol_0000125`, `irrelevantCol_0000126`, `irrelevantCol_0000127`, `irrelevantCol_0000128`, `irrelevantCol_0000129`, `irrelevantCol_0000130`, `irrelevantCol_0000131`, `irrelevantCol_0000132`, `irrelevantCol_0000133`, `irrelevantCol_0000134`, `irrelevantCol_0000135`, `irrelevantCol_0000136`, `irrelevantCol_0000137`, `irrelevantCol_0000138`, `irrelevantCol_0000139`, `irrelevantCol_0000140`, `irrelevantCol_0000141`, `irrelevantCol_0000142`, `irrelevantCol_0000143`, `irrelevantCol_0000144`, `irrelevantCol_0000145`, `irrelevantCol_0000146`, `irrelevantCol_0000147`, `irrelevantCol_0000148`, `irrelevantCol_0000149`, `irrelevantCol_0000150`, `irrelevantCol_0000151`, `irrelevantCol_0000152`, `irrelevantCol_0000153`, `irrelevantCol_0000154`, `irrelevantCol_0000155`, `irrelevantCol_0000156`, `irrelevantCol_0000157`, `irrelevantCol_0000158`, `irrelevantCol_0000159`, `irrelevantCol_0000160`, `irrelevantCol_0000161`, `irrelevantCol_0000162`, `irrelevantCol_0000163`, `irrelevantCol_0000164`, `irrelevantCol_0000165`, `irrelevantCol_0000166`, `irrelevantCol_0000167`, `irrelevantCol_0000168`, `irrelevantCol_0000169`, `irrelevantCol_0000170`, `irrelevantCol_0000171`, `irrelevantCol_0000172`, `irrelevantCol_0000173`, `irrelevantCol_0000174`, `irrelevantCol_0000175`, `irrelevantCol_0000176`, `irrelevantCol_0000177`, `irrelevantCol_0000178`, `irrelevantCol_0000179`, `irrelevantCol_0000180`, `irrelevantCol_0000181`, `irrelevantCol_0000182`, `irrelevantCol_0000183`, `irrelevantCol_0000184`, `irrelevantCol_0000185`, `irrelevantCol_0000186`, `irrelevantCol_0000187`, `irrelevantCol_0000188`, `irrelevantCol_0000189`, `irrelevantCol_0000190`, `irrelevantCol_0000191`, `irrelevantCol_0000192`, `irrelevantCol_0000193`, `irrelevantCol_0000194`, `irrelevantCol_0000195`, `irrelevantCol_0000196`, `irrelevantCol_0000197`, `irrelevantCol_0000198`, `irrelevantCol_0000199`, `irrelevantCol_0000200`, `irrelevantCol_0000201`, `irrelevantCol_0000202`, `irrelevantCol_0000203`, `irrelevantCol_0000204`, `irrelevantCol_0000205`, `irrelevantCol_0000206`, `irrelevantCol_0000207`, `irrelevantCol_0000208`, `irrelevantCol_0000209`, `irrelevantCol_0000210`, `irrelevantCol_0000211`, `irrelevantCol_0000212`, `irrelevantCol_0000213`, `irrelevantCol_0000214`, `irrelevantCol_0000215`, `irrelevantCol_0000216`, `irrelevantCol_0000217`, `irrelevantCol_0000218`, `irrelevantCol_0000219`, `irrelevantCol_0000220`, `irrelevantCol_0000221`, `irrelevantCol_0000222`, `irrelevantCol_0000223`, `irrelevantCol_0000224`, `irrelevantCol_0000225`, `irrelevantCol_0000226`, `irrelevantCol_0000227`, `irrelevantCol_0000228`, `irrelevantCol_0000229`, `irrelevantCol_0000230`, `irrelevantCol_0000231`, `irrelevantCol_0000232`, `irrelevantCol_0000233`, `irrelevantCol_0000234`, `irrelevantCol_0000235`, `irrelevantCol_0000236`, `irrelevantCol_0000237`, `irrelevantCol_0000238`, `irrelevantCol_0000239`, `irrelevantCol_0000240`, `irrelevantCol_0000241`, `irrelevantCol_0000242`, `irrelevantCol_0000243`, `irrelevantCol_0000244`, `irrelevantCol_0000245`, `irrelevantCol_0000246`, `irrelevantCol_0000247`, `irrelevantCol_0000248`, `irrelevantCol_0000249`, `irrelevantCol_0000250`, `irrelevantCol_0000251`, `irrelevantCol_0000252`, `irrelevantCol_0000253`, `irrelevantCol_0000254`, `irrelevantCol_0000255`, `irrelevantCol_0000256`, `irrelevantCol_0000257`, `irrelevantCol_0000258`, `irrelevantCol_0000259`, `irrelevantCol_0000260`, `irrelevantCol_0000261`, `irrelevantCol_0000262`, `irrelevantCol_0000263`, `irrelevantCol_0000264`, `irrelevantCol_0000265`, `irrelevantCol_0000266`, `irrelevantCol_0000267`, `irrelevantCol_0000268`, `irrelevantCol_0000269`, `irrelevantCol_0000270`, `irrelevantCol_0000271`, `irrelevantCol_0000272`, `irrelevantCol_0000273`, `irrelevantCol_0000274`, `irrelevantCol_0000275`, `irrelevantCol_0000276`, `irrelevantCol_0000277`, `irrelevantCol_0000278`, `irrelevantCol_0000279`, `irrelevantCol_0000280`, `irrelevantCol_0000281`, `irrelevantCol_0000282`, `irrelevantCol_0000283`, `irrelevantCol_0000284`, `irrelevantCol_0000285`, `irrelevantCol_0000286`, `irrelevantCol_0000287`, `irrelevantCol_0000288`, `irrelevantCol_0000289`, `irrelevantCol_0000290`, `irrelevantCol_0000291`, `irrelevantCol_0000292`, `irrelevantCol_0000293`, `irrelevantCol_0000294`, `irrelevantCol_0000295`, `irrelevantCol_0000296`, `irrelevantCol_0000297`, `irrelevantCol_0000298`, `irrelevantCol_0000299`, `irrelevantCol_0000300`, `irrelevantCol_0000301`, `irrelevantCol_0000302`, `irrelevantCol_0000303`, `irrelevantCol_0000304`, `irrelevantCol_0000305`, `irrelevantCol_0000306`, `irrelevantCol_0000307`, `irrelevantCol_0000308`, `irrelevantCol_0000309`, `irrelevantCol_0000310`, `irrelevantCol_0000311`, `irrelevantCol_0000312`, `irrelevantCol_0000313`, `irrelevantCol_0000314`, `irrelevantCol_0000315`, `irrelevantCol_0000316`, `irrelevantCol_0000317`, `irrelevantCol_0000318`, `irrelevantCol_0000319`, `irrelevantCol_0000320`, `irrelevantCol_0000321`, `irrelevantCol_0000322`, `irrelevantCol_0000323`, `irrelevantCol_0000324`, `irrelevantCol_0000325`, `irrelevantCol_0000326`, `irrelevantCol_0000327`, `irrelevantCol_0000328`, `irrelevantCol_0000329`, `irrelevantCol_0000330`, `irrelevantCol_0000331`, `irrelevantCol_0000332`, `irrelevantCol_0000333`, `irrelevantCol_0000334`, `irrelevantCol_0000335`, `irrelevantCol_0000336`, `irrelevantCol_0000337`, `irrelevantCol_0000338`, `irrelevantCol_0000339`, `irrelevantCol_0000340`, `irrelevantCol_0000341`, `irrelevantCol_0000342`, `irrelevantCol_0000343`, `irrelevantCol_0000344`, `irrelevantCol_0000345`, `irrelevantCol_0000346`, `irrelevantCol_0000347`, `irrelevantCol_0000348`, `irrelevantCol_0000349`, `irrelevantCol_0000350`, `irrelevantCol_0000351`, `irrelevantCol_0000352`, `irrelevantCol_0000353`, `irrelevantCol_0000354`, `irrelevantCol_0000355`, `irrelevantCol_0000356`, `irrelevantCol_0000357`, `irrelevantCol_0000358`, `irrelevantCol_0000359`, `irrelevantCol_0000360`, `irrelevantCol_0000361`, `irrelevantCol_0000362`, `irrelevantCol_0000363`, `irrelevantCol_0000364`, `irrelevantCol_0000365`, `irrelevantCol_0000366`, `irrelevantCol_0000367`, `irrelevantCol_0000368`, `irrelevantCol_0000369`, `irrelevantCol_0000370`, `irrelevantCol_0000371`, `irrelevantCol_0000372`, `irrelevantCol_0000373`, `irrelevantCol_0000374`, `irrelevantCol_0000375`, `irrelevantCol_0000376`, `irrelevantCol_0000377`, `irrelevantCol_0000378`, `irrelevantCol_0000379`, `irrelevantCol_0000380`, `irrelevantCol_0000381`, `irrelevantCol_0000382`, `irrelevantCol_0000383`, `irrelevantCol_0000384`, `irrelevantCol_0000385`, `irrelevantCol_0000386`, `irrelevantCol_0000387`, `irrelevantCol_0000388`, `irrelevantCol_0000389`, `irrelevantCol_0000390`, `irrelevantCol_0000391`, `irrelevantCol_0000392`, `irrelevantCol_0000393`, `irrelevantCol_0000394`, `irrelevantCol_0000395`, `irrelevantCol_0000396`, `irrelevantCol_0000397`, `irrelevantCol_0000398`, `irrelevantCol_0000399`, `irrelevantCol_0000400`, `irrelevantCol_0000401`, `irrelevantCol_0000402`, `irrelevantCol_0000403`, `irrelevantCol_0000404`, `irrelevantCol_0000405`, `irrelevantCol_0000406`, `irrelevantCol_0000407`, `irrelevantCol_0000408`, `irrelevantCol_0000409`, `irrelevantCol_0000410`, `irrelevantCol_0000411`, `irrelevantCol_0000412`, `irrelevantCol_0000413`, `irrelevantCol_0000414`, `irrelevantCol_0000415`, `irrelevantCol_0000416`, `irrelevantCol_0000417`, `irrelevantCol_0000418`, `irrelevantCol_0000419`, `irrelevantCol_0000420`, `irrelevantCol_0000421`, `irrelevantCol_0000422`, `irrelevantCol_0000423`, `irrelevantCol_0000424`, `irrelevantCol_0000425`, `irrelevantCol_0000426`, `irrelevantCol_0000427`, `irrelevantCol_0000428`, `irrelevantCol_0000429`, `irrelevantCol_0000430`, `irrelevantCol_0000431`, `irrelevantCol_0000432`, `irrelevantCol_0000433`, `irrelevantCol_0000434`, `irrelevantCol_0000435`, `irrelevantCol_0000436`, `irrelevantCol_0000437`, `irrelevantCol_0000438`, `irrelevantCol_0000439`, `irrelevantCol_0000440`, `irrelevantCol_0000441`, `irrelevantCol_0000442`, `irrelevantCol_0000443`, `irrelevantCol_0000444`, `irrelevantCol_0000445`, `irrelevantCol_0000446`, `irrelevantCol_0000447`, `irrelevantCol_0000448`, `irrelevantCol_0000449`, `irrelevantCol_0000450`, `irrelevantCol_0000451`, `irrelevantCol_0000452`, `irrelevantCol_0000453`, `irrelevantCol_0000454`, `irrelevantCol_0000455`, `irrelevantCol_0000456`, `irrelevantCol_0000457`, `irrelevantCol_0000458`, `irrelevantCol_0000459`, `irrelevantCol_0000460`, `irrelevantCol_0000461`, `irrelevantCol_0000462`, `irrelevantCol_0000463`, `irrelevantCol_0000464`, `irrelevantCol_0000465`, `irrelevantCol_0000466`, `irrelevantCol_0000467`, `irrelevantCol_0000468`, `irrelevantCol_0000469`, `irrelevantCol_0000470`, `irrelevantCol_0000471`, `irrelevantCol_0000472`, `irrelevantCol_0000473`, `irrelevantCol_0000474`, `irrelevantCol_0000475`, `irrelevantCol_0000476`, `irrelevantCol_0000477`, `irrelevantCol_0000478`, `irrelevantCol_0000479`, `irrelevantCol_0000480`, `irrelevantCol_0000481`, `irrelevantCol_0000482`, `irrelevantCol_0000483`, `irrelevantCol_0000484`, `irrelevantCol_0000485`, `irrelevantCol_0000486`, `irrelevantCol_0000487`, `irrelevantCol_0000488`, `irrelevantCol_0000489`, `irrelevantCol_0000490`, `irrelevantCol_0000491`, `irrelevantCol_0000492`, `irrelevantCol_0000493`, `irrelevantCol_0000494`, `irrelevantCol_0000495`, `irrelevantCol_0000496`, `irrelevantCol_0000497`, `irrelevantCol_0000498`, `irrelevantCol_0000499`, `irrelevantCol_0000500`, EXP(`assessmentTotal` * 0.237) / sum(EXP(`assessmentTotal` * 0.237)) OVER (PARTITION BY `subjectID`) AS `probability`
## FROM `d`) `rkudjbvcut`
## ORDER BY `probability`, `surveyCategory`) `prycagbagn`) `yoxhooevup`
## WHERE (`zzz5` = `zzz6`)) `uojpgjgzyr`) `kryqlvypwq`
## ORDER BY `subjectID`

dplyr_run <- function(narrow, compute) {
  dR <- dT
  if(narrow) {
    dR <- dR %>%
      select(subjectID, surveyCategory, assessmentTotal)
  }
  dR <- dR %>%
    group_by(subjectID) %>%
    mutate(probability =
             exp(assessmentTotal * scale)/
             sum(exp(assessmentTotal * scale), na.rm = TRUE)) %>%
    arrange(probability, surveyCategory) %>%
    filter(row_number() == n()) %>%
    ungroup() 
  if(compute) {
    dR <- compute(dR)
  }
  dR %>%
    rename(diagnosis = surveyCategory) %>%
    select(subjectID, diagnosis, probability) %>%
    arrange(subjectID) %>% 
    head(n=1) %>%
    collect() %>%
    as.data.frame()
}


dplyr_run(narrow=FALSE, compute=FALSE)

##   subjectID           diagnosis probability
## 1         1 withdrawal behavior         0.5

dplyr_run(narrow=TRUE, compute=FALSE)

##   subjectID           diagnosis probability
## 1         1 withdrawal behavior         0.5

optree <- dR %.>%
  extend_nse(.,
             probability %:=%
               exp(assessmentTotal * scale)/
               sum(exp(assessmentTotal * scale)),
             count %:=% count(1),
             partitionby = 'subjectID') %.>%
  extend_nse(.,
             rank %:=% row_number(),
             partitionby = 'subjectID',
             orderby = c('probability', 'surveyCategory'))  %.>%
  rename_columns(., 'diagnosis' %:=% 'surveyCategory') %.>%
  select_rows_nse(., rank == count) %.>%
  select_columns(., c('subjectID', 
                      'diagnosis', 
                      'probability')) %.>%
  orderby(., 'subjectID')

cat(to_sql(optree, my_db))

## SELECT * FROM (
##  SELECT
##   `subjectID`,
##   `diagnosis`,
##   `probability`
##  FROM (
##   SELECT * FROM (
##    SELECT
##     `count` AS `count`,
##     `probability` AS `probability`,
##     `rank` AS `rank`,
##     `subjectID` AS `subjectID`,
##     `surveyCategory` AS `diagnosis`
##    FROM (
##     SELECT
##      `count`,
##      `probability`,
##      `subjectID`,
##      `surveyCategory`,
##      row_number ( ) OVER (  PARTITION BY `subjectID` ORDER BY `probability`, `surveyCategory` ) AS `rank`
##     FROM (
##      SELECT
##       `subjectID`,
##       `surveyCategory`,
##       `assessmentTotal`,
##       exp ( `assessmentTotal` * 0.237 ) / sum ( exp ( `assessmentTotal` * 0.237 ) ) OVER (  PARTITION BY `subjectID` ) AS `probability`,
##       count ( 1 ) OVER (  PARTITION BY `subjectID` ) AS `count`
##      FROM (
##       SELECT
##        `subjectID`,
##        `surveyCategory`,
##        `assessmentTotal`
##       FROM
##        `d`
##       ) tsql_46098159875078923333_0000000000
##      ) tsql_46098159875078923333_0000000001
##    ) tsql_46098159875078923333_0000000002
##   ) tsql_46098159875078923333_0000000003
##   WHERE `rank` = `count`
##  ) tsql_46098159875078923333_0000000004
## ) tsql_46098159875078923333_0000000005 ORDER BY `subjectID`

rquery_run <- function() {
  optree <- dR %.>%
    extend_nse(.,
               probability %:=%
                 exp(assessmentTotal * scale)/
                 sum(exp(assessmentTotal * scale)),
               count %:=% count(1),
               partitionby = 'subjectID') %.>%
    extend_nse(.,
               rank %:=% row_number(),
               partitionby = 'subjectID',
               orderby = c('probability', 'surveyCategory'))  %.>%
    rename_columns(., 'diagnosis' %:=% 'surveyCategory') %.>%
    select_rows_nse(., rank == count) %.>%
    select_columns(., c('subjectID', 
                        'diagnosis', 
                        'probability')) %.>%
    orderby(., 'subjectID', limit = 1)
  execute(my_db, optree)
}

rquery_run()

##   subjectID           diagnosis probability
## 1         1 withdrawal behavior         0.5

We can get timings for variations of the function:

library("microbenchmark")

timings <- microbenchmark(dplyr_run(narrow=FALSE, compute=FALSE), 
                          dplyr_run(narrow=TRUE, compute=FALSE),
                          dplyr_run(narrow=FALSE, compute=TRUE), 
                          dplyr_run(narrow=TRUE, compute=TRUE),
                          rquery_run())

dT <- NULL
dR <- NULL

And then present the results:

print(timings)

## Unit: milliseconds
##                                        expr       min        lq      mean
##  dplyr_run(narrow = FALSE, compute = FALSE) 1101.6446 1163.9656 2514.3345
##   dplyr_run(narrow = TRUE, compute = FALSE)  569.1484  617.9120 1332.6794
##   dplyr_run(narrow = FALSE, compute = TRUE) 1574.3874 1615.5607 4597.8581
##    dplyr_run(narrow = TRUE, compute = TRUE)  321.3660  345.0255  573.3324
##                                rquery_run()  324.8277  366.8723  810.8341
##     median        uq       max neval
##  1196.0648 2458.8179  9617.158   100
##   644.9764  770.9428  6059.717   100
##  1653.6202 3691.8374 76008.617   100
##   367.1139  399.1494  3268.368   100
##   388.0891  411.5830  4083.752   100

autoplot(timings)

## Coordinate system already present. Adding new coordinate system, which will replace the existing one.

tdf <- as.data.frame(timings)

# order the data
tdf <- tdf %>%
  group_by(., expr) %>%
  mutate(., mtime = median(time)) %>%
  ungroup(.)

tdf$expr <- reorder(tdf$expr, tdf$mtime)
WVPlots::ScatterBoxPlotH(tdf, "time", "expr",  
                         pt_alpha=0.2,
                         title="Execution times in NS")

The necsissity of the narrowing effect seems to be not present on Spark when we look only at the final result. Though the effect would certainly be there if one inspected an intermediate table.

Of course, narrowing to the exact columns used can be difficult: it can involve inspecting an arbitrarily long pipeline for column uses. That is part of why we are developing a new R query generator that automates that procedure: rquery.

WinVector/rquery documentation built on Aug. 24, 2023, 11:12 a.m.