OHDSI / OHDSI/CommonDataModel

Possibly simplify drug_era SQL

Open
#464 23 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
HTML
Stars
1.1k
Forks
508
Avg merge
1h 30m
Merged PRs (30d)
3

Description

I've been looking at the drug_era SQL and may have found a small simplification that always gives the same results. The query I'm looking at is the one that identifies the end dates of each ingredient exposure. I have compared my new query to the existing query on every case of two ingredient exposures that I can think of. I would like to be able to claim that if the query gives correct results for any set of two ingredient exposures then it will work for any number of ingredient exposures but I don't know if I can make that claim. Since the entire query is partitioned by ingredient_id and person_id I don't multiple ingredients or multiple persons will alter the correctness of the results. The algorithm runs on person-ingredient combinations and each person-ingredient combo is independent of all the others. Some assumptions I'm making about the input are that start date and end dates are populated and end date is at least 30 days after start date. I think these assumptions are guaranteed by the SQL statement that precedes the end date identification.

Here is my reprex:

library(dplyr)
library(RSQLite)

new_query <- function(ingredient_exposure){
  con <- dbConnect(SQLite(), ":memory:")
  ingredient_exposure <- as.data.frame(ingredient_exposure)
  dbWriteTable(con, DBI::SQL("temp.cteDrugTarget"), ingredient_exposure)
  
  sql <- "
  SELECT PERSON_ID
  ,INGREDIENT_CONCEPT_ID
  ,DATEADD(day, - 30, EVENT_DATE) AS END_DATE -- unpad the end date
  --INTO #cteEndDates
  FROM (
      SELECT E1.PERSON_ID
          ,E1.INGREDIENT_CONCEPT_ID
          ,E1.EVENT_DATE
          ,MAX(E2.START_ORDINAL) AS START_ORDINAL
          ,E1.OVERALL_ORD
      FROM (
          SELECT PERSON_ID
              ,INGREDIENT_CONCEPT_ID
              ,EVENT_DATE
              ,ROW_NUMBER() OVER (
                  PARTITION BY PERSON_ID
                  ,INGREDIENT_CONCEPT_ID ORDER BY EVENT_DATE
                  ) AS OVERALL_ORD -- this numbers the inner UNION so all rows are numbered ordered by the event date
          FROM (
              SELECT PERSON_ID
                  ,INGREDIENT_CONCEPT_ID
                  ,DRUG_EXPOSURE_START_DATE AS EVENT_DATE
              FROM #cteDrugTarget
  
              UNION ALL
  
              SELECT PERSON_ID
                  ,INGREDIENT_CONCEPT_ID
                  ,DATEADD(day, 30, DRUG_EXPOSURE_END_DATE)
              FROM #cteDrugTarget
              ) RAWDATA
          ) E1
      INNER JOIN (
          SELECT PERSON_ID
              ,INGREDIENT_CONCEPT_ID
              ,DRUG_EXPOSURE_START_DATE AS EVENT_DATE
              ,ROW_NUMBER() OVER (
                  PARTITION BY PERSON_ID
                  ,INGREDIENT_CONCEPT_ID ORDER BY DRUG_EXPOSURE_START_DATE
                  ) AS START_ORDINAL
          FROM #cteDrugTarget
          ) E2 ON E1.PERSON_ID = E2.PERSON_ID
          AND E1.INGREDIENT_CONCEPT_ID = E2.INGREDIENT_CONCEPT_ID
          AND E2.EVENT_DATE <= E1.EVENT_DATE
      GROUP BY E1.PERSON_ID
          ,E1.INGREDIENT_CONCEPT_ID
          ,E1.EVENT_DATE
          ,E1.OVERALL_ORD
      ) E
  WHERE 2 * E.START_ORDINAL - E.OVERALL_ORD = 0;
  "
  
  sql <- SqlRender::translate(sql, "sqlite")
  
  result <- dbGetQuery(con, sql) %>%
    mutate_at(vars(matches("DATE")), ~as.Date(., origin = "1970-01-01"))
  dbDisconnect(con)
  result
}





current_query <- function(ingredient_exposure){
  con <- dbConnect(SQLite(), ":memory:")
  ingredient_exposure <- as.data.frame(ingredient_exposure)
  dbWriteTable(con, DBI::SQL("temp.cteDrugTarget"), ingredient_exposure)
  
  sql <- "
  SELECT PERSON_ID
  ,INGREDIENT_CONCEPT_ID
  ,DATEADD(day, - 30, EVENT_DATE) AS END_DATE -- unpad the end date
  --INTO #cteEndDates
  FROM (
    SELECT E1.PERSON_ID
    ,E1.INGREDIENT_CONCEPT_ID
    ,E1.EVENT_DATE
    ,COALESCE(E1.START_ORDINAL, MAX(E2.START_ORDINAL)) AS START_ORDINAL
    ,E1.OVERALL_ORD
    FROM (
      SELECT PERSON_ID
      ,INGREDIENT_CONCEPT_ID
      ,EVENT_DATE
      ,EVENT_TYPE
      ,START_ORDINAL
      ,ROW_NUMBER() OVER (
        PARTITION BY PERSON_ID
        ,INGREDIENT_CONCEPT_ID ORDER BY EVENT_DATE
        ,EVENT_TYPE
      ) AS OVERALL_ORD -- this re-numbers the inner UNION so all rows are numbered ordered by the event date
      FROM (
        -- select the start dates, assigning a row number to each
        SELECT PERSON_ID
        ,INGREDIENT_CONCEPT_ID
        ,DRUG_EXPOSURE_START_DATE AS EVENT_DATE
        ,0 AS EVENT_TYPE
        ,ROW_NUMBER() OVER (
          PARTITION BY PERSON_ID
          ,INGREDIENT_CONCEPT_ID ORDER BY DRUG_EXPOSURE_START_DATE
        ) AS START_ORDINAL
        FROM #cteDrugTarget
        
        UNION ALL
        
        -- add the end dates with NULL as the row number, padding the end dates by 30 to allow a grace period for overlapping ranges.
        SELECT PERSON_ID
        ,INGREDIENT_CONCEPT_ID
        ,DATEADD(day, 30, DRUG_EXPOSURE_END_DATE)
        ,1 AS EVENT_TYPE
        ,NULL
        FROM #cteDrugTarget
      ) RAWDATA
    ) E1
    INNER JOIN (
      SELECT PERSON_ID
      ,INGREDIENT_CONCEPT_ID
      ,DRUG_EXPOSURE_START_DATE AS EVENT_DATE
      ,ROW_NUMBER() OVER (
        PARTITION BY PERSON_ID
        ,INGREDIENT_CONCEPT_ID ORDER BY DRUG_EXPOSURE_START_DATE
      ) AS START_ORDINAL
      FROM #cteDrugTarget
    ) E2 ON E1.PERSON_ID = E2.PERSON_ID
    AND E1.INGREDIENT_CONCEPT_ID = E2.INGREDIENT_CONCEPT_ID
    AND E2.EVENT_DATE <= E1.EVENT_DATE
    GROUP BY E1.PERSON_ID
    ,E1.INGREDIENT_CONCEPT_ID
    ,E1.EVENT_DATE
    ,E1.START_ORDINAL
    ,E1.OVERALL_ORD
  ) E
  WHERE 2 * E.START_ORDINAL - E.OVERALL_ORD = 0;"
  
  sql <- SqlRender::translate(sql, "sqlite")
  
  result <- dbGetQuery(con, sql) %>%
    mutate_at(vars(matches("DATE")), ~as.Date(., origin = "1970-01-01"))
  dbDisconnect(con)
  result
}


# Case 1: start 1 < start 2 < end 1 < end 2
case1 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-01-31",
  1,          1,                      "2020-01-02",             "2020-02-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case1)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-02-01
new_query(case1)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-02-01

# Case 2: start 1 < start 2 < end 2 < end 1 
case2 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-02-28",
  1,          1,                      "2020-01-02",             "2020-02-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case2)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-02-28
new_query(case2)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-02-28

# Case 3: start 1 < end 1 < start 2 < end 2
case3 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-01-31",
  1,          1,                      "2020-02-01",             "2020-03-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case3)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01
new_query(case3)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01

# Case 4: start 1 == start 2 < end 1 < end 2
case4 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-03-01",
  1,          1,                      "2020-01-01",             "2020-02-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case4)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01
new_query(case4)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01

# Case 5: start 1 == start 2 < end 1 == end 2
case5 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-03-01",
  1,          1,                      "2020-01-01",             "2020-03-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case5)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01
new_query(case5)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01

# Case 6: start 1 < start 2 < end 1 == end 2
case6 <- tribble(
  ~PERSON_ID, ~INGREDIENT_CONCEPT_ID, ~DRUG_EXPOSURE_START_DATE, ~DRUG_EXPOSURE_END_DATE,
  1,          1,                      "2020-01-01",             "2020-03-01",
  1,          1,                      "2020-02-01",             "2020-03-01") %>% 
  mutate_at(vars(matches("DATE")), as.Date)

current_query(case6)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01
new_query(case6)
#>   PERSON_ID INGREDIENT_CONCEPT_ID   END_DATE
#> 1         1                     1 2020-03-01

Created on 2021-11-27 by the reprex package (v2.0.1)

Is it possible for these two queries to give different results as long as the assumptions about the input are correct?

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by running the reprex and comparing new_query with current_query across the six cases shown. Analyze how the window functions, ordering, partitioning, and input assumptions affect equivalence; done means establishing whether any valid input can produce different results and documenting the conclusion.

Written by the indexing model from the issue text.

Assessment

Tech stack
r, sql
Domain
databases
Issue type
Refactor
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.