如何使用Hive/Pig补全用户分组下缺失年份的数值数据

Alright, let's solve this problem where we need to fill in the missing years for each [id1, id2] user group using the last available value. Here's a step-by-step approach using Hive SQL:

Step 1: Get the date range for each user group #

First, we need to figure out the minimum and maximum year for every [id1, id2] pair. This tells us the full range of years we need to cover for each group:

WITH user_year_ranges AS (
    SELECT 
        id1,
        id2,
        MIN(year) AS min_year,
        MAX(year) AS max_year
    FROM your_table_name
    GROUP BY id1, id2
)

Step 2: Generate all consecutive years for each group #

Using Hive's sequence() function (available in Hive 2.0+), we can generate every year between the min and max year for each group. We'll use posexplode() to turn the sequence into individual rows:

, user_all_years AS (
    SELECT 
        id1,
        id2,
        year_val AS year
    FROM user_year_ranges
    LATERAL VIEW posexplode(sequence(min_year, max_year)) exploded AS pos, year_val
)

Note: If you're using an older Hive version that doesn't support sequence(), you can create a temporary numbers table (with integers from 0 to, say, 100) and calculate the years by adding the number to the min_year.

Step 3: Join with original data and fill missing values #

Now we'll left join the full year list with the original table to get all years (including missing ones), then use the LAST_VALUE() window function with IGNORE NULLS to fill in the missing value with the last non-null value from the group:

SELECT 
    ay.id1,
    ay.id2,
    ay.year,
    LAST_VALUE(t.value, TRUE) OVER (
        PARTITION BY ay.id1, ay.id2 
        ORDER BY ay.year 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_value
FROM user_all_years ay
LEFT JOIN your_table_name t
    ON ay.id1 = t.id1 
    AND ay.id2 = t.id2 
    AND ay.year = t.year
ORDER BY ay.id1, ay.id2, ay.year;

Let's test this with your sample data #

For the group [1,1], the year 2001 was missing. The query will fill it with 20 (the value from 2000). For [2,3], 2007 is missing and will be filled with 56 (from 2006). The output will look like this:

1, 1, 2000, 20
1, 1, 2001, 20
1, 1, 2002, 23
1, 1, 2003, 24
1, 2, 1999, 34
1, 2, 2000, 35
1, 2, 2001, 37
2, 3, 2005, 50
2, 3, 2006, 56
2, 3, 2007, 56
2, 3, 2008, 60

Just replace your_table_name with the actual name of your Hive table, and this should work perfectly.

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