参数化视图

创建带参数的视图，语法如下。

-- 执行定义视图SQL前提条件，先创建对应表（若存在请忽略）。
CREATE TABLE srcp (key STRING, value BIGINT, p STRING);

-- view with parameters
-- param @a -a table parameter
-- param @b -a string parameter
-- returns a table with schema (key string, value string)
CREATE VIEW IF NOT EXISTS pv1(@a TABLE (k STRING, v BIGINT), @b STRING)  
AS
SELECT srcp.key,srcp.value FROM srcp JOIN @a ON srcp.key=a.k AND srcp.p=@b;

语法说明：

• 因为定义了参数，所以定义参数化视图需要通过脚本模式操作。

• 创建的视图pv1有两个参数，即表参数和STRING参数，参数可以是任意的表或基本数据类型。

• 支持使用子查询作为参数的值，例如SELECT * FROM view_name( (SELECT 1 FROM src WHERE a > 0), 1);。

• 定义视图时，您可以为参数指定ANY类型，表示任意数据类型。例如CREATE VIEW paramed_view (@a any) AS SELECT * FROM src WHERE case WHEN @a is null then key1 else key2 end = key3;，定义了视图的第一个参数可以接受任意类型。

  但是ANY类型不能参与类似+、and需要明确类型才能执行的运算。ANY类型通常用在Table参数中作为PassThrough列，示例如下。

  -- 执行定义视图SQL前提条件，先创建对应表（若存在请忽略）。
  CREATE TABLE students (name STRING, id BIGINT, age BIGINT);
  
  -- 定义视图。
  CREATE VIEW paramed_view  (@a TABLE (name STRING, id ANY, age BIGINT)) 
  AS SELECT * FROM @a WHERE name = 'foo' AND age< 25;
  --调用示例。
  SELECT * FROM paramed_view ((SELECT name, id, age FROM students));

  说明

  使用create view创建视图后，可以执行desc命令获取视图的描述。此描述中包含视图的返回类型信息。

  视图的返回类型是在调用时重新推算的，它可能与创建视图时不一致，例如ANY类型。

• 定义视图时，Table的参数还支持使用星号（*）表示任意多个列。星号（*）可以指定数据类型，也可以使用ANY类型，示例如下。

  -- 执行定义视图SQL前提条件，先创建对应表（若存在请忽略）。
  CREATE TABLE school(name STRING, address STRING);
  CREATE TABLE student(name STRING, school STRING, age STRING, address STRING);
  
  -- 定义视图。
  CREATE VIEW paramed_view1 (@a TABLE(key STRING, * ANY), @b TABLE(key STRING, * STRING)) 
  AS SELECT a.* FROM @a JOIN @b ON a.key = b.key;
  --调用示例。
  SELECT name, address FROM paramed_view1 ((SELECT school, name, age, address FROM student), school) WHERE age < 20;

  示例中的视图接受两个表值参数。第一个表值参数第一列是STRING类型，后面可以是任意多个任意类型的列；第二个表值参数的第一列是STRING类型，后面可以是任意多个STRING类型的列。注意事项如下：

  • 变长部分必须写在表值参数定义语句的最后位置，即在星号（*）的后面不允许再出现其它列。因此，一个表值参数中最多只有一个变长列列表。

  • 由于变长部分必须写在表值参数定义语句的最后位置，有时输入表的列不一定是按照这种顺序排列的，这时需要重排输入表的列，可以以子查询作为参数（参考上述示例），子查询外面必须加一层括号。

  • 因为表值参数中变长部分没有名字，所以在视图定义过程中无法获得对这部分数据的引用，也无法对这些数据做运算。

  • 虽然无法对变长部分做运算，但可以使用select *这种通配符将变长部分的列传递出去。

  • 表值参数的列与定义视图时指定的定长列部分不一定会完全一致。如果名字不一致，编译器会自动做重命名；如果类型不一致，编译器会做隐式转换（不能隐式转换时，会发生报错）。

调用已经定义的pv1视图的示例如下。

-- 执行调用视图SQL前提条件，先创建对应表（若存在请忽略）。
CREATE TABLE src (key STRING, value BIGINT);
CREATE TABLE src2 (key STRING, value BIGINT);
CREATE TABLE src3 (key STRING, value BIGINT);

-- 调用示例。
@a := SELECT * FROM src WHERE value > 0;
--call view with table variable and scalar
@b := SELECT * FROM pv1(@a,'20170101');
@another_day := '20170102';
--call view with table name and scalar variable
@c := SELECT * FROM pv1(src2, @another_day);
@d := SELECT * FROM @c UNION ALL SELECT * FROM @b;
WITH 
t AS (SELECT * FROM src3)
SELECT * FROM @c 
UNION ALL
SELECT * FROM @d 
UNION ALL
SELECT * FROM pv1(t, @another_day);