更新存储过程中的聚合列

Question

考虑一个存储过程，该过程在特定日期call_date更新指定phone_id的聚合数据。

create proc phone_calls_aggregate @phone_id int, @call_date date
as
begin
    -- check if aggregate table already exists
    if not exists (select * from sys.sysobjects 
                   where name = 'tbl_calls_agg')    
        create table tbl_calls_agg ( -- create table to store aggregate data
            phone_id int,
            call_date date,
            total_charge money,
            total_duration int
         )
     --update aggregate data
     update tbl_calls_agg 
     -- update aggregate columns
     where phone_id = @phone_id and call_date = @call_date
end
go

列phone_id and call_date将作为参数传入。其他列应该包含从另一个表tbl_calls.计算的聚合数据。我可以在传统的UPDATE语句中更新这些列吗

最初插入聚合数据的查询是：

select
    phone_id as [Phone Number], 
    call_date as [Call Date]     
    sum(charge) as [Total charge], 
    count(duration) as [Total Calls Duration]
from 
    tbl_calls
where 
    phone_id = @phone_id and call_date = @day
group by 
    phone_id, call_date

更具体地，sum(charge)和count(duration)是期望的聚集体。

Answer 1

MERGE语句是为以下类型的工作而设计的：

CREATE PROCEDURE phone_calls_aggregate @phone_id int, @call_date date
AS
BEGIN
IF NOT EXISTS (SELECT * FROM sys.sysobjects 
               WHERE name = 'tbl_calls_agg')    
    CREATE TABLE tbl_calls_agg (
        phone_id INT,
        call_date DATE,
        total_charge MONEY,
        total_duration INT
     )
MERGE INTO tbl_calls_agg d
USING ( SELECT
            phone_id, 
            call_date     
            sum(charge) AS total_charge, 
            COUNT(duration) AS total_duration
        FROM tbl_calls
        WHERE phone_id = @phone_id AND call_date = @day
        GROUP BY phone_id, call_date
      ) s
ON d.phone_id = s.phone_id AND d.call_date = s.call_date
WHEN MATCHED AND (d.total_charge <> s.total_charge 
                  OR d.total_duration <> s.total_duration) THEN
    UPDATE SET d.total_charge = s.total_charge, d.total_duration = s.total_duration
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    VALUES ( phone_id, call_date, total_charge, total_duration )
WHEN NOT MATCHED BY SOURCE THEN   
    DELETE;
END
GO

Answer 2

类似的东西可能会起作用：

CREATE PROC phone_calls_aggregate @phone_id INT
    ,@call_date DATE
AS
BEGIN
    -- check if aggregate table already exists
    IF NOT EXISTS (
            SELECT *
            FROM sys.sysobjects
            WHERE NAME = 'tbl_calls_agg'
            )
        CREATE TABLE tbl_calls_agg (
            -- create table to store aggregate data
            phone_id INT
            ,call_date DATE
            ,total_charge MONEY
            ,total_duration INT
            )

    --update aggregate data
            ;
    WITH Agg_Data
    AS (
        SELECT phone_id AS [Phone Number]
            ,call_date AS [Call Date] sum(charge) AS [Total charge]
            ,count(duration) AS [Total Calls Duration]
        FROM tbl_calls
        WHERE phone_id = @phone_id
            AND call_date = @day
        GROUP BY phone_id
            ,call_date
        )

    UPDATE tbl
    SET total_charge = agg.[Total charge]
        ,total_duration = agg.[Total Calls Duration]
    FROM tbl_calls_agg tbl
    JOIN Agg_Data agg ON tbl.phone_id = agg.[Phone Number]
        AND tbl.call_date = agg.[Call Date]
END
GO

在应用更新之前，您需要记录值吗？在这种情况下，我会使用insert和一个标志来将已经存在的数字/日期标记为旧的。

最后，您可能需要考虑明确地处理日期，因为它们在某个时刻总是令人痛苦的。

Answer 3

如果查询已经插入aggregate值。具有单个值或聚合值的列之间没有区别。您可以正常更新列。你唯一应该考虑的是功能性的

update tbl_calls_agg 
     set total_charge=1000,
         total_duration=100
     where phone_id = @phone_id and call_date = @call_date