【SQL】Spark SQL 比较上下两条数据，多个字段多个条件，赋值一个结果字段成一个array，

需求

比较上下两条数据，多个字段多个条件，赋值一个结果字段成一个array，

• 满足c1从 2变成1 返回[a],
• 满足c2 2变成1 返回[b]
• 同时满足返回 [a,b]
  这个只是例子，条件还很多，总共有三百多个可能

解决：

WITH tmp AS (
    SELECT  1 AS id,
            1 AS c1,
            1 AS c2
    UNION ALL
    SELECT  2 AS id,
            2 AS c1,
            2 AS c2
    UNION ALL
    SELECT  3 AS id,
            1 AS c1,
            2 AS c2
    UNION ALL
    SELECT  4 AS id,
            2 AS c1,
            1 AS c2
    UNION ALL
    SELECT  5 AS id,
            2 AS c1,
            2 AS c2
),
r1 AS (
    SELECT  row_num,
            ARRAY(
                CASE WHEN (c1 = 2 AND prev_c1 = 1) THEN 'a'
                     ELSE NULL
                END,
                CASE WHEN (c2 = 2 AND prev_c2 = 1) THEN 'b'
                     ELSE NULL
                END
            ) AS event
    FROM    (
                SELECT  c1,
                        c2,
                        ROW_NUMBER() OVER (
                            ORDER BY
                                    id
                        ) AS row_num,
                        LAG(c1) OVER (
                            ORDER BY
                                    id
                        ) AS prev_c1,
                        LAG(c2) OVER (
                            ORDER BY
                                    id
                        ) AS prev_c2
                FROM    tmp
            ) ranked_data
),
r2 AS (
    SELECT  row_num,
            exploded_event
    FROM    r1
    LATERAL VIEW
            EXPLODE(event) exploded AS exploded_event
)
 
 SELECT * from r2 where exploded_event is not null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58