SQL-запрос, чтобы найти клиентов, которые заказывают слишком часто?

Моя база данных на самом деле состоит не из клиентов и заказов, а из клиентов и рецептов на проверку их зрения (на случай, если кому-то интересно, почему я хочу, чтобы мои клиенты делали заказы реже!)

У меня есть база данных для сети оптиков, в таблице рецептов есть идентификационный номер отделения, идентификационный номер пациента и дата проверки зрения. Со временем у пациентов в базе данных появится более одного теста для проверки зрения. Как я могу получить список пациентов, у которых рецепт был введен в систему более одного раза в шесть месяцев. Другими словами, когда дата одного рецепта находится, например, в пределах трех месяцев от даты предыдущего рецепта для того же пациента.

Пример данных:

Branch  Patient DateOfTest
1      1          2007-08-12
1      1          2008-08-30
1      1          2008-08-31
1      2          2006-04-15
1      2          2007-04-12

Мне не нужно знать фактические даты в результирующем наборе, и это не должно быть точно три месяца, просто список пациентов, у которых рецепт слишком близок к предыдущему рецепту. В приведенном примере данных я хочу, чтобы запрос возвращался:

Branch   Patient
1       1

Такой запрос не будет выполняться очень регулярно, поэтому я не слишком беспокоюсь об эффективности. В нашей живой базе данных у меня есть четверть миллиона записей в таблице рецептов.


sql sql-server tsql
person Dan    schedule 19.02.2009    source источник

Ответы (4)


arrow_upward
7
arrow_downward

Что-то вроде этого

select p1.branch, p1.patient
from prescription p1, prescription p2
where p1.patient=p2.patient
and p1.dateoftest > p2.dateoftest
and datediff('day', p2.dateoftest, p1.dateoftest) < 90;

должен сделать ... вы можете добавить

and p1.dateoftest > getdate()

чтобы ограничить будущие тестовые предписания.

person tehvan    schedule 19.02.2009
comment
+1 Просто и эффективно. Однако не очень эффективно, поскольку индексы не могут быть очень полезными, это создаст большую матрицу перед фильтрацией. - person AnthonyWJones; 20.02.2009

arrow_upward
1
arrow_downward

Этот будет эффективно использовать индекс для (Branch, Patient, DateOfTest), который вы, конечно же, должны иметь:

SELECT Patient, DateOfTest, pDate
FROM (
  SELECT (
    SELECT TOP 1 DateOfTest AS last
    FROM Patients pp
    WHERE pp.Branch = p.Branch
      AND pp.Patient = p.Patient
      AND pp.DateOfTest BETWEEN DATEADD(month, -3, p.DateOfTest) AND p.DateOfTest
    ORDER BY 
      DateOfTest DESC
    ) pDate
  FROM Patients p
) po
WHERE pDate IS NOT NULL
person Quassnoi    schedule 19.02.2009
comment
+1 Эффективность. Однако это немного сложно, не все так ясно, что он делает. - person AnthonyWJones; 20.02.2009
comment
Для каждого предписанного случая он выбирает предыдущий случай в течение 3 месяцев, если таковой имеется, и отфильтровывает тех, у кого были случаи. - person Quassnoi; 20.02.2009

arrow_upward
0
arrow_downward

На пути:

select d.branch, d.patient
from   data d
where exists
( select null from data d1
  where  d1.branch = d.branch
  and    d1.patient = d.patient
  and    "difference (d1.dateoftest ,d.dateoftest) < 6 months"
);

Эту часть нужно изменить - я не знаком с операциями с датами SQL Server:

"difference (d1.dateoftest ,d.dateoftest) < 6 months"
person Tony Andrews    schedule 19.02.2009

arrow_upward
0
arrow_downward

Самостоятельное присоединение:

select a.branch, a.patient
   from prescriptions a
   join prescriptions b
   on     a.branch = b.branch 
      and a.patient = b.patient
      and a.dateoftest > b.dateoftest
      and a.dateoftest - b.dateoftest < 180
group by a.branch, a.patient

Это предполагает, что вам нужны пациенты, посещающие одно и то же отделение дважды. Если вы этого не сделаете, выньте часть ответвления.

person Carlos A. Ibarra    schedule 19.02.2009