如何引用列中的第一个非空字符串-cloudera impala/apache hive/spark sql

hk8txs48 于 2021-06-29 发布在 Hive

关注(0)|答案(3)|浏览(270)

我正在使用impalasql。我目前有一个包含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中这样做，但后来意识到没有类似的功能 IGNORE NULLS 在 Impala 实施。
这就是我在oracle中的想法（我意识到这只处理空值的前向填充）：

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

sql Hive impala apache-spark-sql oracle

来源：https://stackoverflow.com/questions/38152136/how-to-reference-first-non-null-string-in-a-column-cloudera-impala-apache-hi

3条答案

按热度按时间

3j86kqsm1#

我使用postgresql来测试查询，所以不能100%确定是否可以在您的系统中工作。 WITH 可以替换为子查询。还得把日期改成数字 ORDER BY 按计划工作。
枚举词：为有效词创建枚举列表。
createflag：设置一个标志，以便验证下一个组何时启动。
creategrp:使用标志和 SUM() 创建组。
最后，使用枚举列表加入组以分配 Fixed_Type 连接中的特殊条件 c.grp = 0 and e.rn =1 当第一排是 NULL 或者 'UNKNOWN' sql fiddle演示

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显示 Fixed_Type 从db上的值中键入，但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

赞(0）回复(0）举报 2021-06-29

o4hqfura2#

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

赞(0）回复(0）举报 2021-06-29

rn0zuynd3#

这是一个解决方案，类似于胡安卡洛斯的，使用解析函数 count 和一个 case 表达式一次创建组。
我创建了更多的输入数据来测试，例如，当一个帐户只有 null 和/或 'UNKNOWN' as type（确保左外连接按预期工作）。

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.

赞(0）回复(0）举报 2021-06-29

我来回答

如何引用列中的第一个非空字符串-cloudera impala/apache hive/spark sql

3条答案

相关问题

热门标签

最新问答