基于日期列合并含CTE的两个SQL查询结果的语法求助

算力阿峰

2026-7-28

问题需求

需要基于「date」列（即两个查询结果中的d_dates.date_date字段）对以下两个SQL查询的结果执行**内连接（INNER JOIN）**合并操作，但其中一个查询包含CTE（公共表表达式），且两个查询本身已有多表连接逻辑，不清楚正确的实现语法。

原始查询语句

Query1

(SELECT
    (TO_CHAR(TO_DATE(d_dates."DAY" ), 'YYYY-MM-DD')) AS "d_dates.date_date",
    COALESCE(SUM(CASE WHEN (( (f_traffic."D_TIMES_ID") = -1  )) AND (( d_platforms."PLATFORM"  ) = 'App') THEN ( f_traffic."VISITS" ) ELSE NULL END), 0) AS app_adobe
FROM DATA_MART.F_TRAFFIC  AS f_traffic
INNER JOIN DATA_MART.D_DATES  AS d_dates ON (f_traffic."WH_DATE_ID") = (d_dates."ID")
INNER JOIN DATA_MART.D_OBJECTS  AS d_objects ON
             (f_traffic."D_OBJECTS_ID") = (d_objects."ID")
INNER JOIN DATA_MART.D_PLATFORMS  AS d_platforms ON (f_traffic."D_PLATFORMS_ID") = (d_platforms."ID")
WHERE ((( d_dates."DAY"  ) >= ((TO_DATE(DATEADD('day', -2, CURRENT_DATE())))) AND ( d_dates."DAY"  ) < ((TO_DATE(DATEADD('day', 2, DATEADD('day', -2, CURRENT_DATE()))))))) AND (d_objects."OBJECT_GROUP" ) = 'WELT' AND (d_objects."OBJECT_NAME" ) = 'WELT'
GROUP BY
    (TO_DATE(d_dates."DAY" ))
ORDER BY
    1)

Query2

(WITH f_ivw_measures_with_plan_data AS (SELECT
      coalesce (f_ivw_measures.WH_DATE_ID, f_ivw_forecasts.WH_DATE_ID, f_ivw_plan_data.WH_DATE_ID)  AS WH_DATE_ID,
      f_ivw_plan_data.PLANNED_PAGE_IMPRESSIONS,
      case when to_date(concat(left(f_ivw_forecasts."WH_DATE_ID" ,4),'-' ,substr(f_ivw_forecasts."WH_DATE_ID" ,5,2),'-',right(f_ivw_forecasts."WH_DATE_ID" ,2)
      ))  >= current_date then  f_ivw_forecasts.VISITS else f_ivw_measures.VISITS end  AS VISITS_FORECAST,
      case when to_date(concat(left(f_ivw_forecasts."WH_DATE_ID" ,4),'-' ,substr(f_ivw_forecasts."WH_DATE_ID" ,5,2),'-',right(f_ivw_forecasts."WH_DATE_ID" ,2)
      ))  >= current_date then  f_ivw_forecasts.PAGE_IMPRESSIONS else f_ivw_measures.PAGE_IMPRESSIONS end AS PAGE_IMPRESSIONS_FORECAST
 FROM DATA_MART.F_IVW_MEASURES AS f_ivw_measures
      FULL OUTER JOIN DATA_MART.F_IVW_FORECASTS AS f_ivw_forecasts
        ON f_ivw_measures.WH_DATE_ID = f_ivw_forecasts.WH_DATE_ID
      AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_forecasts.D_IVW_OFFERS_ID
      AND f_ivw_measures.D_IVW_CODES_ID = f_ivw_forecasts.D_IVW_CODES_ID
      FULL OUTER JOIN DATA_MART.F_IVW_PLANNING_DATA AS f_ivw_plan_data
        ON f_ivw_measures.WH_DATE_ID = f_ivw_plan_data.WH_DATE_ID
      AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_plan_data.D_IVW_OFFERS_ID
       )
SELECT
    (TO_CHAR(TO_DATE(d_dates."DAY" ), 'YYYY-MM-DD')) AS "d_dates.date_date",
    COALESCE(SUM(CASE WHEN ((( d_ivw_offers."OFFER"  ) NOT IN ('AWPBILD', 'CTVBILD') OR (( d_ivw_offers."OFFER"  )) IS NULL)) AND (( d_platforms."PLATFORM"  ) = 'Desktop') THEN ( f_ivw_measures_with_plan_data.VISITS_FORECAST  )  ELSE NULL END), 0) AS web
FROM f_ivw_measures_with_plan_data
INNER JOIN DATA_MART.D_DATES  AS d_dates ON (f_ivw_measures_with_plan_data."WH_DATE_ID") = (d_dates."ID")
INNER JOIN DATA_MART.D_OBJECTS  AS d_objects ON (f_ivw_measures_with_plan_data."D_OBJECTS_ID") = (d_objects."ID")
INNER JOIN DATA_MART.D_IVW_OFFERS AS d_ivw_offers ON f_ivw_measures_with_plan_data."D_IVW_OFFERS_ID" = d_ivw_offers."ID"
INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_ivw_measures_with_plan_data."D_PLATFORMS_ID" = d_platforms."ID"
WHERE (((( d_dates."DAY"  )) < (TO_DATE(DATEADD('minute', 0, DATE_TRUNC('minute', CURRENT_TIMESTAMP())))))) AND (d_objects."OBJECT_GROUP" ) = 'WELT' AND (d_objects."OBJECT_NAME" ) = 'WELT'
GROUP BY
    (TO_DATE(d_dates."DAY" ))
ORDER BY
    1 DESC)

实现方案

以下提供两种可行的内连接实现方式，同时修正了原Query2中的语法错误与逻辑遗漏：

方式一：子查询直接连接

将两个查询分别作为子查询，通过d_dates.date_date字段执行内连接：

SELECT
    q1."d_dates.date_date",
    q1.app_adobe,
    q2.web
FROM
    (
        -- Query1 原始逻辑
        SELECT
            TO_CHAR(TO_DATE(d_dates."DAY"), 'YYYY-MM-DD') AS "d_dates.date_date",
            COALESCE(SUM(CASE WHEN f_traffic."D_TIMES_ID" = -1 AND d_platforms."PLATFORM" = 'App' THEN f_traffic."VISITS" ELSE NULL END), 0) AS app_adobe
        FROM DATA_MART.F_TRAFFIC AS f_traffic
        INNER JOIN DATA_MART.D_DATES AS d_dates ON f_traffic."WH_DATE_ID" = d_dates."ID"
        INNER JOIN DATA_MART.D_OBJECTS AS d_objects ON f_traffic."D_OBJECTS_ID" = d_objects."ID"
        INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_traffic."D_PLATFORMS_ID" = d_platforms."ID"
        WHERE 
            d_dates."DAY" >= TO_DATE(DATEADD('day', -2, CURRENT_DATE()))
            AND d_dates."DAY" < TO_DATE(DATEADD('day', 2, DATEADD('day', -2, CURRENT_DATE())))
            AND d_objects."OBJECT_GROUP" = 'WELT'
            AND d_objects."OBJECT_NAME" = 'WELT'
        GROUP BY TO_DATE(d_dates."DAY")
    ) q1
INNER JOIN
    (
        -- Query2 修正后的逻辑
        WITH f_ivw_measures_with_plan_data AS (
            SELECT
                COALESCE(f_ivw_measures.WH_DATE_ID, f_ivw_forecasts.WH_DATE_ID, f_ivw_plan_data.WH_DATE_ID) AS WH_DATE_ID,
                f_ivw_plan_data.PLANNED_PAGE_IMPRESSIONS,
                CASE 
                    WHEN TO_DATE(CONCAT(LEFT(f_ivw_forecasts."WH_DATE_ID",4),'-',SUBSTR(f_ivw_forecasts."WH_DATE_ID",5,2),'-',RIGHT(f_ivw_forecasts."WH_DATE_ID",2))) >= CURRENT_DATE 
                    THEN f_ivw_forecasts.VISITS 
                    ELSE f_ivw_measures.VISITS 
                END AS VISITS_FORECAST,
                CASE 
                    WHEN TO_DATE(CONCAT(LEFT(f_ivw_forecasts."WH_DATE_ID",4),'-',SUBSTR(f_ivw_forecasts."WH_DATE_ID",5,2),'-',RIGHT(f_ivw_forecasts."WH_DATE_ID",2))) >= CURRENT_DATE 
                    THEN f_ivw_forecasts.PAGE_IMPRESSIONS 
                    ELSE f_ivw_measures.PAGE_IMPRESSIONS 
                END AS PAGE_IMPRESSIONS_FORECAST,
                f_ivw_measures.D_IVW_OFFERS_ID,
                f_ivw_measures.D_PLATFORMS_ID,
                f_ivw_measures.D_OBJECTS_ID
            FROM DATA_MART.F_IVW_MEASURES AS f_ivw_measures
            FULL OUTER JOIN DATA_MART.F_IVW_FORECASTS AS f_ivw_forecasts
                ON f_ivw_measures.WH_DATE_ID = f_ivw_forecasts.WH_DATE_ID
                AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_forecasts.D_IVW_OFFERS_ID
                AND f_ivw_measures.D_IVW_CODES_ID = f_ivw_forecasts.D_IVW_CODES_ID
            FULL OUTER JOIN DATA_MART.F_IVW_PLANNING_DATA AS f_ivw_plan_data
                ON f_ivw_measures.WH_DATE_ID = f_ivw_plan_data.WH_DATE_ID
                AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_plan_data.D_IVW_OFFERS_ID
        )
        SELECT
            TO_CHAR(TO_DATE(d_dates."DAY"), 'YYYY-MM-DD') AS "d_dates.date_date",
            COALESCE(SUM(CASE WHEN (d_ivw_offers."OFFER" NOT IN ('AWPBILD', 'CTVBILD') OR d_ivw_offers."OFFER" IS NULL) AND d_platforms."PLATFORM" = 'Desktop' THEN f_ivw_measures_with_plan_data.VISITS_FORECAST ELSE NULL END), 0) AS web
        FROM f_ivw_measures_with_plan_data
        INNER JOIN DATA_MART.D_DATES AS d_dates ON f_ivw_measures_with_plan_data."WH_DATE_ID" = d_dates."ID"
        INNER JOIN DATA_MART.D_OBJECTS AS d_objects ON f_ivw_measures_with_plan_data."D_OBJECTS_ID" = d_objects."ID"
        INNER JOIN DATA_MART.D_IVW_OFFERS AS d_ivw_offers ON f_ivw_measures_with_plan_data."D_IVW_OFFERS_ID" = d_ivw_offers."ID"
        INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_ivw_measures_with_plan_data."D_PLATFORMS_ID" = d_platforms."ID"
        WHERE 
            d_dates."DAY" < TO_DATE(DATEADD('minute', 0, DATE_TRUNC('minute', CURRENT_TIMESTAMP())))
            AND d_objects."OBJECT_GROUP" = 'WELT'
            AND d_objects."OBJECT_NAME" = 'WELT'
        GROUP BY TO_DATE(d_dates."DAY")
    ) q2
ON q1."d_dates.date_date" = q2."d_dates.date_date"
ORDER BY q1."d_dates.date_date";

方式二：统一CTE结构

将两个查询的结果都定义为顶层CTE，再执行内连接，可读性更强：

WITH query1_results AS (
    -- Query1 逻辑
    SELECT
        TO_CHAR(TO_DATE(d_dates."DAY"), 'YYYY-MM-DD') AS date_date,
        COALESCE(SUM(CASE WHEN f_traffic."D_TIMES_ID" = -1 AND d_platforms."PLATFORM" = 'App' THEN f_traffic."VISITS" ELSE NULL END), 0) AS app_adobe
    FROM DATA_MART.F_TRAFFIC AS f_traffic
    INNER JOIN DATA_MART.D_DATES AS d_dates ON f_traffic."WH_DATE_ID" = d_dates."ID"
    INNER JOIN DATA_MART.D_OBJECTS AS d_objects ON f_traffic."D_OBJECTS_ID" = d_objects."ID"
    INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_traffic."D_PLATFORMS_ID" = d_platforms."ID"
    WHERE 
        d_dates."DAY" >= TO_DATE(DATEADD('day', -2, CURRENT_DATE()))
        AND d_dates."DAY" < TO_DATE(DATEADD('day', 2, DATEADD('day', -2, CURRENT_DATE())))
        AND d_objects."OBJECT_GROUP" = 'WELT'
        AND d_objects."OBJECT_NAME" = 'WELT'
    GROUP BY TO_DATE(d_dates."DAY")
),
query2_results AS (
    -- Query2 修正后的逻辑
    WITH f_ivw_measures_with_plan_data AS (
        SELECT
            COALESCE(f_ivw_measures.WH_DATE_ID, f_ivw_forecasts.WH_DATE_ID, f_ivw_plan_data.WH_DATE_ID) AS WH_DATE_ID,
            f_ivw_plan_data.PLANNED_PAGE_IMPRESSIONS,
            CASE 
                WHEN TO_DATE(CONCAT(LEFT(f_ivw_forecasts."WH_DATE_ID",4),'-',SUBSTR(f_ivw_forecasts."WH_DATE_ID",5,2),'-',RIGHT(f_ivw_forecasts."WH_DATE_ID",2))) >= CURRENT_DATE 
                THEN f_ivw_forecasts.VISITS 
                ELSE f_ivw_measures.VISITS 
            END AS VISITS_FORECAST,
            CASE 
                WHEN TO_DATE(CONCAT(LEFT(f_ivw_forecasts."WH_DATE_ID",4),'-',SUBSTR(f_ivw_forecasts."WH_DATE_ID",5,2),'-',RIGHT(f_ivw_forecasts."WH_DATE_ID",2))) >= CURRENT_DATE 
                THEN f_ivw_forecasts.PAGE_IMPRESSIONS 
                ELSE f_ivw_measures.PAGE_IMPRESSIONS 
            END AS PAGE_IMPRESSIONS_FORECAST,
            f_ivw_measures.D_IVW_OFFERS_ID,
            f_ivw_measures.D_PLATFORMS_ID,
            f_ivw_measures.D_OBJECTS_ID
        FROM DATA_MART.F_IVW_MEASURES AS f_ivw_measures
        FULL OUTER JOIN DATA_MART.F_IVW_FORECASTS AS f_ivw_forecasts
            ON f_ivw_measures.WH_DATE_ID = f_ivw_forecasts.WH_DATE_ID
            AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_forecasts.D_IVW_OFFERS_ID
            AND f_ivw_measures.D_IVW_CODES_ID = f_ivw_forecasts.D_IVW_CODES_ID
        FULL OUTER JOIN DATA_MART.F_IVW_PLANNING_DATA AS f_ivw_plan_data
            ON f_ivw_measures.WH_DATE_ID = f_ivw_plan_data.WH_DATE_ID
            AND f_ivw_measures.D_IVW_OFFERS_ID = f_ivw_plan_data.D_IVW_OFFERS_ID
    )
    SELECT
        TO_CHAR(TO_DATE(d_dates."DAY"), 'YYYY-MM-DD') AS date_date,
        COALESCE(SUM(CASE WHEN (d_ivw_offers."OFFER" NOT IN ('AWPBILD', 'CTVBILD') OR d_ivw_offers."OFFER" IS NULL) AND d_platforms."PLATFORM" = 'Desktop' THEN f_ivw_measures_with_plan_data.VISITS_FORECAST ELSE NULL END), 0) AS web
    FROM f_ivw_measures_with_plan_data
    INNER JOIN DATA_MART.D_DATES AS d_dates ON f_ivw_measures_with_plan_data."WH_DATE_ID" = d_dates."ID"
    INNER JOIN DATA_MART.D_OBJECTS AS d_objects ON f_ivw_measures_with_plan_data."D_OBJECTS_ID" = d_objects."ID"
    INNER JOIN DATA_MART.D_IVW_OFFERS AS d_ivw_offers ON f_ivw_measures_with_plan_data."D_IVW_OFFERS_ID" = d_ivw_offers."ID"
    INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_ivw_measures_with_plan_data."D_PLATFORMS_ID" = d_platforms."ID"
    WHERE 
        d_dates."DAY" < TO_DATE(DATEADD('minute', 0, DATE_TRUNC('minute', CURRENT_TIMESTAMP())))
        AND d_objects."OBJECT_GROUP" = 'WELT'
        AND d_objects."OBJECT_NAME" = 'WELT'
    GROUP BY TO_DATE(d_dates."DAY")
)
SELECT
    q1.date_date,
    q1.app_adobe,
    q2.web
FROM query1_results q1
INNER JOIN query2_results q2 ON q1.date_date = q2.date_date
ORDER BY q1.date_date;

关键修正说明

修正了原Query2中WH_DATE_ID字段后的无效符号..,,,
原Query2中引用了`

基于日期列合并含CTE的两个SQL查询结果的语法求助

原始查询语句 #

Query1 #

Query2 #

方式一：子查询直接连接 #

方式二：统一CTE结构 #