mysql - 根据条件在 Spark SQL 或 MySQL 中生成新列

Question

创建表:

CREATE TABLE temp (
name varchar(20), 
dep varchar(20));

INSERT INTO temp VALUES 
('a', null), 
('b', null), 
('c', 'b'), 
('d', 'c'), 
('e', 'b'), 
('e', 'd');

我需要社区的帮助来编写生成新列的查询，假设 xyz，如果 dep 为 null，则该列的值为 1。否则它必须为相应的 name 取 dep 并将 1 添加到 xyz 列值。

例如:这里c依赖于b，所以它必须取b<的xyz/strong> 为 1 并向其加 1，这使 c 的 xyz 值为 2，依此类推。

输出:

+------+------+-----+
| name |  dep | xyz |
+------+------+-----+
|  a   | null |  1  |
|  b   | null |  1  |
|  c   |  b   |  2  |
|  d   |  c   |  3  |
|  e   |  b   |  2  |
|  e   |  d   |  4  |
+------+------+-----+

创建表:

create table temp1(name varchar(20), dependency varchar(20));
insert into temp1 values
    ('city', null), ('state', null), ('country', 'city'),
    ('country','state'), ('pin','country'), ('pin','state'),
    ('continent','country'), ('continent','pin'), ('continent','city');

预期输出:这里的序列是要生成的新列。

| name     | dependency | sequence |
|----------|------------|----------|
| city     | null       | 1        |
| state    | null       | 1        |
| country  | city       | 2        |
| country  | state      | 2        |
| pin      | country    | 3        |
| pin      | state      | 2        |
| continent| country    | 3        |
| continent| pin        | 4        |
| continent| city       | 2        |

我向社区提出的第一个问题 :)提前谢谢大家。

Answer 1

试试看Demo在 My SQL 8.0

中

select
  name,
  dep,
  dense_rank() over (order by dep) as xyz
from myTable
order by
  name, dep

输出:

+--------------+
name  dep   xyz
+--------------+
 a  (null)   1
 b  (null)   1
 c    b      2
 d    c      3
 e    b      2
 e    d      4

对于你的第二个问题，你可以通过简单的case语句来实现，如下所示

select
    name,
    type,
    case
    when 
        (name = 'country' and type = 'city') 
        OR (name = 'continent' and type = 'city')
        OR (name = 'pin' and type = 'state')
        OR (name = 'country' and type = 'state') 
    then
        2
    when
        (name = 'pin' and type = 'country')
        OR (name = 'continent' and type = 'country')
    then
        3
    when
        (name = 'continent' and type = 'pin')
    then
        4
    else
        1
    end as ranks
from myTable

输出:

+--------------------------+
name        type      ranks
+--------------------------+
city        null        1
state       null        1
country     city        2
country     state       2
pin         country     3
pin         state       2
continent   country     3
continent   pin         4
continent   city        2