如何替换带条件的SQL子查询？兼Vertica日期函数替代方案

一、替换JOIN中的子查询 #

原查询通过子查询找到同Term下大于Inv_Dt日部分的最小TAG，可通过以下两种方案替换：

方案1：用ROW_NUMBER()窗口函数实现 #

先对ZT05按Term分组并按TAG升序排序，关联时筛选符合条件的首行：

WITH ZT05_Ranked AS (
    SELECT 
        TERM, TAG, FAEL, MONA, TAG1,
        ROW_NUMBER() OVER (PARTITION BY TERM ORDER BY TAG ASC) AS rn
    FROM ZT05
)
SELECT 
    ZBK.INV_DT,
    ZLF.TERM,
    T1.TAG,
    T1.TAG1,
    T1.FAEL,
    T1.MONA,
    -- 日期计算部分后续替换为Vertica兼容逻辑
    DATEDIFF(
        'DAYS',
        ZBK.INV_DT,
        ADD_MONTHS(
            TRUNC(ZBK.INV_DT, 'MONTH') + (T1.FAEL - 1)::INTERVAL '1 day',
            T1.MONA
        )
    ) + T1.TAG1 AS SUM_DAYS
FROM ZBK
INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
INNER JOIN ZT05_Ranked T1 
    ON ZLF.TERM = T1.TERM
    AND T1.TAG > DAY(ZBK.INV_DT)
WHERE T1.rn = 1 -- 取同Term下符合条件的最小TAG
ORDER BY ZBK.INV_DT;

方案2：创建预排序临时表 #

如果允许创建额外表，提前处理ZT05的TAG区间匹配关系：

-- 创建临时表存储每个Term的TAG匹配规则
CREATE TEMP TABLE ZT05_TAG_MATCH AS
SELECT 
    TERM,
    TAG,
    FAEL,
    MONA,
    TAG1,
    -- 获取当前TAG的前一个最大TAG，用于匹配Inv_Dt日的区间
    LAG(TAG) OVER (PARTITION BY TERM ORDER BY TAG DESC) AS prev_max_tag
FROM ZT05
ORDER BY TERM, TAG ASC;

-- 关联查询
SELECT 
    ZBK.INV_DT,
    ZLF.TERM,
    T1.TAG,
    T1.TAG1,
    T1.FAEL,
    T1.MONA,
    DATEDIFF(
        'DAYS',
        ZBK.INV_DT,
        ADD_MONTHS(
            TRUNC(ZBK.INV_DT, 'MONTH') + (T1.FAEL - 1)::INTERVAL '1 day',
            T1.MONA
        )
    ) + T1.TAG1 AS SUM_DAYS
FROM ZBK
INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
INNER JOIN ZT05_TAG_MATCH T1 
    ON ZLF.TERM = T1.TERM
    AND T1.TAG > DAY(ZBK.INV_DT)
    -- 确保当前TAG是大于Inv_Dt日的最小值
    AND (T1.prev_max_tag IS NULL OR T1.prev_max_tag <= DAY(ZBK.INV_DT))
ORDER BY ZBK.INV_DT;

二、Vertica替代DATE_FROM_PARTS的方案 #

Vertica不支持DATE_FROM_PARTS，有两种等价实现方式：

方式1：基于当月第一天计算 #

先截断Inv_Dt到当月第一天，再加上(FAEL-1)天得到目标日期：

TRUNC(ZBK.INV_DT, 'MONTH') + (T1.FAEL - 1)::INTERVAL '1 day'

比如Inv_Dt是2023-03-17，TRUNC后是2023-03-01，FAEL=20的话，加19天得到2023-03-20，和DATE_FROM_PARTS(YEAR(Inv_Dt), MONTH(Inv_Dt), FAEL)结果一致。

方式2：拼接字符串转日期 #

将年、月、日拼成标准格式字符串，用TO_DATE转换：

TO_DATE(
    CONCAT(
        YEAR(ZBK.INV_DT), '-',
        LPAD(MONTH(ZBK.INV_DT), 2, '0'), '-',
        LPAD(T1.FAEL, 2, '0')
    ),
    'YYYY-MM-DD'
)

用LPAD补零是为了避免单数字月/日导致转换失败，比如把3月转成03，5日转成05。

完整兼容Vertica的查询 #

结合上述两个方案，完整查询如下：

WITH ZT05_Ranked AS (
    SELECT 
        TERM, TAG, FAEL, MONA, TAG1,
        ROW_NUMBER() OVER (PARTITION BY TERM ORDER BY TAG ASC) AS rn
    FROM ZT05
)
SELECT 
    TO_CHAR(ZBK.INV_DT, 'YYYY-MM-DD') AS inv_dt,
    ZLF.TERM,
    T1.TAG,
    T1.TAG1,
    T1.FAEL,
    T1.MONA,
    DATEDIFF(
        'DAYS',
        ZBK.INV_DT,
        ADD_MONTHS(
            TRUNC(ZBK.INV_DT, 'MONTH') + (T1.FAEL - 1)::INTERVAL '1 day',
            T1.MONA
        )
    ) + T1.TAG1 AS sum_days,
    -- 可选生成参考用的list列
    CASE
        WHEN T1.MONA = 0 THEN CONCAT(DATEDIFF('DAYS', ZBK.INV_DT, TRUNC(ZBK.INV_DT, 'MONTH') + (T1.FAEL - 1)::INTERVAL '1 day'), ' + ', T1.TAG1, '(tag1)')
        ELSE CONCAT(
            DATEDIFF('DAYS', ZBK.INV_DT, LAST_DAY(ZBK.INV_DT)), '+',
            (T1.MONA - 1)*30, '+', T1.FAEL, ' + ', T1.TAG1, '(tag1)'
        )
    END AS list
FROM ZBK
INNER JOIN ZLF ON ZBK.VENDOR = ZLF.VENDOR
INNER JOIN ZT05_Ranked T1 
    ON ZLF.TERM = T1.TERM
    AND T1.TAG > DAY(ZBK.INV_DT)
WHERE T1.rn = 1
ORDER BY ZBK.INV_DT;

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