SQL代码简化咨询：值与空值校验语句优化需求

首先来说你遇到的单个条件简化问题：你想把(CF3.STRINGVALUE = 'Empty' OR CF3.STRINGVALUE IS NULL)写成(CF3.STRINGVALUE = 'Empty' OR IS NULL)，但SQL语法要求IS NULL必须明确指定要检查的列，所以这种简写是不被支持的。不过我们有更优雅的替代写法，既能简化代码，又符合语法规范：

单个条件的简化方案 #

你可以用COALESCE函数（ANSI SQL标准，兼容性好）或者SQL Server专属的ISNULL函数来合并判断：

• 使用COALESCE：

  COALESCE(CF3.STRINGVALUE, 'Empty') = 'Empty'
  

  这个函数会把CF3.STRINGVALUE的NULL值替换成'Empty'，然后直接判断是否等于'Empty'，和原条件逻辑完全一致。
• 使用ISNULL（仅SQL Server）：

  ISNULL(CF3.STRINGVALUE, 'Empty') = 'Empty'
  

  效果和COALESCE相同，但COALESCE支持多个参数，兼容性更广。

整体代码的优化建议 #

你的SQL里有大量重复的窗口函数和条件判断，这会让代码难以维护，还可能导致数据库重复计算相同的逻辑。下面是针对整体代码的优化方案：

1. 提取重复的窗口函数到CTE #

把多次用到的LAG窗口函数提前计算好，后续直接引用，减少重复代码和计算量。

2. 用IN简化多值OR判断 #

比如(ist.pname = 'Avería (FTTH)' OR ist.pname ='Avería (xDSL)')可以简化为ist.pname IN ('Avería (FTTH)', 'Avería (xDSL)')，更简洁易读。

3. 复用重复的组合条件 #

Reitero CM和Reitero MM的大部分判断条件是重复的，我们可以先在CTE里计算这个共同条件的结果，后面直接复用。

优化后的完整SQL代码 #

WITH JiraIssueWithLag AS (
    SELECT
        jis.created,
        jis.resolutiondate,
        jis.issuenum,
        jis.issuetype,
        CF1.STRINGVALUE AS cf_group_id, -- 可根据实际字段含义修改别名
        CF3.STRINGVALUE AS cf_empty_flag,
        cfo8.customvalue AS solved_in_first_go,
        cfo1.customvalue AS averia_motivo,
        ist.pname AS issue_type,
        -- 计算重复的窗口函数
        LAG(jis.resolutiondate) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) AS prev_resolution_date,
        LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) AS prev_issue_type,
        LAG(cfo1.customvalue, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) AS prev_averia_motivo,
        -- 计算共同的判断条件
        CASE
            WHEN DATEDIFF(ss, LAG(jis.resolutiondate) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC), jis.CREATED) < 604800
                AND COALESCE(CF3.STRINGVALUE, 'Empty') = 'Empty'
                AND COALESCE(cfo8.customvalue, 'NO') = 'NO'
                AND ist.pname IN ('Avería (FTTH)', 'Avería (xDSL)')
                AND LAG(ist.pname, 1, 0) OVER (PARTITION BY CF1.STRINGVALUE ORDER BY CF1.STRINGVALUE, jis.issuenum ASC) IN ('Avería (FTTH)', 'Avería (xDSL)')
            THEN 1
            ELSE 0
        END AS common_reitero_condition
    FROM [DWH].[JIR].[jiraissue] jis 
    LEFT JOIN [DWH].[JIR].[customfieldvalue] CF1 ON (CF1.issue = jis.id AND CF1.CUSTOMFIELD = 10004) 
    LEFT JOIN [DWH].[JIR].[customfieldvalue] CF2 ON (CF2.issue = jis.id AND CF2.CUSTOMFIELD = 10026) /*Motivo de la Averia*/ 
    LEFT JOIN [DWH].[JIR].customfieldoption cfo1 ON (CF2.customfield = cfo1.customfield AND CF2.stringvalue=CAST(cfo1.id AS CHAR)) 
    LEFT JOIN [DWH].[JIR].[customfieldvalue] CF3 ON (CF3.issue = jis.id AND CF3.CUSTOMFIELD = 10032) 
    LEFT JOIN [DWH].[JIR].[customfieldvalue] CF14 ON (CF14.issue = jis.id AND CF14.CUSTOMFIELD = 10906) 
    LEFT JOIN [DWH].[JIR].customfieldoption cfo8 ON (CF14.customfield = cfo8.customfield AND CF14.stringvalue=CAST(cfo8.id AS CHAR)) 
    LEFT JOIN dwh.jir.issuetype ist ON ist.ID = jis.issuetype
)
SELECT
    created,
    resolutiondate,
    IIF(cf_group_id LIKE 'IDR-%', 'SI', 'NO') AS [Massive],
    solved_in_first_go AS [Solved in first go],
    IIF(common_reitero_condition = 1, 'SI', 'NO') AS [Reitero CM],
    IIF(common_reitero_condition = 1 AND averia_motivo = prev_averia_motivo, 'SI', 'NO') AS [Reitero MM],
    averia_motivo AS [Motivo de la Averia],
    prev_averia_motivo AS [Motivo anterior de la Averia]
FROM JiraIssueWithLag
ORDER BY cf_group_id ASC;

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