由于存在不相关的字段而导致交叉表拆分结果

一尘不染

由于存在不相关的字段而导致交叉表拆分结果

sql

我正在将postgres 9.1与tablefunc：crosstab一起使用

我有一个具有以下结构的表：

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和instrument字段是否相同，并通过将交叉表的输出分组来产生了解决方法。

分析我发现，输入表中存在另一个条目会导致输出分成两行。如果我有如下输入：

| 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'

再说一次，输出就如我们所期望的那样，这令人惊讶，因为我所做的只是将instrument字段设置为其自身。

因此，我只能得出这样的结论：在该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 |

阅读 227

2021-03-08

共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值的所有行，“额外”列应相同。

大胆强调我的。一栏。似乎您想在两列上形成组，但并没有按您希望的那样工作。

解决方案取决于您实际想要实现的目标。这不是您的问题，您默默地假定该功能将实现您希望的功能。

解决方案

我想您想在两个主要专栏上进行分组：(dt, instrument)。您可以使用串联或数组来玩技巧，但这会很慢和/或不可靠。我建议使用窗口函数
rank()
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);

2021-03-08