SQL查询以查找频繁下单的客户？

Question

我的数据库实际上并不是客户和订单，而是客户的验光处方（以防有人想知道为什么我希望我的客户不频繁下订单！）

我有一个眼镜连锁店的数据库，处方表中有分店ID号码、患者ID号码和他们进行眼部检查的日期。随着时间的推移，患者将在数据库中列出多次眼部检查。如何获取在六个月内在系统中输入过一次以上处方的患者列表。换句话说，其中一个处方的日期与同一患者之前的处方日期相差三个月或更短。

示例数据：

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

这种查询不会经常运行，因此效率并不是我过于关心的问题。在我们的实时数据库中，处方表中有25万条记录。

- Dan

Answer 1

类似这样的东西

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

限制未来的测试处方。

- tehvan

---

+1 简单而有效。但是效率不是很高，因为索引对于创建大矩阵并在筛选之前没有什么帮助。 - AnthonyWJones

Answer 2

这个会有效地使用在 (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

- Quassnoi

---

+1 高效。然而它有点复杂，不太清楚它在做什么。 - AnthonyWJones

对于每个处方案例，它选择前三个月内的先前案例（如果有的话），并过滤掉那些已经有这些案例的人。 - Quassnoi

Answer 3

进行中：

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"

- Tony Andrews

Answer 4

自连接：

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

这假设您想要两次访问同一分支机构的患者。如果不需要，请删除分支部分。

- Carlos A. Ibarra

Answer 5

SELECT Branch
      ,Patient
  FROM (SELECT Branch
              ,Patient
              ,DateOfTest
              ,DateOfOtherTest
          FROM Prescriptions P1
          JOIN Prescriptions P2
            ON P2.Branch = P1.Branch
           AND P2.Patient = P2.Patient
           AND P2.DateOfTest <> P1.DateOfTest
       ) AS SubQuery
  WHERE DATEDIFF(day, SubQuery.DateOfTest, SubQuery.DateOfOtherTest) < 90

- Bliek