插入和更新序列

使用 SQL 插入和更新序列

可以通过 SQL 以四种不同的方式插入或更新序列：

1. 作为一个标量值：

    INSERT OR UPDATE INTO t VALUES (1, 1);

2. 作为一个数组：

    INSERT OR UPDATE INTO t VALUES (1, [1,2,3]);

3. 作为字符串：

    INSERT OR UPDATE INTO t VALUES (1, '{1,2,3}');

4. 作为子查询的结果：

    INSERT OR UPDATE INTO t SELECT * FROM tt;

在 SmartESQL 7.1 版本之前的版本中，当使用方法 1 至 3 时，序列会被追加，而在第 4 种方法（子查询）中，序列会被覆盖。从 7.1 版本开始，这种行为已发生改变，并通过 append_mode 参数进行控制：如果 append_mode == true，则序列会被追加（这是默认行为），否则会被覆盖。append_mode 参数通过 SQL 的 set 命令进行修改：

    set append_mode true

INSERT OR UPDATE 语句不仅支持在序列内的任意位置插入元素（SmartESQL 支持对序列进行无序修改），还特别适用于填补有序序列中的“空缺”。例如，当时间序列中某些记录缺失时，您可以使用此功能稍后补充这些记录。不过，在使用该功能时，请注意以下几点：

• 请确保将附加模式参数设置为“真”，否则新数据可能会覆盖现有序列；
• 表的第一个字段（或多个字段）需要是主键；
• 第一个序列必须保持有序；
• 插入一系列元素时，请确保它们不会与现有元素重叠；
• 如果多个范围与现有元素重叠，则无法通过单个查询完成插入；
• 元素只能从另一个中间表插入。

此外，应用程序需要确保序列的一致性，因为 SmartESQL 仅在明确定义排序的情况下强制约束，其他情况下不会自动处理一致性问题。例如，对于“时间戳”、“买入价”和“卖出价”这三个序列，应用程序应确保在需要时正确更新所有三个序列。

seq_remove() 函数可用于移除序列中的元素。它接受一个参数，即要移除的元素投影，并返回实际移除的元素数量。

在序列中插入和移除元素示例

步骤 1：创建并填充“Quote”表

创建并填充名为“Quote”的表，其中包含日期（day）、开盘价（open）和收盘价（close）序列，以股票代码（sym）作为索引。根据上述要求，表中的第一个字段（sym）是主键，其他字段是序列；第一个序列（day）是有序的（升序）。请注意，序列数据在 20170505 和 20170509 之间存在空缺，我们将在步骤 2 中将其补充完整。另外，请注意参数 append_mode 明确设置为 true。

    set append_mode true;
    create table Quotes(sym string primary key,
        day sequence(int asc),
        open sequence(int),
        close sequence(int));
    insert into Quotes values('AAA',
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
        [101, 102, 103, 104, 105, 109, 110],
        [111, 112, 113, 114, 115, 119, 120]);
    insert into Quotes values('BBB',
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
        [201, 202, 203, 204, 205, 209, 210],
        [211, 212, 213, 214, 215, 219, 220]);
    insert into Quotes values('CCC',
        [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
        [301, 302, 303, 304, 305, 309, 310],
        [311, 312, 313, 314, 315, 319, 320]);

步骤 2：创建临时表 QuoteHorizontal

创建一个临时表 QuoteHorizontal，其中包含缺失的元素，并使用带有 order by 子句的 insert 或 update 语句从 QuoteHorizontal 表填充 Quotes 表（请注意，order by day 子句是按正确顺序排列元素所必需的）：

    create table QuotesHorizontal(sym string , day int, open int, close int);
    insert into QuotesHorizontal values('AAA', 20170506, 106, 116),
        ('BBB', 20170506, 206, 216), ('CCC', 20170506, 306, 316);
    insert into QuotesHorizontal values('AAA', 20170507, 107, 117),
        ('BBB', 20170507, 207, 217), ('CCC', 20170507, 307, 317);
    insert into QuotesHorizontal values('AAA', 20170508, 108, 118),
        ('BBB', 20170508, 208, 218), ('CCC', 20170508, 308, 318);
    insert or update into Quotes select * from QuotesHorizontal order by day;

步骤3：从序列中删除一些数据

此时序列数据中没有空缺。我们将使用 seq_remove() 函数从序列中删除一些数据，例如从 5 月 1 日到 5 月 3 日的数据。请注意，需要 for update 子句才能修改序列（有关 for update 子句的更多详细信息，请参阅 SQL DML Select 页面）：

    select !seq_search(day, 20170501, 20170503) as daterange,
        seq_remove(day@daterange), seq_remove(open@daterange),
        seq_remove(close@daterange)
    from Quotes for update;

请注意，操作符“!” 是 seq_ignore() 的快捷方式。被忽略的列可以在其他计算列中使用，但不会出现在实际的语句输出中。

序列的并行处理

SQL 引擎通过线程池实现了对序列的并行处理，从而在多处理器环境中显著提升性能。线程池能够将序列任务合理分配到各个线程中，并让这些线程同时进行计算，有效缩短了总执行时间。

您可以通过设置环境变量 MCO_CPU_NUMBER 来调整线程池的大小。如果没有设置该变量，系统会自动使用 CPU 核心的总数作为线程池的大小。如果您的硬件支持超线程（HT），大多数现代 CPU 都具备这一功能，那么线程数将等于 HT 核心的数量（通常每个物理核心有两个线程）。例如，如果您的机器有 4 个实际核心，线程数就会是 8。不过，请注意，有时过多的线程反而可能影响性能。

在 xSQL 配置文件中，您可以使用参数 sql_use_connection_pool 来选择是否启用线程池。默认情况下，此参数设置为 true，即在启动时会自动创建线程池。如果您希望禁用线程池，可以将此参数设置为 false。

    sql_use_connection_pool:false

在运行时，可以使用函数 seq_parallelism() 来限制用于运行查询的线程数量。该函数接受要使用的线程数作为参数，并返回线程池中的线程总数。

    seq_parallelism(<n_threads>)

将 n_threads 的值设为 1 实际上会关闭线程池处理。例如，以下命令将线程池大小设置为使用 4 个 CPU 内核：

    XSQL> SELECT seq_parallelism(4);

请注意，函数 seq_parallelism() 影响整个服务器，而不仅仅是单个客户端会话。因此，不应从同时运行的不同客户端执行 seq_parallelism()。另外，如果由函数 seq_parallelism() 请求的并行线程数超过了环境变量 MCO_CPU_NUMBER 中指定的池大小（默认值为可用核心数），SQL 引擎将使用 MCO_CPU_NUMBER 中指定的数值。

关于Windows上线程池管理的说明

序列的并行处理通过 ThreadPool 类实现，该类实例化了一个全局变量 ThreadPool。当程序退出 main() 函数时，ThreadPool 的析构函数会自动调用，释放线程池相关的资源（内存、同步原语和任务）。然而，在 Windows C++ 运行时中，应用程序退出时会先停止所有非主线程，再调用全局对象的析构函数。这导致 ThreadPool 的任务在析构函数调用前已被停止。

为了解决这个问题，当前的方案是在 Windows 上不调用 ThreadPool 的析构函数。但这可能导致资源泄漏，特别是在 xSQL 服务器（或 .NET Framework 或 JNI 应用）动态加载数据库 DLL（如 mcoxsql.dll）且多次重新加载的情况下。每次重新加载都会产生新的资源泄漏。

更可靠的解决方案将在未来的版本中推出，目前 Windows 开发人员应留意这一潜在问题。

序列中的空值

空值可以插入到序列中或添加到序列末尾。例如：

    XSQL>set append_mode true;
    XSQL>create table hor(i integer, f float);
    XSQL>insert into hor values(1, 100.0);
    XSQL>insert into hor values(1, NULL);
    XSQL>insert into hor values(1, 300.0);
    XSQL>insert into hor values(1, NULL);
    XSQL>create table ver(i integer primary key, f sequence(float));
    XSQL>insert or update into ver select * from hor;

现在表 ver 中的序列字段 f 具有预期的值：

    XSQL>select * from ver;
    i	f
    ----------------------------------------------------------------
    1	{100, null, 300, null}

使用“FLATTENED”关键字，该序列可以按如下方式显示：

    XSQL>select flattened * from ver;
    i	f
    ----------------------------------------------------------------
    1	100
    1	null
    1	300
    1	null

请注意，不允许向有序序列中插入空值，这是预期的行为。如果尝试这样做，SQL 编译器将发出错误。例如：

    XSQL>insert into ver values(1, [1,2,3,null,5]);
    ERROR: Sequence item is out of order

序列函数中的空值

在有意义的情况下，序列函数支持空值。例如，使用上面定义的名为 ver 的表以及相同的插入值：

    XSQL>select seq_last(f) from ver where i=1;
    #1
    ----------------------------------------------------------------
    null
 
    XSQL>select seq_first(f) from ver where i=1;
    #1
    ----------------------------------------------------------------
    100

以下 SQL 函数（及其 xSQL 对应函数）不支持包含空值的序列，因此如果在包含空值的序列上调用，将返回 MCO_E_SEQ_NULL_VALUE 错误：

SQL	xSQL
mco_seq_window_agg*()	seq_window_agg*()
mco_seq_group_agg*()	seq_group_agg*()
mco_seq_diff()	seq_diff()
mco_seq_map()	seq_map()
mco_seq_union()	seq_union()
mco_seq_cross()	seq_cross()
mco_seq_extrema()	seq_extrema()
mco_seq_stretch*()	seq_stretch*()
mco_seq_asof_join()	seq_asof_join()

使用数组语法插入空值

在将空值插入序列时，存在一种可能令人困惑的用例：当使用“INSERT OR UPDATE”子句插入单个空值字面量时，它会将序列字段的值设置为空值，而不是将空值添加到序列中。例如：

    XSQL>set append_mode true;
    XSQL>create table ver(i integer primary key, f sequence(float));
    XSQL>insert or update into ver values(1, [1,2,3]);
    XSQL>insert or update into ver values(1, null);
    XSQL>select * from ver;
 
    i	f
    ----------------------------------------------------------------
    1	null

如果需要将空值添加到序列中，则必须使用数组语法。例如：

    XSQL>set append_mode true;
    XSQL>create table ver(i integer primary key, f sequence(float));
    XSQL>insert or update into ver values(1, [1,2,3]);
    XSQL>insert or update into ver values(1, [null]);
    XSQL>select * from ver;
     
    i	f
    ----------------------------------------------------------------
    1	{1, 2, 3, null}

如果初始序列为空，后一种语法将生成一个包含单个空元素的序列。例如：

    XSQL>set append_mode true;
    XSQL>create table ver(i integer primary key, f sequence(float));
    XSQL>insert or update into ver values(1, [null]);
    XSQL>select * from ver;
    i	f
    ----------------------------------------------------------------
    1	{null}

请注意这与第一个案例的不同之处。第一个案例将序列字段的值设置为 null，而使用数组语法则会得到一个包含一个 null 元素的序列。