Presto DB中基于日期区间合并数据集的正确方法求助

我明白你的需求：要合并df和interval数据集，既要让id=1显示完整的日期区间（2000-2005），又不能丢失id=3，同时只保留df中存在的ID，还要适配Presto的大数据场景。

先分析你当前代码的问题：你先把all_dates和df按日期匹配，这就导致id=1的2000-2002这些日期因为在df里没有对应记录，后续和interval关联时也无法匹配，自然就丢失了。

下面是适配Presto的解决方案，核心思路是先关联ID对应的区间，再生成该区间内的所有日期，最后整合无区间的ID数据：

WITH interval AS (
    SELECT 2000 AS startd, 2005 AS endd, 1 AS id
    UNION ALL SELECT 2003 AS startd, 2005 AS endd, 2 AS id
    UNION ALL SELECT 2000 AS startd, 2006 AS endd, 4 AS id
),
all_dates AS (
    SELECT 2000 AS date
    UNION ALL SELECT 2001 AS date
    UNION ALL SELECT 2002 AS date
    UNION ALL SELECT 2003 AS date
    UNION ALL SELECT 2004 AS date
    UNION ALL SELECT 2005 AS date
    UNION ALL SELECT 2006 AS date
),
df AS (
    SELECT 2003 AS date, 1 AS id
    UNION ALL SELECT 2004 AS date, 1 AS id
    UNION ALL SELECT 2005 AS date, 1 AS id
    UNION ALL SELECT 2003 AS date, 2 AS id
    UNION ALL SELECT 2004 AS date, 2 AS id
    UNION ALL SELECT 2005 AS date, 2 AS id
    UNION ALL SELECT 2000 AS date, 3 AS id
),
-- 提取df中存在的所有唯一ID，过滤掉df里没有的id（比如interval里的id=4）
df_unique_ids AS (
    SELECT DISTINCT id FROM df
),
-- 处理有区间的ID：生成该ID区间内的所有日期
interval_ids_dates AS (
    SELECT ad.date, i.id
    FROM df_unique_ids dui
    JOIN interval i ON dui.id = i.id
    JOIN all_dates ad ON ad.date BETWEEN i.startd AND i.endd
),
-- 处理无区间的ID：保留df中原本的日期记录
no_interval_ids_dates AS (
    SELECT d.date, d.id
    FROM df d
    LEFT JOIN interval i ON d.id = i.id
    WHERE i.id IS NULL
)
-- 合并两部分数据，再和all_dates关联补全空日期
SELECT ad.date, COALESCE(combined.id, NULL) AS id
FROM all_dates ad
LEFT JOIN (
    SELECT * FROM interval_ids_dates
    UNION ALL
    SELECT * FROM no_interval_ids_dates
) combined ON ad.date = combined.date
ORDER BY ad.date DESC;

代码说明： #

• df_unique_ids：确保只处理df中存在的ID，自动过滤掉interval里的id=4（因为df里没有这个ID）。
• interval_ids_dates：将有区间的ID和all_dates关联，生成该ID区间内的所有日期，这样id=1的2000-2005所有日期都会被生成。
• no_interval_ids_dates：单独处理没有对应区间的ID（比如id=3），保留它在df中的原始日期记录。
• 最后合并两部分数据，再和all_dates做左连接，补全所有日期，得到你想要的结果。

这个方案在Presto中可以高效处理大数据量，因为所有操作都是基于集合的关联和过滤，避免了低效的嵌套循环。

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