Hive中如何处理Concat输出空值以生成指定格式结果

问题背景 #

现有Hive查询在数据非空时能输出正常格式化结果，但目标日期区间无数据时，整个查询返回NULL。需要调整逻辑，在空值场景下输出指定格式的结果：

| 20220807 | DB_shop | NULL | Data Not Found |

其中日期为传入的${PERIOD}参数值。

修改后的查询语句 #

WITH date_param AS (
    SELECT cast(${PERIOD} as int) AS target_date
),
trend_data AS (
    SELECT
        day PERIOD,
        'DB_shop' TB_NAME,
        CASE WHEN percentage IS NOT NULL THEN CONCAT(substring(cast(percentage AS STRING),1,5),'%') ELSE 'NULL' END AS PERCENTAGE,
        CASE
            WHEN percentage >= 20 THEN concat('Data increase ', cast(percentage AS STRING),'% from last day')
            WHEN percentage <= -20 THEN concat('Data drop ', cast(percentage AS STRING),'% from last day')
            WHEN percentage IS NULL THEN 'Data Not Found'
            ELSE 'Normal'
        END AS STATUS
    FROM (
        SELECT
            day,
            count(1) count_all,
            lag(count(1)) OVER(ORDER BY day) as PrevCount,
            round(((count(1) - lag(count(1)) OVER(ORDER BY day))/lag(count(1)) OVER(ORDER BY day))*100,2) percentage
        FROM DB_shop
        WHERE day BETWEEN cast(substring(regexp_replace(cast(date_add(to_date(from_unixtime(unix_timestamp(cast(${PERIOD} as string), 'yyyyMMdd'))),-1)  as string),'-',''),1,8) as int
          AND cast(substring(regexp_replace(cast(to_date(from_unixtime(unix_timestamp(cast(${PERIOD} as string), 'yyyyMMdd')))  as string),'-',''),1,8) as int
        GROUP BY day
        ORDER BY day DESC
        LIMIT 1
    )x
)
SELECT concat("| ", COALESCE(t.PERIOD, d.target_date), " | ", COALESCE(t.TB_NAME, 'DB_shop'), " | ", COALESCE(t.PERCENTAGE, 'NULL'), " | ", COALESCE(t.STATUS, 'Data Not Found'), " |") AS change_in_percentage
FROM date_param d
LEFT JOIN trend_data t ON 1=1;

关键改动说明 #

• 确保默认行存在：通过CTE date_param 获取传入的${PERIOD}参数，再用LEFT JOIN保证即使trend_data无数据，也能基于默认行输出结果。
• 空值兜底处理：
  • 用COALESCE函数为每个字段指定默认值：日期取传入的target_date，表名固定为DB_shop，百分比显示NULL，状态显示Data Not Found。
  • 调整PERCENTAGE字段的CASE逻辑，当percentage为空时直接返回字符串'NULL'，避免拼接时产生空值。
• 逻辑拆分简化：通过CTE拆分查询模块，让结构更清晰，便于后续维护。

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