Spell ID creation fails on large datasets

[fiona-grimm]

SQL query to generate spell IDs requires too much temporary storage (?) when used on large datasets and fails with a disk I/O error

[fiona-grimm]

Somewhat improved by changes in commit 39c98c9.

If spell creating fails as described above, the spell creation algorithm can be split and run manually as below, in order to avoid the I/O error. Not efficient, but it works.

dbExecute(con, paste0("CREATE TABLE APC2 AS     
                       WITH APC_DERIVED AS    
                       (    
                         SELECT ", str_c(cols_to_keep, collapse = ", "), ",
                         LAG(ADMIDATE_FILLED, 1) OVER ", spell_grouping, " AS PREV_ADMIDATE_FILLED,
                         LAG(EPISTART, 1) OVER ", spell_grouping, " AS PREV_EPISTART,
                         LAG(DISMETH, 1) OVER ", spell_grouping, " AS PREV_DISMETH,
                         LAG(EPIEND, 1) OVER ", spell_grouping, " AS PREV_EPIEND,
                         ROW_NUMBER() OVER ", spell_grouping, " AS ROWNUMBER
                         FROM APC
                       ),
                       APC_NEWSPELL AS
                       (
                         SELECT ", str_c(cols_to_keep, collapse = ", "), ", ROWNUMBER, CASE
                         WHEN EPI_VALID IS 0 THEN NULL
                         WHEN ADMIDATE_FILLED = PREV_ADMIDATE_FILLED THEN 0
                         WHEN EPISTART = PREV_EPISTART THEN 0
                         WHEN PREV_DISMETH IN (8,9) AND EPISTART = PREV_EPIEND THEN 0
                         ELSE 1
                         END NEW_SPELL 
                         FROM APC_DERIVED
                       )                      
                         SELECT *, CASE
                          WHEN NEW_SPELL = 1 THEN ROWNUMBER
                          WHEN NEW_SPELL = 0 THEN 0
                          WHEN NEW_SPELL IS NULL THEN NULL
                         END SPELL_ID_TEMP
                         FROM APC_NEWSPELL"))      
                
dbRemoveTable(con, "APC")

dbExecute(con, paste0("CREATE TABLE APC3 AS 
                       SELECT ", str_c(cols_to_keep, collapse = ", "), ", ROWNUMBER, NEW_SPELL, CASE
                       WHEN NEW_SPELL = 1 THEN SPELL_ID_TEMP
                       WHEN NEW_SPELL IS NULL THEN NULL
                       WHEN NEW_SPELL = 0 THEN MAX(SPELL_ID_TEMP) OVER (PARTITION BY ENCRYPTED_HESID, 
                                                                        PROCODE3 ORDER BY ROWNUMBER ASC ROWS BETWEEN
                                                                        UNBOUNDED PRECEDING AND CURRENT ROW)
                       END SPELL_ID    
                       FROM APC2"))    
 
dbRemoveTable(con, "APC2")    

dbExecute(con, "ALTER TABLE APC3 RENAME TO APC")