Just a quick post today to play around with some analytic functions.
The first time I talked to Tom Kyte, he asked me this question: What if you had a table that recorded patient visits by date and you wanted to compute the time between visits for each patient?
I fumbled it at the time, but soon realized this is really easy to answer with a very little use of analytic functions.
Let’s make a table and populate it:
CREATE TABLE scott.patient_visits ( visit_id NUMBER generated always AS IDENTITY (START WITH 1), patient_name varchar2(30), visit_date DATE); INSERT INTO scott.patient_visits (patient_name, visit_date) (SELECT substr(object_name,1,10) AS patient_name, trunc(sysdate-MOD(object_id,365)) AS visit_date FROM all_objects WHERE object_name NOT LIKE '\_%' AND object_name NOT LIKE '/%'); /
And a quick look at the data:
SELECT patient_name, visit_date FROM patient_visits WHERE rownum < 20;
Now that we have a table, we can use LAG to answer the question, and analytic COUNT to answer a few more besides:
SELECT * FROM ( SELECT patient_name, visit_date , to_char(visit_date,'WW') visit_wk , lag(visit_date) OVER (partition BY patient_name ORDER BY patient_name, visit_date) last_visit , visit_date - lag(visit_date) OVER (partition BY patient_name ORDER BY patient_name, visit_date) days_since , COUNT(visit_id) OVER (partition BY patient_name) num_visits , COUNT(visit_id) OVER (partition BY patient_name ORDER BY visit_date) nth_visit , COUNT(visit_id) OVER (partition BY patient_name ORDER BY visit_date range 7 preceding) nth_visit_last_7d , COUNT(visit_id) OVER (partition BY patient_name, to_number(to_char(visit_date,'WW'))) num_visits_this_wk FROM patient_visits ) WHERE num_visits > 10 ORDER BY patient_name, visit_date;
One thing I got out of this exercise was that I really like analytic COUNT. What are some of your favorite ways to use LAG or analytic COUNT?