sql - 有没有更优雅的方式来重用代码块？

Question

我喜欢像下面这样编写我的 SQL。

好处是我可以重复使用多次使用的代码块，并且当我修复某些内容时，我总是只需要在一个地方修复它。

例如我写了两次current_year而不是提取(sys_date中的年份)。

其他示例，我写了两次 first_of_april_this_year 而不是 to_date ('01.APR.'|| current_year ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS')。

它可以工作，但正如您在下面看到的，它不是很漂亮/易于阅读。您是否有更好的建议，让我既不会失去不必在多个地方修复某些内容的好处，又使其更具可读性？我还使用了 with 子句，但恕我直言，它的可读性更差。非常感谢! <3

PS:请让我们继续讨论重用代码块的主题，不要深入探讨如何以可测试的方式更好地完成查找前 4 月 1 日的具体任务。 TY!

-- gives the latest 1st of April based on sysdate, whereas for testing sysdate can freely be set     
select 
    case when sys_date <= first_of_april_this_year then first_of_april_last_year else first_of_april_this_year end previous_first_of_april
    -- ,params3.* 
from (    
    select 
        to_date ('01.APR.'|| current_year    ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_this_year,
        to_date ('01.APR.'||(current_year-1) ||' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_last_year,
        params2.*
    from (
        select 
            extract (year from sys_date) current_year,
            params1.* 
        from
        (select 
            to_date ('02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date  -- for testing, sysdate can be overwritten
            --sysdate sys_date 
        from dual) params1 
    ) params2 
) params3;

Answer 1

您可以使用子查询分解(又名通用表表达式，又名 CTE)来使具有多个子查询的查询更易于阅读。例如。您的查询将变为:

WITH params1 AS (SELECT to_date('02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date -- for testing, sysdate can be overwritten
                 --sysdate sys_date 
                 FROM   dual),
     params2 AS (SELECT extract(YEAR FROM sys_date) current_year,
                        sys_date
                 FROM   params1),
     params3 AS (SELECT to_date('01.APR.' || current_year || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_this_year,
                        to_date('01.APR.' || (current_year - 1) || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_last_year,
                        sys_date
                 FROM   params2)
SELECT CASE
         WHEN sys_date <= first_of_april_this_year THEN
          first_of_april_last_year
         ELSE
          first_of_april_this_year
       END previous_first_of_april
FROM   params3;

顺便说一句，根据日期算出 4 月 1 日可以更简单:

SELECT add_months(TRUNC(add_months(SYSDATE, -3), 'yyyy'), 3)
FROM   dual;

在这里，我们从指定日期回退 3 个月，将结果日期截断为当年(以达到当年的 1 月 1 日)，然后简单地向后添加 3 个月，以达到当年的 4 月 1 日。