While running Achilles I get the next error messages. I got nearly 18TB free. How can this happen?. Thanks
Jose
|======================================================================| 100%
|======== | 12%2021-09-07 06:08:02 Thread 17 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83332.1876”: No space left on devicenAn error report has been created at //achillesError_1608.txt” when using argument(s): list(analysisId = “1608”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1608\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * total_paid) AS NUMERIC) as avg_value,\n CAST(STDDEV(total_paid) AS NUMERIC) as stdev_value,\n min(total_paid) as min_value,\n max(total_paid) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\nwhere total_paid is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1608\n AS\nSELECT\nsubject_id as stratum1_id, \n\ttotal_paid as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by total_paid) as rn\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\ngroup by subject_id, total_paid\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1608\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1608 s \njoin achilles_scratch.tmpach_statsView_1608 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1608\n AS\nSELECT\n1608 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1608 p \njoin achilles_scratch.tmpach_overallStats_1608 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1608;\ndrop table achilles_scratch.tmpach_overallStats_1608;\n\ntruncate table achilles_scratch.tmpach_statsView_1608;\ndrop table achilles_scratch.tmpach_statsView_1608;\n\ntruncate table achilles_scratch.tmpach_priorStats_1608;\ndrop table achilles_scratch.tmpach_priorStats_1608;\n”)
|============ | 18%2021-09-07 06:12:35 Thread 15 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83330.2101”: No space left on devicenAn error report has been created at //achillesError_1606.txt” when using argument(s): list(analysisId = “1606”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1606\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * paid_by_primary) AS NUMERIC) as avg_value,\n CAST(STDDEV(paid_by_primary) AS NUMERIC) as stdev_value,\n min(paid_by_primary) as min_value,\n max(paid_by_primary) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\nwhere paid_by_primary is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1606\n AS\nSELECT\nsubject_id as stratum1_id, \n\tpaid_by_primary as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by paid_by_primary) as rn\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\ngroup by subject_id, paid_by_primary\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1606\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1606 s \njoin achilles_scratch.tmpach_statsView_1606 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1606\n AS\nSELECT\n1606 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1606 p \njoin achilles_scratch.tmpach_overallStats_1606 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1606;\ndrop table achilles_scratch.tmpach_overallStats_1606;\n\ntruncate table achilles_scratch.tmpach_statsView_1606;\ndrop table achilles_scratch.tmpach_statsView_1606;\n\ntruncate table achilles_scratch.tmpach_priorStats_1606;\ndrop table achilles_scratch.tmpach_priorStats_1606;\n”)
|================ | 24%2021-09-07 06:13:34 Thread 14 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83328.2181”: No space left on devicenAn error report has been created at //achillesError_1605.txt” when using argument(s): list(analysisId = “1605”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1605\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * paid_by_payer) AS NUMERIC) as avg_value,\n CAST(STDDEV(paid_by_payer) AS NUMERIC) as stdev_value,\n min(paid_by_payer) as min_value,\n max(paid_by_payer) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\nwhere paid_by_payer is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1605\n AS\nSELECT\nsubject_id as stratum1_id, \n\tpaid_by_payer as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by paid_by_payer) as rn\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\ngroup by subject_id, paid_by_payer\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1605\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1605 s \njoin achilles_scratch.tmpach_statsView_1605 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1605\n AS\nSELECT\n1605 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1605 p \njoin achilles_scratch.tmpach_overallStats_1605 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1605;\ndrop table achilles_scratch.tmpach_overallStats_1605;\n\ntruncate table achilles_scratch.tmpach_statsView_1605;\ndrop table achilles_scratch.tmpach_statsView_1605;\n\ntruncate table achilles_scratch.tmpach_priorStats_1605;\ndrop table achilles_scratch.tmpach_priorStats_1605;\n”)
|===================== | 29%2021-09-07 06:16:01 Thread 16 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83331.2255”: No space left on devicenAn error report has been created at //achillesError_1607.txt” when using argument(s): list(analysisId = “1607”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1607\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * paid_by_patient) AS NUMERIC) as avg_value,\n CAST(STDDEV(paid_by_patient) AS NUMERIC) as stdev_value,\n min(paid_by_patient) as min_value,\n max(paid_by_patient) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\nwhere paid_by_patient is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1607\n AS\nSELECT\nsubject_id as stratum1_id, \n\tpaid_by_patient as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by paid_by_patient) as rn\n\nFROM\nachilles_scratch.tmpach_Procedure_cost_raw\ngroup by subject_id, paid_by_patient\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1607\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1607 s \njoin achilles_scratch.tmpach_statsView_1607 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1607\n AS\nSELECT\n1607 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1607 p \njoin achilles_scratch.tmpach_overallStats_1607 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1607;\ndrop table achilles_scratch.tmpach_overallStats_1607;\n\ntruncate table achilles_scratch.tmpach_statsView_1607;\ndrop table achilles_scratch.tmpach_statsView_1607;\n\ntruncate table achilles_scratch.tmpach_priorStats_1607;\ndrop table achilles_scratch.tmpach_priorStats_1607;\n”)
|========================= | 35%2021-09-07 06:17:12 Thread 7 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83323.2339”: No space left on devicenAn error report has been created at //achillesError_1508.txt” when using argument(s): list(analysisId = “1508”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1508\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * total_paid) AS NUMERIC) as avg_value,\n CAST(STDDEV(total_paid) AS NUMERIC) as stdev_value,\n min(total_paid) as min_value,\n max(total_paid) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\nwhere total_paid is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1508\n AS\nSELECT\nsubject_id as stratum1_id, \n\ttotal_paid as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by total_paid) as rn\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\ngroup by subject_id, total_paid\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1508\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1508 s \njoin achilles_scratch.tmpach_statsView_1508 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1508\n AS\nSELECT\n1508 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1508 p \njoin achilles_scratch.tmpach_overallStats_1508 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1508;\ndrop table achilles_scratch.tmpach_overallStats_1508;\n\ntruncate table achilles_scratch.tmpach_statsView_1508;\ndrop table achilles_scratch.tmpach_statsView_1508;\n\ntruncate table achilles_scratch.tmpach_priorStats_1508;\ndrop table achilles_scratch.tmpach_priorStats_1508;\n”)
|============================= | 41%2021-09-07 06:17:18 Thread 8 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83321.2312”: No space left on devicenAn error report has been created at //achillesError_1509.txt” when using argument(s): list(analysisId = “1509”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1509\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * paid_ingredient_cost) AS NUMERIC) as avg_value,\n CAST(STDDEV(paid_ingredient_cost) AS NUMERIC) as stdev_value,\n min(paid_ingredient_cost) as min_value,\n max(paid_ingredient_cost) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\nwhere paid_ingredient_cost is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1509\n AS\nSELECT\nsubject_id as stratum1_id, \n\tpaid_ingredient_cost as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by paid_ingredient_cost) as rn\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\ngroup by subject_id, paid_ingredient_cost\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1509\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1509 s \njoin achilles_scratch.tmpach_statsView_1509 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1509\n AS\nSELECT\n1509 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1509 p \njoin achilles_scratch.tmpach_overallStats_1509 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1509;\ndrop table achilles_scratch.tmpach_overallStats_1509;\n\ntruncate table achilles_scratch.tmpach_statsView_1509;\ndrop table achilles_scratch.tmpach_statsView_1509;\n\ntruncate table achilles_scratch.tmpach_priorStats_1509;\ndrop table achilles_scratch.tmpach_priorStats_1509;\n”)
|================================= | 47%2021-09-07 06:22:08 Thread 4 returns error: “Error executing SQL:norg.postgresql.util.PSQLException: ERROR: could not write to file “base/pgsql_tmp/pgsql_tmp83316.2592”: No space left on devicenAn error report has been created at //achillesError_1505.txt” when using argument(s): list(analysisId = “1505”, sql = “\n-- overallStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_overallStats_1505\n AS\nSELECT\nsubject_id as stratum1_id, \n CAST(avg(1.0 * paid_by_payer) AS NUMERIC) as avg_value,\n CAST(STDDEV(paid_by_payer) AS NUMERIC) as stdev_value,\n min(paid_by_payer) as min_value,\n max(paid_by_payer) as max_value,\n COUNT() as total\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\nwhere paid_by_payer is not null\ngroup by subject_id\n;\n\n-- statsView\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_statsView_1505\n AS\nSELECT\nsubject_id as stratum1_id, \n\tpaid_by_payer as count_value, \n COUNT() as total, \n\trow_number() over (partition by subject_id order by paid_by_payer) as rn\n\nFROM\nachilles_scratch.tmpach_Drug_cost_raw\ngroup by subject_id, paid_by_payer\n;\n\n-- priorStats\n\n–HINT DISTRIBUTE_ON_KEY(stratum1_id)\nCREATE TABLE achilles_scratch.tmpach_priorStats_1505\n AS\nSELECT\ns.stratum1_id, \n s.count_value, \n s.total, \n sum(p.total) as accumulated\n\nFROM\nachilles_scratch.tmpach_statsView_1505 s \njoin achilles_scratch.tmpach_statsView_1505 p\n on s.stratum1_id = p.stratum1_id and p.rn <= s.rn\ngroup by s.stratum1_id, s.count_value, s.total, s.rn\n;\n\n–HINT DISTRIBUTE_ON_KEY(stratum_1)\nCREATE TABLE achilles_scratch.tmpach_dist_1505\n AS\nSELECT\n1505 as analysis_id,\n\tCAST(p.stratum1_id AS VARCHAR(255)) as stratum_1,\n\tcast(null as varchar(255)) as stratum_2,\n\tcast(null as varchar(255)) as stratum_3,\n\tcast(null as varchar(255)) as stratum_4,\n\tcast(null as varchar(255)) as stratum_5,\n\to.total as count_value,\n\to.min_value,\n\to.max_value,\n\to.avg_value,\n\to.stdev_value,\n\tMIN(case when p.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,\n\tMIN(case when p.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,\n\tMIN(case when p.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,\n\tMIN(case when p.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,\n\tMIN(case when p.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value\n\nFROM\nachilles_scratch.tmpach_priorStats_1505 p \njoin achilles_scratch.tmpach_overallStats_1505 o on p.stratum1_id = o.stratum1_id\ngroup by p.stratum1_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value\n;\n\ntruncate table achilles_scratch.tmpach_overallStats_1505;\ndrop table achilles_scratch.tmpach_overallStats_1505;\n\ntruncate table achilles_scratch.tmpach_statsView_1505;\ndrop table achilles_scratch.tmpach_statsView_1505;\n\ntruncate table achilles_scratch.tmpach_priorStats_1505;\ndrop table achilles_scratch.tmpach_priorStats_1505;\n”)