通过PL/SQL填充日期维度表并判定日期类型

Got it, let's break this down into manageable steps to get your TIMES table filled out correctly. You need two main things: syncing unique sale dates from SALES to TIMES, then setting the dayType column with the priority rule (Holiday > Weekend > Weekday).

1. Sync Unique saleDate Values to TIMES First #

First, we want to make sure TIMES has all the distinct dates from SALES without duplicates. Use this INSERT statement to add only dates that aren't already in TIMES:

INSERT INTO TIMES (saleDay)
SELECT DISTINCT saleDate
FROM SALES
WHERE saleDate NOT IN (SELECT saleDay FROM TIMES);

This avoids reinserting dates you already have in your dimension table, which is key for keeping your star schema clean.

2. Calculate and Update dayType #

Next, we'll set the dayType based on your priority rules. Let's cover two approaches—one for small holiday lists, and a more scalable one for larger or frequently updated holiday sets.

Option 1: Hardcode Holidays (For Small Lists) #

If your holiday list is short and doesn't change often, you can list them directly in a CASE statement. Just note that date formatting might vary slightly by database (I'll use standard SQL functions here, adjust if needed):

UPDATE TIMES
SET dayType = 
    CASE
        -- Check holidays first (highest priority)
        WHEN TO_CHAR(saleDay, 'MM-DD') IN ('01-01', '01-15', '01-19') THEN 'Holiday'
        -- Then check for weekends (adjust the weekday codes based on your DB)
        -- Example: Oracle uses '1' for Sunday, '7' for Saturday; MySQL uses DAYOFWEEK() where 1=Sunday,7=Saturday
        WHEN TO_CHAR(saleDay, 'D') IN ('1', '7') THEN 'Weekend'
        -- Everything else is a weekday
        ELSE 'Weekday'
    END;

Option 2: Use a Holidays Table (Recommended for Scalability) #

If you have lots of holidays or need to update them regularly, creating a separate HOLIDAYS table is way easier to maintain. Here's how:

First, create the table and populate it with your holidays:

CREATE TABLE HOLIDAYS (
    holiday_date DATE PRIMARY KEY
);

-- Insert your holiday dates (adjust format to match your DB)
INSERT INTO HOLIDAYS (holiday_date) 
VALUES ('2024-01-01'), ('2024-01-15'), ('2024-01-19');

Then update TIMES using this table to check for holidays:

UPDATE TIMES t
SET dayType = 
    CASE
        WHEN EXISTS (SELECT 1 FROM HOLIDAYS h WHERE h.holiday_date = t.saleDay) THEN 'Holiday'
        WHEN TO_CHAR(t.saleDay, 'D') IN ('1', '7') THEN 'Weekend'
        ELSE 'Weekday'
    END;

This way, when holidays change, you just update the HOLIDAYS table instead of rewriting your UPDATE query.

Option 3: Do It All in One Step (With MERGE) #

If you want to combine inserting new dates and setting dayType in a single query, use MERGE (supported in Oracle, SQL Server, PostgreSQL, etc.):

MERGE INTO TIMES t
USING (SELECT DISTINCT saleDate FROM SALES) s
ON (t.saleDay = s.saleDate)
-- Insert new dates with their dayType already set
WHEN NOT MATCHED THEN
    INSERT (saleDay, dayType)
    VALUES (s.saleDate,
        CASE
            WHEN TO_CHAR(s.saleDate, 'MM-DD') IN ('01-01', '01-15', '01-19') THEN 'Holiday'
            WHEN TO_CHAR(s.saleDate, 'D') IN ('1', '7') THEN 'Weekend'
            ELSE 'Weekday'
        END)
-- Update existing dates in case dayType needs refreshing
WHEN MATCHED THEN
    UPDATE SET dayType = 
        CASE
            WHEN TO_CHAR(s.saleDate, 'MM-DD') IN ('01-01', '01-15', '01-19') THEN 'Holiday'
            WHEN TO_CHAR(s.saleDate, 'D') IN ('1', '7') THEN 'Weekend'
            ELSE 'Weekday'
        END;

Quick Notes to Adjust for Your Database #

• Weekday Calculations: Different databases use different codes for weekdays. For example, SQL Server's DATEPART(WEEKDAY, saleDay) might treat Monday as 1 depending on your server settings—double-check your DB's documentation.
• Floating Holidays: If you have holidays that change annually (like Thanksgiving), you'll need more logic to calculate those dates, but the holidays table approach still works—you just need to populate it with the correct yearly dates.

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