代码之家 › 专栏 › 技术社区 › Daniel V

mariadb中lag函数的默认值

mariadb

Daniel V · 技术社区 · 6 年前

我正在尝试构建一个视图,它允许我跟踪连续两个月的工资值之间的差异。然而,当一个数字丢失时,这将是因为它是第一个条目,因此支付的金额为0。目前,我正在使用下面的数字来表示自 [,default] mariadb中没有实现参数。

CASE WHEN (
    NOT(policy_agent_month.policy_agent_month_id IS NOT NULL        
    AND LAG(days_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id ) IS NULL)) THEN        
         LAG(days_paid, 1) OVER ( PARTITION BY claim_id ORDER BY month_id)        
    ELSE 
         0        
    END

我遇到的问题是,我有大约30个变量需要应用这个函数,它使我的代码不可读,而且非常笨拙。有更好的解决方案吗?

2 回复 | 直到 6 年前

The Impaler 6 年前

可以使用Mariadb 10.2+中的CTE(公用表表达式)预先计算常用表达式,并将其命名以供以后使用:

with
x as ( -- first we compute the CTE that we name "x"
  select
    *,
    coalesce(
      LAG(days_paid, 1) OVER (PARTITION BY claim_id ORDER BY month_id), 
      123456
    ) as prev_month -- this expression gets the name "prev_month"
  from my_table -- or a simple/complex join here
)
select -- now the main query
  prev_month
from x
... -- rest of your query here where "prev_month" is computed.

在主查询中 prev_month 具有滞后值或默认值 123456 当它为空时。

Rick James diyism 6 年前

为什么使用 WITH ?

SELECT province, tot_pop,
       tot_pop - COALESCE(
              (LAG(tot_pop) OVER (ORDER BY tot_pop ASC)),
                        0) AS delta
    FROM provinces
    ORDER BY tot_pop asc;

+---------------------------+----------+---------+
| province                  | tot_pop  | delta   |
+---------------------------+----------+---------+
| Nunavut                   |    14585 |   14585 |
| Yukon                     |    21304 |    6719 |
| Northwest Territories     |    24571 |    3267 |
| Prince Edward Island      |    63071 |   38500 |
| Newfoundland and Labrador |   100761 |   37690 |
| New Brunswick             |   332715 |  231954 |
| Nova Scotia               |   471284 |  138569 |
| Saskatchewan              |   622467 |  151183 |
| Manitoba                  |   772672 |  150205 |
| Alberta                   |  2481213 | 1708541 |
| British Columbia          |  3287519 |  806306 |
| Quebec                    |  5321098 | 2033579 |
| Ontario                   | 10071458 | 4750360 |
+---------------------------+----------+---------+
13 rows in set (0.00 sec)

但是,它并不便宜(至少在MySQL8.0中是这样); 这张桌子还有13行

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';
MySQL 8.0:
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_rnd           | 89    |
| Handler_read_rnd_next      | 52    |
| Handler_write              | 26    |
  (and others)

MariaDB 10.3:
| Handler_read_rnd           | 77    |
| Handler_read_rnd_next      | 42    |
| Handler_tmp_write          | 13    |
| Handler_update             | 13    |