如何在列中引用第一个非空字符串- Cloudera Impala / Apache Hive / Spark SQL

我正在使用Impala SQL。我目前有一个数据库，有3列:Account, Date, Type。

在Type下，有各种描述相关类型的数据字符串，但有些等于'UNKNOWN'，有些等于null。

我想创建另一个列Fixed_Type。Fixed_Type中的值应该来自Type列。

如果Type中的值是null或'UNKNOWN'，它应该获得Type列中的最后一个有效值，按帐户和日期划分。
如果分区以null或'UNKNOWN'开头，则Fixed_Type中的值应该是Type中的第一个有效值。

例如:

Account | Date | Type   |  Fixed_Type
1         Jan     data1     data1
1         Feb   'UNKNOWN'   data1
1         Mar     null      data1
2         Apr     data2     data2
2         May     null      data2
2         Jun     null      data2
2         Jul     data3     data3
3         Feb   'UNKNOWN'   data4
3         Mar   'UNKNOWN'   data4
3         Apr     data4     data4

我开始在Oracle中这样做，但后来意识到在Impala中没有类似IGNORE NULLS的功能。

这就是我想在Oracle中做的(我意识到这只处理null的前向填充):

select account, date, type, 
       case when type is null 
            then last_value(type ignore nulls)
                 over (partition by account order by date) 
            else type 
       end as fixed_type

我使用postgresql来测试查询，所以不能100%确定是否可以使它在您的系统中工作。WITH可以用子查询代替。还必须将日期更改为数字，以便ORDER BY按预期工作。

enumerateWords:创建有效单词的枚举列表。
createFlag:设置一个标志，以便您可以验证下一个组何时开始。
createGrp:使用标志和SUM()创建组。
最后通过枚举列表加入组，分配Fixed_Type
- 在JOIN c.grp = 0 and e.rn =1中的特殊条件，当第一行是NULL或'UNKNOWN'

Sql Fiddle Demo

WITH enumerateWords as (
    SELECT "Account", "Date", "Type",
           row_number() over (partition by "Account"
                              order by "Date") rn
    FROM Days  
    WHERE "Type" <> '''UNKNOWN''' AND "Type" IS NOT NULL   
),  createFlag as (
  SELECT *, CASE WHEN "Type" = '''UNKNOWN''' OR "Type" IS NULL 
                 THEN 0
                 ELSE 1
             END as FLAG       
  FROM Days  
), createGrp as ( 
  SELECT *,
         SUM(FLAG) OVER (PARTITION BY "Account" 
                         ORDER BY "Date") as grp         
  FROM createFlag  
)
SELECT c.*, e."Account", e."Date", e."Type" as "Fixed_Type"
FROM createGrp c
JOIN enumerateWords e
  ON c."Account" = e."Account"
 AND (     c.grp = e.rn   
       OR (c.grp = 0 and e.rn = 1)
     )

你可以看到createGrp从DB上的值显示Fixed_Type类型，但是enumerateWords从Type创建它。

你可以看到flag和grp是如何一起工作来检测变化的

|                      createGrp                       ||      enumerateWords       |
|---------|------|-----------|------------|------|-----||---------|----|------------|
| Account | Date |      Type | Fixed_Type | flag | grp || Account | rn | Fixed_Type |
|---------|------|-----------|------------|------|-----||---------|----|------------|
|       1 |    1 |     data1 |      data1 |    1 |   1 ||       1 |  1 |      data1 |
|       1 |    2 | 'UNKNOWN' |      data1 |    0 |   1 ||       1 |  1 |      data1 |
|       1 |    3 |    (null) |      data1 |    0 |   1 ||       1 |  1 |      data1 |
|---------|------|-----------|------------|------|-----||---------|----|------------|
|       2 |    4 |     data2 |      data2 |    1 |   1 ||       2 |  1 |      data2 |
|       2 |    5 |    (null) |      data2 |    0 |   1 ||       2 |  1 |      data2 |
|       2 |    6 |    (null) |      data2 |    0 |   1 ||       2 |  1 |      data2 |
|       2 |    7 |     data3 |      data3 |    1 |   2 ||       2 |  2 |      data3 |
|       2 |    8 |    (null) |      data3 |    0 |   2 ||       2 |  2 |      data3 |
|---------|------|-----------|------------|------|-----||---------|----|------------|
|       3 |    9 | 'UNKNOWN' |      data4 |    0 |   0 ||       3 |  1 |      data4 | <= 
|       3 |   10 | 'UNKNOWN' |      data4 |    0 |   0 ||       3 |  1 |      data4 | <= 
|       3 |   11 |     data4 |      data4 |    1 |   1 ||       3 |  1 |      data4 |
                                                                    ^^^ special case 0 = 1

Oracle安装:

CREATE TABLE Table_Name ( Acct, Dt, Type ) AS
SELECT 1, DATE '2016-01-01', 'Data1'   FROM DUAL UNION ALL
SELECT 1, DATE '2016-02-01', 'UNKNOWN' FROM DUAL UNION ALL
SELECT 1, DATE '2016-03-01', NULL      FROM DUAL UNION ALL
SELECT 2, DATE '2016-04-01', 'Data2'   FROM DUAL UNION ALL
SELECT 2, DATE '2016-05-01', NULL      FROM DUAL UNION ALL
SELECT 2, DATE '2016-06-01', NULL      FROM DUAL UNION ALL
SELECT 2, DATE '2016-07-01', 'Data3'   FROM DUAL UNION ALL
SELECT 3, DATE '2016-02-01', 'UNKNOWN' FROM DUAL UNION ALL
SELECT 3, DATE '2016-03-01', 'UNKNOWN' FROM DUAL UNION ALL
SELECT 3, DATE '2016-04-01', 'Data4'   FROM DUAL;

查询

SELECT Acct,
       Dt,
       Type,
       Fixed_Type
FROM   (
  SELECT r.Acct,
         r.Dt,
         r.Type,
         t.type AS fixed_type,
         ROW_NUMBER() OVER ( PARTITION BY r.Acct, r.dt
                             ORDER BY SIGN( ABS( t.dt - r.dt ) ),
                                      SIGN( t.dt - r.dt ),
                                      ABS( t.dt - r.dt ) ) AS rn
  FROM   table_name r
         LEFT OUTER JOIN
         table_name t
         ON (    r.acct = t.acct
             AND t.type IS NOT NULL
             AND t.type <> 'UNKNOWN' )
)
WHERE   rn = 1
ORDER BY acct, dt;

如果将表本身连接起来，使两个表具有相同的帐户编号，则可以将每个帐户的每一行与同一帐户中的所有其他行进行比较。但是，我们对比较所有行不感兴趣，而只对NULL或'UNKNOWN'以外的行感兴趣，因此我们得到了连接条件:

ON (    r.acct = t.acct
    AND t.type IS NOT NULL
    AND t.type <> 'UNKNOWN' )

使用LEFT OUTER JOIN只是在有一个帐号的类型具有所有NULL或'UNKNOWN'值的情况下，以便不排除行。

然后就是找到最近的行了。在Oracle中，如果你从一个日期减去另一个日期，那么你得到的天数(或天数的分数)差-所以:

SIGN( ABS( t.dt - r.dt ) )如果两个日期相同则给出0，如果两个日期不同则给出1。按此顺序排序意味着，如果有一个值具有相同的日期，那么它将优先于不相同的日期;
SIGN( t.dt - r.dt )将返回0，如果两个日期是相同的(但在前面的语句中已经被过滤)或-1，如果比较的日期在当前行之前或+1，如果它在之后-这是用来更喜欢之前的日期而不是之后的日期。
ABS( t.dt - r.dt )将按最接近的日期排序。

所以ORDER BY子句有效地声明:ORDER BY相同的日期首先，然后日期之前(最接近r.dt)，最后日期之后(最接近r.dt)。

然后，所有这些都放在一个内联视图中，并过滤以获得每行(WHERE rn = 1)的最佳匹配。

      ACCT DT                  TYPE    FIXED_TYPE
---------- ------------------- ------- ----------
         1 2016-01-01 00:00:00 Data1   Data1      
         1 2016-02-01 00:00:00 UNKNOWN Data1      
         1 2016-03-01 00:00:00         Data1      
         2 2016-04-01 00:00:00 Data2   Data2      
         2 2016-05-01 00:00:00         Data2      
         2 2016-06-01 00:00:00         Data2      
         2 2016-07-01 00:00:00 Data3   Data3      
         3 2016-02-01 00:00:00 UNKNOWN Data4      
         3 2016-03-01 00:00:00 UNKNOWN Data4      
         3 2016-04-01 00:00:00 Data4   Data4

这是一个类似于Juan Carlos的解决方案，使用解析函数count和case表达式一次创建组。

我创建了更多的输入数据来测试，例如，当帐户只有null和/或'UNKNOWN'作为类型时会发生什么(确保左外连接按预期工作)。

create table table_name ( acct, dt, type ) as
select 1, date '2016-01-01', 'Data1'   from dual union all
select 1, date '2016-02-01', 'UNKNOWN' from dual union all
select 1, date '2016-03-01', null      from dual union all
select 2, date '2016-04-01', 'Data2'   from dual union all
select 2, date '2016-05-01', null      from dual union all
select 2, date '2016-06-01', null      from dual union all
select 2, date '2016-07-01', 'Data3'   from dual union all
select 3, date '2016-02-01', 'UNKNOWN' from dual union all
select 3, date '2016-03-01', 'UNKNOWN' from dual union all
select 3, date '2016-04-01', 'Data4'   from dual union all
select 3, date '2016-05-01', 'UNKNOWN' from dual union all
select 3, date '2016-06-01', 'Data5'   from dual union all
select 4, date '2016-02-01', null      from dual union all
select 4, date '2016-03-01', 'UNKNOWN' from dual;
SQL> select * from table_name;
      ACCT DT         TYPE
---------- ---------- -------
         1 2016-01-01 Data1
         1 2016-02-01 UNKNOWN
         1 2016-03-01
         2 2016-04-01 Data2
         2 2016-05-01
         2 2016-06-01
         2 2016-07-01 Data3
         3 2016-02-01 UNKNOWN
         3 2016-03-01 UNKNOWN
         3 2016-04-01 Data4
         3 2016-05-01 UNKNOWN
         3 2016-06-01 Data5
         4 2016-02-01
         4 2016-03-01 UNKNOWN
14 rows selected.

查询

with
     prep(acct, dt, type, gp) as (
       select acct, dt, type, 
              count(case when type != 'UNKNOWN' then 1 end)
                             over (partition by acct order by dt)
       from   table_name
     ),
     no_nulls(acct, type, gp) as (
       select acct, type, gp
       from   prep
       where  type != 'UNKNOWN'
     )
select p.acct, p.dt, p.type, n.type as fixed_type
from   prep p left outer join no_nulls n
on     p.acct = n.acct and (p.gp = n.gp or p.gp = 0 and n.gp = 1)
order by acct, dt;

      ACCT DT         TYPE    FIXED_TYPE
---------- ---------- ------- ----------
         1 2016-01-01 Data1   Data1
         1 2016-02-01 UNKNOWN Data1
         1 2016-03-01         Data1
         2 2016-04-01 Data2   Data2
         2 2016-05-01         Data2
         2 2016-06-01         Data2
         2 2016-07-01 Data3   Data3
         3 2016-02-01 UNKNOWN Data4
         3 2016-03-01 UNKNOWN Data4
         3 2016-04-01 Data4   Data4
         3 2016-05-01 UNKNOWN Data4
         3 2016-06-01 Data5   Data5
         4 2016-02-01
         4 2016-03-01 UNKNOWN
14 rows selected.

相关内容

最新更新

热门标签：