从前一个空值开始获取第一个非空值的滞后时间 [英] lag to get first non null value since the previous null value

问题描述

下面是我要在Redshift数据库中尝试实现的示例.

Below is an example of what I'm trying to achieve in a Redshift Database.

我有一个变量current_value，我想创建一个新列value_desired，即:

I have a variable current_value and I want to create a new column value_desired that is:

• 如果上一行为空，则与current_value相同
• 等于上一个非空值，如果前一行为非空

听起来很容易，但是我还没有找到一种方法.

It sounds like an easy task but I haven't found a way to do it yet.

row_numb     current_value   value_desired
1
2
3            47              47
4
5            45              45
6
7
8            42              42
9            41              42
10           40              42
11           39              42
12           38              42
13
14           36              36
15
16
17           33              33
18           32              33

我已经尝试过使用LAG()函数，但是我只能获取先前的值(而不是"non-null"块中的第一个)，这是我的看法:

I've tried with the LAG() function but I can only get the previous value (not the first in the "non-null" block), here is my take:

SELECT *
    , CASE WHEN current_value is not null and LAG(current_value) is null THEN current_value
           WHEN current_value is not null and LAG(current_value) is not null 
            THEN LAG(current_value)
      ELSE NULL END AS value_desired
  FROM test1

非常感谢您的帮助.

推荐答案

这是正确的答案，可以给出正确的结果. 这里有一些聪明的把戏，我建议您仔细看一下，然后让我知道需要澄清的地方

Here is the correct answer, which gives the right results. There are a few clever tricks here, i suggest you take a careful look through and let me know what needs clarifications

根据您的问题创建测试数据.

create test data, as per your question.

drop table if exists test_table ;
create table test_table (row_num int,current_value int);
insert into test_table(row_num, current_value)
values
  (1,null),
(2,null),
(3,47),
(4,null),
(5,45),
(6,null),
(7,null),
(8 ,42),
(9 ,41),
(10,40  ),
(11,39 ),
(12,38 ),
(13,null),
(14,36),
(15,null),
(16,null),
(17 ,33),
(18,32  )
;

然后运行此代码

SELECT DISTINCT
  j1.row_num,
  CASE WHEN j1.current_value IS NULL
    THEN NULL
  ELSE
    last_value(j2.current_value)
    OVER (
      PARTITION BY j1.row_num
      ORDER BY j2.row_num
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) END AS value_desired
FROM test_table AS j1
  LEFT JOIN (SELECT
               row_num,
               current_value,
               lag(current_value, 1)
               OVER (
                 ORDER BY row_num ) AS prev_cval
             FROM test_table) AS j2
    ON j1.row_num >= j2.row_num AND j2.current_value IS NOT NULL
       AND j2.prev_cval IS NULL
ORDER BY j1.row_num;

这篇关于从前一个空值开始获取第一个非空值的滞后时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！