UNION ALL语句

本页目录

• 语法
• 示例一
• 示例二

语法

1. select_statement
2. UNION ALL
3. select_statement;

UNION ALL 语句将两个流合并。要求两个流的字段完全一致，包括字段类型、字段顺序。

注意: 实时计算同样支持 UNION 函数。UNION ALL 允许重复值，UNION 不允许重复值。在Flink 底层，UNION 是 UNION ALL + Distinct , 因此 UNION 运行效率比较低，一般不推荐使用 UNION。

示例一

1. SELECT *
2. FROM (
3. (SELECT user FROM 表名 WHERE a % 2 = 0)
4. UNION ALL
5. (SELECT user FROM 表名 WHERE b = 0)
6. );

示例二

测试数据

test_source_union1：

a(varchar)	b(bigint)	c(bigint)
test1	1	10

test_source_union2：

a(varchar)	b(bigint)	c(bigint)
test1	1	10
test2	2	20

test_source_union3：

a(varchar)	b(bigint)	c(bigint)
test1	1	10
test2	2	20
test1	1	10

测试语句

1. create table test_source_union1 (
2. a varchar,
3. b bigint,
4. c bigint
5. ) WITH (
6. type='datahub',
7. endpoint='',
8. accessId='',
9. accessKey='',
10. projectName='',
11. topic='',
12. project=''
13. );
15. create table test_source_union2 (
16. a varchar,
17. b bigint,
18. c bigint
19. ) WITH (
20. type='datahub',
21. endpoint='',
22. accessId='',
23. accessKey='',
24. projectName='',
25. topic='',
26. project=''
27. );
29. create table test_source_union3 (
30. a varchar,
31. b bigint,
32. c bigint
33. ) WITH (
34. type='datahub',
35. endpoint='',
36. accessId='',
37. accessKey='',
38. projectName='',
39. topic='',
40. project=''
41. );
43. create table test_result_union (
44. d VARCHAR,
45. e BIGINT,
46. f BIGINT,
47. primary key(d)
48. ) WITH (
49. type='rds',
50. url='',
51. username='',
52. password='',
53. tableName=''
54. );
55. INSERT into test_result_union
57. SELECT
58. a,
59. sum(b),
60. sum(c)
61. FROM
62. (SELECT * from test_source_union1
63. UNION ALL
64. SELECT * from test_source_union2
65. UNION ALL
66. SELECT * from test_source_union3
67. )t
68. GROUP BY a;

测试结果

d(varchar)	e(bigint)	f(bigint)
test1	1	10
test2	2	20
test1	2	20
test1	3	30
test2	4	40
test1	4	40

本文转自实时计算—— UNION ALL语句