如何從SQL表中提取行直到滿足多列的配額？

我已經能夠找到一些與此類似的問題示例，但大多數只涉及檢查一列。

SQL 選擇直到達到數量

選擇行直到條件滿足

我有一個表示設施的大表，其中包含每種可用資源型別的列以及每個設施可用的特定資源數量。我希望這個存盤程序能夠將整數值作為多個引數（代表這些列中的每一列）和一個緯度/經度。然后它應該遍歷按距離排序的表，并回傳所有行（設施），直到滿足所需的可用資源數量（由引數指定）。

資料源示例：

想要的結果：

@latQuery = 50.634

@LongQuery = 4.67

@res1Query = 10

@res2Query = 20

結果包括單獨滿足查詢的所有行。結果也按與請求的緯度/經度的距離排序

我能夠按距離對結果進行排序，并按照其他執行緒中的建議對總運行值求和，但是我在將運行值與引數中提供的配額進行比較的邏輯上遇到了一些問題。

首先，我有一些 CTE 來獲取最近的編輯，按距離排序，然后對運行總數求和

WITH cte1 AS (SELECT
  @origin.STDistance(geography::Point(Facility.Lat, Facility.Long, 4326)) AS distance,
  Facility.Resource1 as res1,
  Facility.Resource2 as res2
  -- ...etc
  FROM Facility
),
cte2 AS (SELECT 
  distance, 
  res1, 
  SUM(res1) OVER (ORDER BY distance) AS totRes1,
  res2, 
  SUM(res1) OVER (ORDER BY distance) AS totRes2
  -- ...etc, there's 15-20 columns here
  FROM cte1
)

接下來，根據該 CTE 的結果，我需要拉行直到滿足所有配額。這里有問題，它適用于一行，但我對所有ANDs 的邏輯并不完全正確。

SELECT * FROM cte2 WHERE (
  (totRes1 <= @res1Query OR (totRes1 > @res1Query AND totRes1- res1 <= @totRes1)) AND
  (totRes2 <= @res2Query OR (totRes2 > @res2Query AND totRes2- res2 <= @totRes2)) AND
  -- ... I also feel like this method of pulling the next row once it's over may be convoluted as well?
)

As-is right now, it's mostly returning nothing, and I'm guessing it's because it's too strict? Essentially, I want to be able to let the total values go past the required values until they are all past the required values, and then return that list.

Has anyone come across a better method of searching using separate quotas for multiple columns?

See my update in the answers/comments

uj5u.com熱心網友回復：

我認為你把這個問題復雜化了。這不需要任何連接，只需要一些運行總和計算和正確的OR邏輯。

解決這個問題的關鍵是你需要所有行，其中前一行的運行總和小于所有需求的要求。這意味著您包括未滿足要求的所有行，以及已滿足或超過要求的第一行。

為此，您可以從運行總和中減去當前行的值。

你可以利用ROWS的規范ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING。但是接下來你需要處理NULL第一行。

在任何情況下，即使是常規的運行總和也應始終使用ROWS UNBOUNDED PRECEDING，因為默認值為RANGE UNBOUNDED PRECEDING，它略有不同，可能導致不正確的結果，并且速度較慢。

您還可以將距離計算分解為 a CROSS APPLY (VALUES，從而避免需要大量 CTE 或派生表。您現在只需要一級推導。

DECLARE @origin geography = geography::Point(@latQuery, @LongQuery, 4326);

SELECT
  f.Id,
  f.Lat,
  f.Long,
  f.Resource1,
  f.Resource2
FROM (
    SELECT f.*,
        SumRes1 = SUM(f.Resource1) OVER (ORDER BY v1.Distance ROWS UNBOUNDED PRECEDING) - f.Resource1,
        SumRes2 = SUM(f.Resource2) OVER (ORDER BY v1.Distance ROWS UNBOUNDED PRECEDING) - f.Resource2
    FROM Facility f
    CROSS APPLY (VALUES(
        @origin.STDistance(geography::Point(f.Lat, f.Long, 4326))
    )) v1(Distance)
) f
WHERE (
      f.SumRes1 < @res1Query
   OR f.SumRes2 < @res2Query
);

資料庫<>小提琴

uj5u.com熱心網友回復：

能夠在這里自己解決問題。我遇到的主要問題是我將 25 個不同列的運行總數與 25 個存盤的 proc 引數（搜索所需的資源配額）進行比較。

(totRes1 <= @res1Query OR (totRes1 > @res1Query AND totRes1- res1 <= @totRes1)) AND --...

(totRes1 <= @res1Query OR (totRes1 > @res1Query AND totRes1- res1 <= @totRes1) OR @res1Query = 0) AND --...

換句話說，搜索通常只針對一兩列（資源型別）——而其他列為零。我的邏輯設定方式導致它跳過很多行，因為它立即將它們標記為已達到配額（值小于或等于配額）。就像@A Neon Tetra建議的那樣，已經非常接近了。

更新：

第一次嘗試并沒有完全解決我自己的問題。發布我現在為我作業的代碼的精簡版本。

DECLARE @Lat AS DECIMAL(12,6)
DECLARE @Lon AS DECIMAL(12,6)
DECLARE @res1Query AS INT
DECLARE @res2Query AS INT
-- repeat for Resource 3 through 25, etc...

DECLARE @origin geography = geography::Point(@Lat, @Lon, 4326);

-- CTE to be able to expose distance 
cte AS (SELECT TOP(99999)  -- --> this is hacky, it won't let me order by distance unless I'm selecting TOP(x) or some other fn?
    dbo.Facility.FacilityGUID,
    dbo.Facility.Lat,
    dbo.Facility.Lon,
    @origin.STDistance(geography::Point(dbo.Facility.Lat, dbo.Facility.Lon, 4326)) 
    AS distance,
    dbo.Facility.Resource1 AS res1,
    dbo.Facility.Resource2 AS res2,
    -- repeat for Resource 3 through 25, etc...
FROM dbo.Facility
ORDER BY distance), 

-- third CTE - has access to distance so we can keep track of a running total ordered by distance
---> have to separate into two since you can't reference the same alias (distance) again within the same SELECT
fullCTE AS (SELECT 
    FacilityID,
    Lat, 
    Long, 
    distance, 
    res1, 
  SUM(res1) OVER (ORDER BY distance)AS totRes1,
    res2, 
  SUM(res2) OVER (ORDER BY distance)AS totRes2,
  -- repeat for Resource 3 through 25, etc...
FROM cte)

SELECT  * -- Customize what you're pulling here for your output as needed
FROM dbo.Facility INNER JOIN fullCTE ON (fullCTE.FacilityID = dbo.Facility.FacilityID)
WHERE EXISTS
    (SELECT 
        FacilityID
    FROM fullCTE WHERE (
        FacilityID = dbo.Facility.FacilityID AND
        -- Keep pulling rows until all conditions are met, as opposed to pulling rows while they're under the quota
        NOT (
          ((totRes1 - res1 >= @res1Query AND @res1Query <> 0) OR (@res1Query = 0)) AND
          ((totRes2 - res2 >= @res2Query AND @res2Query <> 0) OR (@res2Query = 0)) AND
          -- repeat for Resource 3 through 25, etc...
        )
  )
)

標籤：sql sql-server tsql

上一篇：根據第二列中每個值的值過濾

下一篇：有沒有辦法在單獨的行中顯示總行數？