我今天和昨天的大部分时间都在试图决定是否在 SQL 中使用循环或游标,或者弄清楚如何使用基于集合的逻辑来解决问题。我对设置逻辑并不陌生,但这个问题似乎特别复杂。
问题
这个想法是,如果我有一个所有交易的列表(数以百万计)和发生日期,我可以开始将其中一些数据组合到每日总计表中,以便通过报告和分析更快速地查看系统。其伪代码如下:
foreach( row in transactions_table )
if( row in totals_table already exists )
update totals_table, add my totals to the totals row
else
insert into totals_table with my row as the base values
delete ( or archive ) row
如您所知,循环块的实现相对简单,游标/循环迭代也是如此。然而,执行时间非常缓慢和笨拙,我的问题是:是否有一种非迭代的方式来执行这样的任务,或者这是我必须“吸干它”并使用游标的罕见异常(exception)之一?
关于这个话题已经有一些讨论,其中一些看起来很相似,但由于 if/else 语句和另一个表上的操作而无法使用,例如:
How to merge rows of SQL data on column-based logic? 这个问题似乎不适用,因为它只是返回所有总和的 View ,实际上并没有对另一个表的添加或更新做出逻辑决策
SQL Looping 似乎对选择几个 case 语句有一些想法,这似乎是可能的,但是我需要根据另一个表的状态完成两个操作,所以这个解决方案似乎不适合。
SQL Call Stored Procedure for each Row without using a cursor 这个解决方案似乎与我需要做的最接近,因为它可以处理每行任意数量的操作,但该组之间似乎没有达成共识。
任何建议如何解决这个令人沮丧的问题?
笔记
我正在使用 SQL Server 2008
架构设置如下:
总计:(id int pk、totals_date 日期、store_id int fk、machine_id int fk、total_in、total_out)
交易:(transaction_id int pk、transaction_date datetime、store_id int fk、machine_id int fk、transaction_type(IN 或 OUT)、transaction_amount 十进制数)
总数应按商店、机器和日期计算,并且应将所有 IN 交易汇总到 total_in 中,将 OUT 交易汇总到 total_out 中。目标是获得一个伪数据立方体。
最佳答案
您可以在两个基于集合的语句中执行此操作:
BEGIN TRANSACTION;
DECLARE @keys TABLE(some_key INT);
UPDATE tot
SET totals += tx.amount
OUTPUT inserted.some_key -- key values updated
INTO @keys
FROM dbo.totals_table AS tot WITH (UPDLOCK, HOLDLOCK)
INNER JOIN
(
SELECT t.some_key, amount = SUM(amount)
FROM dbo.transactions_table AS t WITH (HOLDLOCK)
INNER JOIN dbo.totals_table AS tot
ON t.some_key = tot.some_key
GROUP BY t.some_key
) AS tx
ON tot.some_key = tx.some_key;
INSERT dbo.totals_table(some_key, amount)
OUTPUT inserted.some_key INTO @keys
SELECT some_key, SUM(amount)
FROM dbo.transactions_table AS tx
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.totals_table
WHERE some_key = tx.some_key
)
GROUP BY some_key;
DELETE dbo.transactions_table
WHERE some_key IN (SELECT some_key FROM @keys);
COMMIT TRANSACTION;
(为简洁起见,省略了错误处理、适用的隔离级别、回滚条件等。)
您首先执行更新,因此您不会插入新行然后更新它们,执行两次工作并且可能重复计算。您可以在这两种情况下使用输出到临时表,然后存档/删除 tx 表中的行。
我会告诫你不要对
MERGE
过于兴奋,除非他们已经 resolved some of these bugs 并且你已经阅读了足够的关于它的内容,以确保你不会对 how much "better" it is for concurrency and atomicity without additional hints 产生任何虚假的信心。您可以解决的竞争条件;你做不到的错误。另一种选择,来自尼古拉的评论
CREATE VIEW dbo.TotalsView
WITH SCHEMABINDING
AS
SELECT some_key_column(s), SUM(amount), COUNT_BIG(*)
FROM dbo.Transaction_Table
GROUP BY some_key_column(s);
GO
CREATE UNIQUE CLUSTERED INDEX some_key ON dbo.TotalsView(some_key_column(s));
GO
现在,如果您想编写获取总计的查询,您可以直接引用 View ,或者 - 根据查询和版本 - 即使您引用基表, View 也可能自动匹配。
注意:如果您使用的不是企业版,则可能必须使用
NOEXPAND
提示来利用 View 实现的预聚合值。关于sql - 如何将 SQL 中的循环转换为基于 Set 的逻辑,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15365029/