LAG and analytic COUNT

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)
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 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.