Hey there, I suspect the nulls you saw earlier were because your UPDATE query couldn't match existing records in server_traffic for some (SID, UCID) pairs from connect_log, or you weren't handling new entries that need to be inserted instead of updated. Let's fix that with proper upsert logic tailored to MySQL and MS SQL, which will handle both existing and new records correctly while applying your weighted average formula.

First, a quick reminder: we need to calculate each new session's stay time as DisconnectTime - ConnectTime from connect_log, then use your formula Average = (Average * Size + NewValue) / (Size + 1) to update AvgTime, and increment NumConnects by 1 for each new entry.

MySQL Implementation #

First, ensure your server_traffic table has a unique composite key on (SID, UCID)—this is required for the upsert logic to work. If you don't have it already, add this constraint:

ALTER TABLE server_traffic ADD UNIQUE KEY idx_sid_ucid (SID, UCID);

Now use INSERT...ON DUPLICATE KEY UPDATE to handle both inserts and updates in one query:

INSERT INTO server_traffic (SID, UCID, AvgTime, NumConnects)
SELECT
    cl.SID,
    cl.UCID,
    (cl.DisconnectTime - cl.ConnectTime) AS AvgTime,
    1 AS NumConnects
FROM connect_log cl
ON DUPLICATE KEY UPDATE
    AvgTime = (server_traffic.AvgTime * server_traffic.NumConnects + (cl.DisconnectTime - cl.ConnectTime)) / (server_traffic.NumConnects + 1),
    NumConnects = server_traffic.NumConnects + 1;

Breakdown: #

• We first pull all new records from connect_log, calculate each session's stay time, and format them as potential new entries (with AvgTime set to the stay time and NumConnects set to 1).
• If a (SID, UCID) pair already exists in server_traffic, the ON DUPLICATE KEY UPDATE triggers:
  • We recalculate the weighted average using your exact formula, combining the existing average with the new stay time.
  • We increment the connection count by 1.
• This eliminates nulls because we're either inserting a valid new record or updating existing non-null values.

MS SQL Implementation #

For MS SQL, we use the MERGE statement to handle upserts. First, add a unique constraint to server_traffic if you haven't already:

ALTER TABLE server_traffic ADD CONSTRAINT UQ_ServerTraffic_SID_UCID UNIQUE (SID, UCID);

Then run the MERGE query:

MERGE INTO server_traffic st
USING (
    SELECT
        SID,
        UCID,
        (DisconnectTime - ConnectTime) AS StayTime
    FROM connect_log
) cl ON st.SID = cl.SID AND st.UCID = cl.UCID
WHEN MATCHED THEN
    UPDATE SET
        AvgTime = (st.AvgTime * st.NumConnects + cl.StayTime) / (st.NumConnects + 1),
        NumConnects = st.NumConnects + 1
WHEN NOT MATCHED THEN
    INSERT (SID, UCID, AvgTime, NumConnects)
    VALUES (cl.SID, cl.UCID, cl.StayTime, 1);

Breakdown: #

• The USING clause creates a temporary result set with all new stay times from connect_log.
• WHEN MATCHED updates existing records: it applies your weighted average formula and increments the connection count.
• WHEN NOT MATCHED inserts new records, setting AvgTime to the first stay time and NumConnects to 1.
• This ensures no null values are introduced, as we're always working with valid calculated stay times or existing non-null statistics.

Key Notes for Success: #

• Unique Constraint is Mandatory: Both solutions rely on a unique constraint on (SID, UCID) to correctly identify existing records. Without this, the upsert logic won't function as intended.
• Multiple New Entries per Pair: If connect_log has multiple entries for the same (SID, UCID), both queries will process all of them in one pass—each entry updates the average and count sequentially, which aligns perfectly with your running average formula.
• Performance Alignment: By pre-storing these statistics instead of calculating them on the fly, you'll keep your read queries fast, which directly supports your goal of maintaining snappy web responses for users.

内容的提问来源于stack exchange，提问作者Igneous01