postgresql 如何重用案例查询值？

ogq8wdun 于 2024-01-07 发布在 PostgreSQL

关注(0)|答案(1)|浏览(108)

我有一个如下的查询，我重用逻辑将时间戳转换为一个统一的形式。我有一些记录，其中日期列看起来像varchar '2023-12-28'，时间戳看起来像varchar '1646376986'。
我用以下方式解析它们：

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 and (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;

型

postgresql

来源：https://stackoverflow.com/questions/77725479/how-to-reuse-case-query-values

1条答案

按热度按时间

q3aa05251#

使用CTE，然后在SELECT查询中使用它，如下所示：

-- USE CTE
WITH CTE AS (
    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 and (object_name ILIKE '%' || COALESCE(@object_name, '') || '%')
      and (industry_id = @industry_id or 0 = @industry_id)
      )
SELECT * FROM CTE  -- SELECT FROM CTE AND USE THE conclusion_date
WHERE  conclusion_date::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(
                 conclusion_date,
                 'YYYY-MM-DD HH24:MI:SS.US'
         )
offset $1 limit $2;

字符串
请注意，这并不能保证良好的性能，但是的，查询的复杂性降低了。

赞(0）回复(0）举报 2024-01-07

我来回答

postgresql 如何重用案例查询值？

1条答案

相关问题

热门标签

最新问答