mysql - 将函数应用于 View 中的多列

Question

我有一个 MYSQL View ，其中包含以下内容:

SELECT
    claim_month_id,
    claim.claim_id,
    month.month_id,
    paid_to_date - CASE WHEN (end_date > reported_to_insurer_date) 
    THEN LAG( paid_to_date, 1) OVER ( PARTITION BY claim_id ORDER BY month_id ) 
    ELSE 0 END AS paid_change,
    days_paid - CASE WHEN (end_date > reported_to_insurer_date) 
    THEN LAG( days_paid, 1) OVER ( PARTITION BY claim_id ORDER BY month_id ) 
    ELSE 0 END AS days_paid_change,
    wks_paid - CASE WHEN (end_date > reported_to_insurer_date) 
    THEN LAG( wks_paid, 1) OVER ( PARTITION BY claim_id ORDER BY month_id ) 
    ELSE 0 END AS wks_paid_change...

最后几行在一系列列中重复；有多个数字列，每个数字列都应用了相同的函数。是否有某种方法可以简化 SQL，使其更易于阅读？它甚至更有用，因为我决定要更改有关算法的一些细节，现在必须在每个领域完成这些细节，而不仅仅是更改源代码。

我的愿景是这样的

SELECT
    claim_month_id,
    claim.claim_id,
    month.month_id,
    VIEWDIFF(paid_to_date) AS paid_to_date_change,
    VIEWDIFF(days_paid) AS days_paid_change,
    VIEWDIFF(wks_paid) AS wks_paid_change

通过某种方式定义 VIEWDIFF 函数。

我的背景是函数式编程，所以我正在努力回到母舰。我知道这是一个愚蠢的问题，但我似乎无法在这里找到正确的搜索词来找到答案。

Answer 1

我假设您使用的是 MySQL 8x

可以形成一个“派生表”，并通过它获得通过别名引用计算列的能力:例如

select 
    *
  , paid_to_date - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_paid_to_date
        ELSE 0
    END AS paid_change
  , days_paid - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_days_paid
        ELSE 0
    END AS days_paid_change
  , wks_paid - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_wks_paid
        ELSE 0
    END AS wks_paid_change
FROM (
    SELECT
          *
        , LAG(paid_to_date, 1) OVER (PARTITION BY claim_id ORDER BY month_id) AS lag_paid_to_date
        , LAG(days_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id)    AS lag_days_paid
        , LAG(wks_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id)     AS lag_wks_paid
    FROM x
    ) d

或者，您可以使用公用表表达式 (cte)，这使得它看起来更像逻辑术语中的“串行”，例如

WITH cte AS (
    SELECT
          *
        , LAG(paid_to_date, 1) OVER (PARTITION BY claim_id ORDER BY month_id) AS lag_paid_to_date
        , LAG(days_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id)    AS lag_days_paid
        , LAG(wks_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id)     AS lag_wks_paid
    FROM x
)

SELECT 
    *
  , paid_to_date - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_paid_to_date
        ELSE 0
    END AS paid_change
  , days_paid - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_days_paid
        ELSE 0
    END AS days_paid_change
  , wks_paid - CASE
        WHEN end_date > reported_to_insurer_date THEN lag_wks_paid
        ELSE 0
    END AS wks_paid_change
FROM cte

我个人更喜欢将 cte 用于特定目的，例如递归，但它越来越多地用于代替简单的派生表。