如何避免在查询表时进行两次单独的传递?

时间:2021-11-12 18:53:39

The context of this problem are hospital visits. The logic is a bit convoluted but these are the requirements.

这个问题的背景是医院访问。逻辑有点复杂,但这些都是要求。

  • Table Visits contains the names and visitIDs for the patients.
  • 表格访问包含患者的姓名和访问ID。

  • Table FilteredVisits contains certain visits that meet some criteria
  • Table FilteredVisits包含符合某些条件的特定访问

The report is run under a certain date range - this is important. Let's assume 1/1/2013 to 1/31/2013

该报告在特定日期范围内运行 - 这很重要。我们假设2013年1月1日至2013年1月31日

  • If a visit exists in FilteredVisits during that date range, the report shows a 1 for that visit. Else, it returns 0.
  • 如果在该日期范围内FilteredVisits中存在访问,则报告会显示该访问的1。否则,它返回0。

  • However, if there exists an entry for that Patient (Name1) in FilteredVisits prior to the date range (before 1/1/2013 in our example), then ALL the visits for that patient need to be 1, regardless if the other visits don't exist in FilterVisits table.
  • 但是,如果在日期范围之前的FilteredVisits中存在该患者(Name1)的条目(在我们的示例中为2013年1月1日之前),那么该患者的所有访问必须为1,无论其他访问是否参与不存在于FilterVisits表中。

  • PatientID and VisitID are both unique values.
  • PatientID和VisitID都是唯一值。

  • the report only outputs patients and visits within the date range
  • 该报告仅在日期范围内输出患者和访问

See below for an example.

请参阅下面的示例。

Currently, the code is doing 2 separate updates (2 passes) to a temp table that is storing the results. One update to set the value to 0/1 for the date range selected and then another update where is searches everything prior to the begin date parameter. The FilteredVisits table can be large, is there a way to optimize this to be faster?

目前,代码正在对存储结果的临时表进行2次单独更新(2次传递)。一个更新将所选日期范围的值设置为0/1,然后另一个更新,其中搜索开始日期参数之前的所有内容。 FilteredVisits表可能很大,有没有办法优化这个更快?

Table1 All Patient Visits

表1所有患者就诊

PatientNameID VisitID
-------------------

PatientName1 P1Visit1
PatientName1 P1Visit2
PatientName1 P1Visit3
PatientName2 P2Visit1
PatientName3 P3Visit1
PatientName3 P3Visit2

Table2 Filtered Visits

表2过滤的访问次数

PatientNameID  VisitID  Date
-------------------------
PatientName1 P1Visit1    12/1/2012
PatientName1 P1Visit3    1/2/2013
PatientName3 P3Visit1    1/8/2013

The results:

Results
PatientName1 P1visit1   1
PatientName1 P1visit2   1 -- would be '0' but there was a visit by PatientName1 BEFORE the date range so set to 1
PatientName1 P1visit3   1
PatientName2 P2visit1   0
PatientName3 P3visit1   1
PatientName3 P3Visit2   0 -- this stays 0 because there is no entry of PatientName3 visit3 in table2 and no visit by PatientName3 prior to the selected date range

-- Edit: TempTable includes only the patients and visits for the selected date range

- 编辑:TempTable仅包括患者和所选日期范围的访问

-- First  pass
UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.visitID = t.visitID 

-- Second pass      
UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.PatientID = t.PatientID -- join on patientID to include all visits
        AND fv.visitDate < @BeginDate -- search only before date range

2 个解决方案

#1


3  

In one pass:

一次通过:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON
 (fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate) -- search only before date range
 OR (fv.visitID = t.visitID)

Although, I would not mind two passes in this case as it does not appear to be a performance issue and it works either way. Up to the developer.

虽然,在这种情况下,我不介意两次通过,因为它似乎不是一个性能问题,它可以工作。由开发人员决定。

Or... are you looking for this:

或者......你在找这个:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.visitID = t.visitID
WHERE
 fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate -- search only before date range

Upon review, I think you are looking for a LEFT JOIN between the Patients and Visits tables with summary information. SUM or COUNT or maybe a CASE WHEN VisitDate IS NULL THEN 0 ELSE 1 END with a GROUP BY PatientName.

经过审核,我认为您正在寻找患者和访问表之间的LEFT JOIN,其中包含摘要信息。 SUM或COUNT或可能是一个例子,当VisitDate为NULL时,则为1,结束时为GROUP BY PatientName。

#2


0  

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
LEFT OUTER JOIN FilteredVisits fv1 ON fv1.visitID = t.visitID
LEFT OUTER JOIN FilteredVisits fv2 ON fv2.PatientID = t.PatientID
WHERE fv1.visitID is not null
   OR fv2.visitDate < @BeginDate -- search only before date range

#1


3  

In one pass:

一次通过:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON
 (fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate) -- search only before date range
 OR (fv.visitID = t.visitID)

Although, I would not mind two passes in this case as it does not appear to be a performance issue and it works either way. Up to the developer.

虽然,在这种情况下,我不介意两次通过,因为它似乎不是一个性能问题,它可以工作。由开发人员决定。

Or... are you looking for this:

或者......你在找这个:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.visitID = t.visitID
WHERE
 fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate -- search only before date range

Upon review, I think you are looking for a LEFT JOIN between the Patients and Visits tables with summary information. SUM or COUNT or maybe a CASE WHEN VisitDate IS NULL THEN 0 ELSE 1 END with a GROUP BY PatientName.

经过审核,我认为您正在寻找患者和访问表之间的LEFT JOIN,其中包含摘要信息。 SUM或COUNT或可能是一个例子,当VisitDate为NULL时,则为1,结束时为GROUP BY PatientName。

#2


0  

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
LEFT OUTER JOIN FilteredVisits fv1 ON fv1.visitID = t.visitID
LEFT OUTER JOIN FilteredVisits fv2 ON fv2.PatientID = t.PatientID
WHERE fv1.visitID is not null
   OR fv2.visitDate < @BeginDate -- search only before date range