How can I convert gaps in dates to discrete intervals?

user_19386477 0
Viewed 26

Can't think of a better title for this, and I've had a lot of problems coming up with a solution in SQL.

Basically, I have a table of events with dates and an associated entity, like the following:

event entity date
er_visit bob 2020-01-01
triage bob 2020-01-01
admitted_to_icu bob 2020-01-01
inpatient_bed_rest bob 2020-01-02
inpatient_bed_rest bob 2020-01-03
physical_therapy bob 2020-01-03
hospital_discharge bob 2020-01-11
physical_therapy bob 2020-01-12
physical_therapy bob 2020-01-13
inpatient_followup bob 2020-02-01
inpatient_followup bob 2021-02-11
pregnancy_checkup alice 2020-01-01
admitted_maternity_ward alice 2020-02-01
inpatient_birth alice 2020-02-02
bed_rest alice 2020-02-02
bed_rest alice 2020-02-03
bed_rest alice 2020-02-04
hospital_discharge alice 2021-02-04

I need to turn this into a list of events per entity, with the relative dates encoded as discrete intervals - say 1 day, 1 week, 1 month, 1 year - and other intervals encoded as a combination of those intervals. 14 months, 15 days would be 1 year + 1 month + 1 month + 1 week + 1 week + 1 day.

event entity seq
er_visit bob 1
triage bob 2
admitted_to_icu bob 3
nextday bob 4
inpatient_bed_rest bob 5
nextday bob 6
inpatient_bed_rest bob 7
physical_therapy bob 8
nextweek bob 9
nextday bob 10
hospital_discharge bob 11
nextday bob 12
physical_therapy bob 13
nextday bob 14
physical_therapy bob 15
nextweek bob 16
nextweek bob 17
nextday bob 18
nextday bob 19
nextday bob 20
nextday bob 21
nextday bob 22
inpatient_followup bob 23
nextyear bob 24
nextweek bob 25
nextday bob 26
nextday bob 27
nextday bob 28
inpatient_followup bob 29
pregnancy_checkup alice 1
nextmonth alice 2
admitted_maternity_ward alice 3
nextday alice 4
inpatient_birth alice 5
bed_rest alice 6
nextday alice 7
bed_rest alice 8
nextday alice 9
bed_rest alice 10
hospital_discharge alice 11

Is there a reasonable solution for this in Athena (Presto) SQL?

presto amazon-athena sql
1 Answers

Done in Trino, but should be the same for these functions.

I've output the intervals in number of days, converting it from there to your strangely formatted desired output is possible, but way more complicated than makes sense.

WITH the_table AS (
    SELECT event, entity, date(date) date
    FROM (VALUES
          ('er_visit', 'bob', '2020-01-01'), ('triage', 'bob', '2020-01-01'),
          ('admitted_to_icu', 'bob', '2020-01-01'), ('inpatient_bed_rest', 'bob', '2020-01-02'),
          ('inpatient_bed_rest', 'bob', '2020-01-03'), ('physical_therapy', 'bob', '2020-01-03'),
          ('hospital_discharge', 'bob', '2020-01-11'), ('physical_therapy', 'bob', '2020-01-12'),
          ('physical_therapy', 'bob', '2020-01-13'), ('inpatient_followup', 'bob', '2020-02-01'),
          ('inpatient_followup', 'bob', '2021-02-11'), ('pregnancy_checkup', 'alice', '2020-01-01'),
          ('admitted_maternity_ward', 'alice', '2020-02-01'), ('inpatient_birth', 'alice', '2020-02-02'),
          ('bed_rest', 'alice', '2020-02-02'), ('bed_rest', 'alice', '2020-02-03'),
          ('bed_rest', 'alice', '2020-02-04'), ('hospital_discharge', 'alice', '2021-02-04'))
             AS data(event, entity, date)
)
SELECT event
     , entity
     , date_diff('day', lag(date) OVER (PARTITION BY entity ORDER BY date), date) days_since_last
FROM the_table
event entity days_since_last
pregnancy_checkup alice NULL
admitted_maternity_ward alice 31
inpatient_birth alice 1
bed_rest alice 0
bed_rest alice 1
bed_rest alice 1
hospital_discharge alice 366
er_visit bob NULL
triage bob 0
admitted_to_icu bob 0
inpatient_bed_rest bob 1
inpatient_bed_rest bob 1
physical_therapy bob 0
hospital_discharge bob 8
physical_therapy bob 1
physical_therapy bob 1
inpatient_followup bob 19
inpatient_followup bob 376
user_118659560
Related