如何在Presto查询中实现循环及缺失日期的日均增量计算

1. Can you use loops in Presto queries? #

Presto doesn’t support explicit procedural loops like FOR or WHILE—it’s built for declarative SQL, where you describe what you want, not how to loop to get it. That said, you can replicate loop-like logic using recursive CTEs (Common Table Expressions) or window functions, depending on your use case.

For example, if you need to generate a sequence of dates (a common task where loops might come to mind), a recursive CTE works perfectly:

WITH RECURSIVE date_sequence AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1' DAY
    FROM date_sequence
    WHERE dt < DATE '2024-01-10'
)
SELECT dt FROM date_sequence;

This recursively builds a list of dates from Jan 1 to Jan 10, mimicking the behavior of a loop that increments a date value. Recursive CTEs also shine for traversing hierarchical data or generating sequential ranges.

2. Calculating daily stats with handling for missing dates #

Your current view only compares each day to the immediate prior day, which breaks when there are gaps in dates. Let’s build a solution that dynamically finds the nearest valid values before/after missing dates, calculates the gap length, and computes the average daily increment using your formula.

Step-by-step solution #

We’ll split this into 3 core parts: generating a full date range for each post, filling in missing date records, and computing gap-aware incremental values.

Here’s the complete SQL adapted to your post_metrics table:

WITH RECURSIVE date_range AS (
    -- Get the min/max date for each post to define our full date range
    SELECT 
        post_id,
        page,
        brandname,
        MIN(dt) AS start_dt,
        MAX(dt) AS end_dt
    FROM hive.facebook.post_metrics
    GROUP BY post_id, page, brandname
    
    UNION ALL
    
    -- Recursively generate every date between start and end for each post
    SELECT 
        post_id,
        page,
        brandname,
        start_dt + INTERVAL '1' DAY AS start_dt,
        end_dt
    FROM date_range
    WHERE start_dt < end_dt
),
-- Join full date range with original data to include missing dates
full_post_dates AS (
    SELECT 
        dr.post_id,
        dr.page,
        dr.brandname,
        dr.start_dt AS dt,
        pm.likes,
        pm.created_time
    FROM date_range dr
    LEFT JOIN hive.facebook.post_metrics pm
        ON dr.post_id = pm.post_id
        AND dr.page = pm.page
        AND dr.brandname = pm.brandname
        AND dr.start_dt = pm.dt
),
-- Use window functions to pull nearest valid likes values and their dates
gap_filled_metrics AS (
    SELECT 
        *,
        -- Last valid likes value before current date
        LAST_VALUE(likes IGNORE NULLS) OVER (
            PARTITION BY post_id, page, brandname
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_valid_likes,
        -- Next valid likes value after current date
        FIRST_VALUE(likes IGNORE NULLS) OVER (
            PARTITION BY post_id, page, brandname
            ORDER BY dt
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_valid_likes,
        -- Date of the last valid likes value
        LAST_VALUE(dt IGNORE NULLS) OVER (
            PARTITION BY post_id, page, brandname
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_valid_dt,
        -- Date of the next valid likes value
        FIRST_VALUE(dt IGNORE NULLS) OVER (
            PARTITION BY post_id, page, brandname
            ORDER BY dt
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_valid_dt
    FROM full_post_dates
)
-- Calculate final daily increment, handling gaps
SELECT 
    post_id,
    page,
    dt,
    created_time,
    CASE
        -- For existing dates: increment from last valid prior date
        WHEN likes IS NOT NULL THEN
            COALESCE(likes - prev_valid_likes, 0)
        -- For missing dates: average daily increment over the gap
        ELSE
            (next_valid_likes - prev_valid_likes) / 
            DATE_DIFF('day', prev_valid_dt, next_valid_dt)
    END AS likes_increment
FROM gap_filled_metrics
ORDER BY post_id, dt;

How this works: #

1. date_range CTE: Generates every date between the first and last recorded date for each post, ensuring no dates are skipped.
2. full_post_dates: Creates rows for all date-post combinations, including missing dates (where likes will be NULL).
3. gap_filled_metrics: Uses LAST_VALUE and FIRST_VALUE with IGNORE NULLS to pull in the nearest valid likes values and their dates before/after each gap.
4. Final calculation:
   • For existing dates: Computes the increment from the last valid prior day (even if there was a gap before it).
   • For missing dates: Uses your formula (next_value - prev_value)/n (where n is the number of days between valid dates) to get the average daily increment.

You can turn this into a view by adding CREATE OR REPLACE VIEW hive.facebook.post_metrics_daily AS at the start of the query.

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