如何复用SQL查询中的CASE逻辑？

我的查询里需要把两种格式的字符串日期（'2023-12-28'格式的日期字符串和'1646376986'格式的时间戳字符串）统一转换成YYYY-MM-DD格式。目前用的转换逻辑如下：

case
    when
        conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
    then
        conclusion_date
    else
        to_char(to_timestamp(conclusion_date::numeric), 'YYYY-MM-DD')
end as conclusion_date

但这段逻辑在完整查询里重复了3次，担心会带来性能和资源浪费的问题，有没有更好的复用方式？

完整查询代码：

SELECT id,
       case
           when
               conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
               then
               conclusion_date
           else
               to_char(to_timestamp(conclusion_date::numeric), 'YYYY-MM-DD')
           end as conclusion_date,
       object_name,
       request_id,
       street,
       industry_id,
       object_type_name,
       category_id
from ergrequests
WHERE (object_name ILIKE '%' || COALESCE(@object_name, '') || '%')
  and (industry_id = @industry_id or 0 = @industry_id)
  and (case
           when
               conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
               then
               conclusion_date
           else
               to_char(to_timestamp(conclusion_date::numeric), 'YYYY-MM-DD')
    end)::timestamp between
    case
        when @date_from = '' then '2019-01-01'::timestamp
        else @date_from::timestamp
        end and
    case
        when @date_to = '' then CURRENT_TIMESTAMP
        else @date_to::timestamp
        end
order by to_timestamp(
                 case
                     when
                         conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
                         then
                         conclusion_date
                     else
                         to_char(to_timestamp(conclusion_date::numeric), 'YYYY-MM-DD')
                     end,
                 'YYYY-MM-DD HH24:MI:SS.US'
         )
offset $1 limit $2;

优化方案 #

1. 使用CTE（公共表表达式） #

把日期转换逻辑提取到CTE中，后续查询直接引用转换后的字段，代码更简洁，PostgreSQL会自动优化执行计划，避免重复计算。

示例代码：

WITH processed_requests AS (
    SELECT 
        id,
        conclusion_date,
        object_name,
        request_id,
        street,
        industry_id,
        object_type_name,
        category_id,
        -- 直接转换为timestamp类型，后续复用更高效
        CASE
            WHEN conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
                THEN conclusion_date::timestamp
            ELSE to_timestamp(conclusion_date::numeric)
        END AS normalized_conclusion_date
    FROM ergrequests
)
SELECT 
    id,
    to_char(normalized_conclusion_date, 'YYYY-MM-DD') AS conclusion_date,
    object_name,
    request_id,
    street,
    industry_id,
    object_type_name,
    category_id
FROM processed_requests
WHERE (object_name ILIKE '%' || COALESCE(@object_name, '') || '%')
  AND (industry_id = @industry_id OR 0 = @industry_id)
  AND normalized_conclusion_date BETWEEN 
      CASE WHEN @date_from = '' THEN '2019-01-01'::timestamp ELSE @date_from::timestamp END
      AND 
      CASE WHEN @date_to = '' THEN CURRENT_TIMESTAMP ELSE @date_to::timestamp END
ORDER BY normalized_conclusion_date
OFFSET $1 LIMIT $2;

2. 使用子查询 #

和CTE原理一致，把转换逻辑放在子查询里，主查询直接复用转换后的字段，适合不习惯用CTE的场景：

SELECT 
    id,
    to_char(normalized_conclusion_date, 'YYYY-MM-DD') AS conclusion_date,
    object_name,
    request_id,
    street,
    industry_id,
    object_type_name,
    category_id
FROM (
    SELECT 
        id,
        conclusion_date,
        object_name,
        request_id,
        street,
        industry_id,
        object_type_name,
        category_id,
        CASE
            WHEN conclusion_date ~ '^\d{4}-\d{2}-\d{2}$'
                THEN conclusion_date::timestamp
            ELSE to_timestamp(conclusion_date::numeric)
        END AS normalized_conclusion_date
    FROM ergrequests
) AS processed_requests
WHERE (object_name ILIKE '%' || COALESCE(@object_name, '') || '%')
  AND (industry_id = @industry_id OR 0 = @industry_id)
  AND normalized_conclusion_date BETWEEN 
      CASE WHEN @date_from = '' THEN '2019-01-01'::timestamp ELSE @date_from::timestamp END
      AND 
      CASE WHEN @date_to = '' THEN CURRENT_TIMESTAMP ELSE @date_to::timestamp END
ORDER BY normalized_conclusion_date
OFFSET $1 LIMIT $2;

3. 创建自定义函数（多查询复用场景） #

如果这个日期转换逻辑在多个查询中都要用到，封装成自定义函数是更高效的选择：

CREATE OR REPLACE FUNCTION normalize_conclusion_date(date_str VARCHAR)
RETURNS TIMESTAMP AS $$
BEGIN
    IF date_str ~ '^\d{4}-\d{2}-\d{2}$' THEN
        RETURN date_str::TIMESTAMP;
    ELSE
        RETURN to_timestamp(date_str::NUMERIC);
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

IMMUTABLE标记表示函数输入相同值时输出固定，PostgreSQL可以对其进行缓存优化，进一步提升性能。

简化后的查询：

SELECT 
    id,
    to_char(normalize_conclusion_date(conclusion_date), 'YYYY-MM-DD') AS conclusion_date,
    object_name,
    request_id,
    street,
    industry_id,
    object_type_name,
    category_id
FROM ergrequests
WHERE (object_name ILIKE '%' || COALESCE(@object_name, '') || '%')
  AND (industry_id = @industry_id OR 0 = @industry_id)
  AND normalize_conclusion_date(conclusion_date) BETWEEN 
      CASE WHEN @date_from = '' THEN '2019-01-01'::timestamp ELSE @date_from::timestamp END
      AND 
      CASE WHEN @date_to = '' THEN CURRENT_TIMESTAMP ELSE @date_to::timestamp END
ORDER BY normalize_conclusion_date(conclusion_date)
OFFSET $1 LIMIT $2;

额外优化建议 #

• 如果业务允许，建议修改表结构，将conclusion_date字段直接改为TIMESTAMP类型，从根源上避免格式转换的开销。
• 尽量避免在WHERE子句中对原始字段重复应用函数，上述方案通过提前转换统一格式，已经减少了重复计算的损耗。

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