不相关场存在的交叉表分裂结果

Question

我使用Postgres9.1和tablefunc:交叉表

我有一张桌子，其结构如下：

CREATE TABLE marketdata.instrument_data
(
  dt date NOT NULL,
  instrument text NOT NULL,
  field text NOT NULL,
  value numeric,
  CONSTRAINT instrument_data_pk PRIMARY KEY (dt , instrument , field )
)

这是由一个每天获取数据的脚本填充的。所以看起来是这样的：

| dt         | instrument        | field     | value |
|------------+-------------------+-----------+-------|
| 2014-05-23 | SGX.MiniJGB.2014U | PX_VOLUME | 1     |
| 2014-05-23 | SGX.MiniJGB.2014U | OPEN_INT  | 2     |

然后，我使用以下交叉表查询来枢轴该表：

select dt, instrument, vol, oi 
FROM crosstab($$
    select dt, instrument, field, value 
    from marketdata.instrument_data 
    where field = 'PX_VOLUME' or field = 'OPEN_INT'
    $$::text, $$VALUES ('PX_VOLUME'),('OPEN_INT')$$::text
) vol(dt date, instrument text, vol numeric, oi numeric);

运行这个程序我得到的结果是：

| dt         | instrument        | vol | oi |
|------------+-------------------+-----+----|
| 2014-05-23 | SGX.MiniJGB.2014U | 1   | 2  |

问题:当在表中使用大量实际数据运行这个问题时，我注意到对于某些字段，函数将结果拆分为两行：

| dt         | instrument        | vol | oi |
|------------+-------------------+-----+----|
| 2014-05-23 | SGX.MiniJGB.2014U | 1   |    |
| 2014-05-23 | SGX.MiniJGB.2014U |     | 2  |

我检查了dt和仪表字段是否相同，并通过分组交叉表的输出产生了一个工作。

分析--我发现，是由于输入表中存在另一个条目，导致输出被分割为2行。如果我有如下输入：

| dt         | instrument        | field     | value |
|------------+-------------------+-----------+-------|
| 2014-04-23 | EUX.Bund.2014M    | PX_VOLUME | 0     |
| 2014-05-23 | SGX.MiniJGB.2014U | PX_VOLUME | 1     |
| 2014-05-23 | SGX.MiniJGB.2014U | OPEN_INT  | 2     |

我得到：

| dt         | instrument        | vol | oi |
|------------+-------------------+-----+----|
| 2014-04-23 | EUX.Bund.2014M    | 0   |    |
| 2014-05-23 | SGX.MiniJGB.2014U | 1   |    |
| 2014-05-23 | SGX.MiniJGB.2014U |     | 2  |

会变得很奇怪..。

如果我手动重新创建上面的输入表，那么输出就像我们所期望的那样，合并成一行。

如果我跑：

update marketdata.instrument_data 
set instrument = instrument 
where instrument = 'EUX.Bund.2014M'

再一次，输出和我们预期的一样，这是令人惊讶的，因为我所做的只是将仪器字段设置为自己。

因此，我只能得出结论，在Bund条目中存在一些隐藏的字符/编码问题，正在破坏交叉表。

是否有任何建议，我可以确定它是什么关于该条目，打破交叉表？

编辑:我在原始表上运行了以下命令，以尝试查看任何隐藏字符：

select instrument, encode(instrument::bytea, 'escape')  
from marketdata.bloomberg_future_data_temp 
where instrument = 'EUX.Bund.2014M';

并得到：

| instrument     | encode         |
|----------------+----------------|
| EUX.Bund.2014M | EUX.Bund.2014M |

Answer 1

两个问题。

1.需要ORDER BY。

手册：

在实践中，SQL查询应该始终指定ORDER BY 1,2，以确保输入行得到正确的排序，也就是说，将具有相同row_name的值组合在一起并在行中正确排序。

对于单参数形式的crosstab()，ORDER BY 1,2是必要的.

2.每组有一个值不同的列。

手册：

crosstab(text source_sql, text category_sql) source_sql是一种生成源数据集的SQL语句。 ..。 此语句必须返回一个 row_name列、一个category列和一个value列。它还可能有一个或多个“额外”列。row_name列必须是第一列。按照这个顺序，category和value列必须是最后两列。row_name和category之间的任何列都被视为“额外的”。对于所有具有相同row_name值的行，预期“额外”列是相同的。

大胆强调我的。One列。您似乎希望在的两个列上形成组，而这两个列并不像您所希望的那样工作。

有关答覆：

• 使用Tablefunc在多列上的枢轴

解决方案取决于你真正想要达到的目标。这不是你的问题，你默默地认为这个函数会做你希望的事情。

解决方案

我想您想在两个领先的列上分组：(dt, instrument)。您可以使用连接或数组来玩把戏，但这将是缓慢和/或不可靠的。我建议使用dense_rank()更简洁、更快的方法来生成每个期望组的单列唯一值。这非常便宜，因为订购行是主要成本，而且框架的顺序与所需的订单是相同的。如果需要，可以删除外部查询中添加的列：

SELECT dt, instrument, vol, oi
FROM   crosstab(
   $$SELECT dense_rank() OVER (ORDER BY dt, instrument) AS rnk
          , dt, instrument, field, value 
     FROM   marketdata.instrument_data 
     WHERE  field IN ('PX_VOLUME', 'OPEN_INT')
     ORDER  BY 1$$
 , $$VALUES ('PX_VOLUME'),('OPEN_INT')$$
   ) vol(rnk int, dt date, instrument text, vol numeric, oi numeric);

更多详细信息：

• PostgreSQL交叉表查询

Answer 2

您可以运行一个用星号替换不规则字符的查询：

select  regexp_replace(instrument, '[^a-zA-Z0-9]', '*', 'g')
from    marketdata.instrument_data
where   instrument = 'EUX.Bund.2014M'

也许instrument = instrument分配放弃了尾随空格。这也解释了为什么where instrument = 'EUX.Bund.2014M'匹配两个crosstab认为不同的值。