我今天和昨天的大部分时间都在试图决定是否在 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/

10-10 18:40
查看更多