Cassandra中是否存在适用于该数据汇总缓存场景的设计模式？

Hey there! Let's work through how to set up this automated yearly player count summary for your tables. First, let's recap your setup to make sure we're aligned:

• Source Table: BIRTHDAYS_TABLE with year (INT) and PLAYER_ID (unique identifier for each player)
• Target Cache Table: NUM_OF_PLAYERS_TABLE where we'll store year and the total NUM_OF_PLAYERS for that year, refreshed annually

Step 1: Create the Cache Table (if it doesn't exist) #

First, let's set up the target table to hold our aggregated data. We'll use year as the primary key to avoid duplicate entries for the same year:

CREATE TABLE IF NOT EXISTS NUM_OF_PLAYERS_TABLE (
    year INT PRIMARY KEY,
    NUM_OF_PLAYERS INT NOT NULL
);

Step 2: SQL to Aggregate and Refresh Data #

We need a query that counts players per year from the source table, and either inserts new year records or updates existing ones (in case players are added to the source table after our last refresh).

For MySQL/MariaDB #

Use INSERT ... ON DUPLICATE KEY UPDATE to handle both new and existing years seamlessly:

INSERT INTO NUM_OF_PLAYERS_TABLE (year, NUM_OF_PLAYERS)
SELECT 
    year, 
    COUNT(DISTINCT PLAYER_ID) AS NUM_OF_PLAYERS -- Use COUNT(*) if PLAYER_ID has no duplicates in the source table
FROM BIRTHDAYS_TABLE
GROUP BY year
ON DUPLICATE KEY UPDATE NUM_OF_PLAYERS = VALUES(NUM_OF_PLAYERS);

For PostgreSQL #

Use INSERT ... ON CONFLICT DO UPDATE instead (PostgreSQL's equivalent of the above):

INSERT INTO NUM_OF_PLAYERS_TABLE (year, NUM_OF_PLAYERS)
SELECT 
    year, 
    COUNT(DISTINCT PLAYER_ID) AS NUM_OF_PLAYERS -- Use COUNT(*) if PLAYER_ID has no duplicates in the source table
FROM BIRTHDAYS_TABLE
GROUP BY year
ON CONFLICT (year) DO UPDATE 
    SET NUM_OF_PLAYERS = EXCLUDED.NUM_OF_PLAYERS;

Note: If your BIRTHDAYS_TABLE guarantees no duplicate PLAYER_ID entries for the same year, swap COUNT(DISTINCT PLAYER_ID) with COUNT(*) for better query performance.

Step 3: Set Up Yearly Scheduled Execution #

To automate this refresh every year, use your database's built-in scheduling tools:

MySQL/MariaDB #

First, enable the event scheduler (if it's not already active):

SET GLOBAL event_scheduler = ON;

Then create a yearly event to run our aggregation query:

DELIMITER //
CREATE EVENT IF NOT EXISTS refresh_player_count_yearly
ON SCHEDULE EVERY 1 YEAR
STARTS '2024-01-01 00:00:00' -- Adjust this to your preferred start date (e.g., end of December)
DO
BEGIN
    INSERT INTO NUM_OF_PLAYERS_TABLE (year, NUM_OF_PLAYERS)
    SELECT year, COUNT(DISTINCT PLAYER_ID) AS NUM_OF_PLAYERS
    FROM BIRTHDAYS_TABLE
    GROUP BY year
    ON DUPLICATE KEY UPDATE NUM_OF_PLAYERS = VALUES(NUM_OF_PLAYERS);
END //
DELIMITER ;

PostgreSQL (with pg_cron extension) #

First, install and enable the pg_cron extension (you'll need superuser access for this):

CREATE EXTENSION IF NOT EXISTS pg_cron;

Then schedule the yearly task (this example runs on January 1st at midnight):

SELECT cron.schedule(
    'refresh-player-count-yearly', -- A descriptive name for your task
    '0 0 1 1 *', -- Cron schedule: 0 mins, 0 hours, 1st day, 1st month, every year
    $$
        INSERT INTO NUM_OF_PLAYERS_TABLE (year, NUM_OF_PLAYERS)
        SELECT year, COUNT(DISTINCT PLAYER_ID) AS NUM_OF_PLAYERS
        FROM BIRTHDAYS_TABLE
        GROUP BY year
        ON CONFLICT (year) DO UPDATE 
            SET NUM_OF_PLAYERS = EXCLUDED.NUM_OF_PLAYERS;
    $$
);

Quick Tips #

• Adjust the cron schedule or event start time to match when you want the refresh to run (e.g., end of December to capture all players added that year)
• Ensure the database user running the scheduled task has read access to BIRTHDAYS_TABLE and write access to NUM_OF_PLAYERS_TABLE
• If you only need to refresh the most recent year instead of all years, add a WHERE year = EXTRACT(YEAR FROM CURRENT_DATE) clause to the SELECT query

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