Power BI表格信息补全求助：跨月订单状态数据整合公式问题

Hey there, let's work through this problem step by step to create a reliable table that tracks every request's status for each day—no more missing data when months roll over!

First, Let's Clarify the Core Problem #

• Your existing PR_HIST_MOVIM_PEDID table drops data across month boundaries, so it can't show a full history of each request's daily status.
• The LOG table is your trusted source: it records the last status change for each request every day, so we'll build our new table using this accurate data.

Step-by-Step Implementation #

1. Create a Continuous Date Dimension Table #

The biggest culprit for cross-month data gaps is missing date entries. We'll first make a calendar table that covers all your business dates (past and future):

-- Create the date dimension table if it doesn't exist
CREATE TABLE IF NOT EXISTS DATE_DIM (
    REF_DATE DATE PRIMARY KEY
);

-- Populate it with continuous dates (adjust the start/end dates to match your needs)
WITH RECURSIVE dates AS (
    SELECT '2023-01-01'::DATE AS ref_date
    UNION ALL
    SELECT ref_date + INTERVAL '1 day'
    FROM dates
    WHERE ref_date < '2024-12-31'::DATE
)
INSERT INTO DATE_DIM (REF_DATE)
SELECT ref_date FROM dates
ON CONFLICT (REF_DATE) DO NOTHING;

2. Extract All Unique Request IDs #

We need to make sure we don't miss any request, so let's pull every unique ID from the LOG table:

-- Temporary table to hold all unique requests
CREATE TEMP TABLE UNIQUE_REQUESTS AS
SELECT DISTINCT REQUEST_ID FROM LOG;

3. Combine Dates + Requests + Daily Status #

Now we'll generate every possible request-date combination, then match each to the last status change from the LOG table:

-- Create the final daily status table
CREATE TABLE IF NOT EXISTS DAILY_REQUEST_STATUS (
    REQUEST_ID VARCHAR(100),
    REF_DATE DATE,
    LAST_STATUS VARCHAR(50),
    STATUS_UPDATE_TIME TIMESTAMP,
    PRIMARY KEY (REQUEST_ID, REF_DATE)
);

-- Populate the table with complete daily status data
INSERT INTO DAILY_REQUEST_STATUS (REQUEST_ID, REF_DATE, LAST_STATUS, STATUS_UPDATE_TIME)
SELECT
    ur.REQUEST_ID,
    dd.REF_DATE,
    l.LAST_STATUS,
    l.UPDATE_TIME
FROM UNIQUE_REQUESTS ur
CROSS JOIN DATE_DIM dd
LEFT JOIN (
    -- Get the last status change for each request per day
    SELECT
        REQUEST_ID,
        DATE(UPDATE_TIME) AS REF_DATE,
        LAST_STATUS,
        UPDATE_TIME,
        ROW_NUMBER() OVER (PARTITION BY REQUEST_ID, DATE(UPDATE_TIME) ORDER BY UPDATE_TIME DESC) AS rn
    FROM LOG
) l ON ur.REQUEST_ID = l.REQUEST_ID AND dd.REF_DATE = l.REF_DATE
WHERE l.rn = 1 OR l.rn IS NULL; -- Include days where no status change happened

4. Optional: Fill in Missing Statuses (Carry Forward Previous Day's Status) #

If you want days with no status change to show the last known status (instead of NULL), use this window function to fill the gaps:

-- Update the table to carry forward the last non-null status
WITH FILLED_STATUS AS (
    SELECT
        REQUEST_ID,
        REF_DATE,
        LAST_STATUS,
        STATUS_UPDATE_TIME,
        LAST_VALUE(LAST_STATUS IGNORE NULLS) OVER (PARTITION BY REQUEST_ID ORDER BY REF_DATE) AS FILLED_LAST_STATUS,
        LAST_VALUE(STATUS_UPDATE_TIME IGNORE NULLS) OVER (PARTITION BY REQUEST_ID ORDER BY REF_DATE) AS FILLED_UPDATE_TIME
    FROM DAILY_REQUEST_STATUS
)
UPDATE DAILY_REQUEST_STATUS
SET
    LAST_STATUS = fs.FILLED_LAST_STATUS,
    STATUS_UPDATE_TIME = fs.FILLED_UPDATE_TIME
FROM FILLED_STATUS fs
WHERE DAILY_REQUEST_STATUS.REQUEST_ID = fs.REQUEST_ID AND DAILY_REQUEST_STATUS.REF_DATE = fs.REF_DATE;

Quick Tips to Keep This Running Smoothly #

• Maintain the calendar table: Add new dates periodically (or set up a job to auto-add future dates) so you never miss a day.
• Optimize performance: Add a composite index on LOG(REQUEST_ID, UPDATE_TIME) to speed up the daily status lookup.
• Incremental updates: Instead of reloading the entire table every day, set up a cron job or scheduled query to only sync the previous day's data.

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