运行总计直到特定条件为真 [英] Running Total until specific condition is true
问题描述
我有一张表，代表庄家的牌和他们的等级.我现在正在尝试进行查询(尽可能快)以设置游戏状态.
I'm having a table representing the dealers cards and their rank. I'm now trying to make a query (as fast as possible) to set status on the game.
(As said before, only the dealer cards is shown)
W = Win
S = Stand
L = Loss
B = Blackjack (in two cards)
关于规则:庄家在 21 点获胜，如果是两张牌，则为 21 点.如果排名在 17 到 20 之间，则为 S = Stand.超过 21 是一种损失.
About the rules: The dealer wins at 21, if it's in two cards its blackjack. If the rank is between 17 and 20 it's S = stand. Over 21 is a loss.
排名:
1 (ACE) - 1 或 11 级.计为 11.
1 (ACE) - 1 or 11 rank. Counted as 11.
2-10 - 2-10 排名
2-10 - 2-10 rank
11-13(骑士 - 国王) - 10 级
11-13 (knight - king) - 10 rank
╔════╦══════╦════════╗
║ Id ║ Rank ║ Status ║
╠════╬══════╬════════╣
║ 1 ║ 1 ║ ║
║ 2 ║ 5 ║ ║
║ 3 ║ 8 ║ L ║ //24 = Loss
║ 4 ║ 3 ║ ║
║ 5 ║ 1 ║ ║
║ 6 ║ 7 ║ W ║ //21 = Win
║ 7 ║ 10 ║ ║
║ 8 ║ 1 ║ B ║ //21 = Blackjack
║ 9 ║ 10 ║ ║
╚════╩══════╩════════╝
我尝试使用计数器来检查它是否是二十一点，然后我使用的是RunningPoint".检查卡的总和.
I've tried to use a counter to check if it's blackjack and then I'm using a "RunningPoint" to check the sum of the cards.
我现在有一个糟糕的解决方案，当它有大量数据时，它显示出非常糟糕的性能.你会怎么做，我可以做些什么来优化我的查询?当使用更多数据时，我还需要使用选项 (maxrecursion 0)
I have now a solution bad it shows very bad performance when it's a lot of data. How would you do this and what can I do to optimize my query? When using more data I also need to use option (maxrecursion 0)
(当有 100 万行时，它甚至无法运行...)
(When having 1 million rows it's not even possible to run this...)
我的例子:http://sqlfiddle.com/#!6/3855e/1
推荐答案
没有使用普通 SQL(包括窗口聚合函数)的有效解决方案，至少目前还没有人找到 :-)
There's no efficient solution using plain SQL (including Windowed Aggregate Functons), at least nobody found one, yet :-)
您的递归查询表现不佳，因为它太复杂了，这是一个简化版本:
Your recursive query performs bad because it's way too complicated, this is a simplified version:
编辑:修正了计算(Fiddle)
WITH ctePoints AS
(
SELECT 1 AS id
,rank
,CASE
WHEN rank >= 10 THEN 10
WHEN rank = 1 THEN 11
ELSE rank
END AS Point
,1 AS Counter
FROM dbo.BlackJack
WHERE Id = 1
UNION ALL
SELECT t2.Id
,t2.rank
,CASE WHEN t1.Point < 17 THEN t1.Point ELSE 0 END
+ CASE
WHEN t2.rank >= 10 THEN 10
WHEN t2.rank = 1 THEN 11
ELSE t2.rank
END AS Point
,CASE WHEN t1.Point < 17 THEN t1.Counter + 1 ELSE 1 END AS Counter
FROM dbo.BlackJack AS t2
INNER JOIN ctePoints AS t1 ON t2.Id = t1.Id + 1
)
SELECT ctepoints.*
,CASE
WHEN Point < 17 THEN ''
WHEN Point < 20 THEN 'S'
WHEN Point > 21 THEN 'L'
WHEN Point = 21 AND Counter = 2 THEN 'B'
ELSE 'W'
END AS DealerStatus
FROM ctePoints
可能还是太慢了，因为它是逐行处理的.
It's probably still too slow, because it processes row by row.
我通常使用递归 SQL 来替换游标逻辑(因为在我的 DBMS 中它通常要快得多)但游标更新实际上可能更快(演示):
I usually use recursive SQL to replace cursor logic (because in my DBMS it's usually much faster) but a cursor update might actually be faster (Demo):
CREATE TABLE #BlackJack
(
id INT PRIMARY KEY CLUSTERED
,Rank INT
,DealerStatus CHAR(1)
);
insert into #BlackJack (Id, Rank)
values
(1, 1),(2, 5), (3, 8), (4, 3), (5, 1), (6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);
DECLARE @Counter INT = 0
,@Point INT = 0
,@id int
,@Rank int
,@DealerStatus char(1)
DECLARE c CURSOR
FOR
SELECT id, Rank
FROM #BlackJack
ORDER BY id FOR UPDATE OF DealerStatus
OPEN c
FETCH NEXT FROM c INTO @id, @Rank
WHILE @@FETCH_STATUS = 0
BEGIN
SET @counter = @counter + 1
SET @Rank = CASE
WHEN @Rank >= 10 THEN 10
WHEN @Rank = 1 THEN 11
ELSE @Rank
END
SET @Point = @Point + @Rank
SET @DealerStatus = CASE
WHEN @Point < 17 THEN ''
WHEN @Point < 20 THEN 'S'
WHEN @Point > 21 THEN 'L'
WHEN @Point = 21 AND @Counter = 2 THEN 'B'
ELSE 'W'
END
IF @Point >= 17
BEGIN
UPDATE #BlackJack
SET DealerStatus = @DealerStatus
WHERE CURRENT OF c;
SET @Point = 0
SET @Counter = 0
END
FETCH NEXT FROM c INTO @id, @Rank
END
CLOSE c
DEALLOCATE c
SELECT * FROM #BlackJack ORDER BY id
@lad2025 的古怪更新"仍然是获得预期结果的最快方法，但它使用的是未记录的功能，如果 Service Pack 破坏了它，则无法抱怨它:-)
Still @lad2025's "quirky update" is the fastest way to get the expected result, but it's using an undocumented feature and if a Service Pack breaks it there's no way to complain about it :-)
这篇关于运行总计直到特定条件为真的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
