postgres tablefunc crosstab()函数中的类别可以是整数吗? [英] Can the categories in the postgres tablefunc crosstab() function be integers?

问题描述

全部在标题中.文档内容如下:

It's all in the title. Documentation has something like this:

SELECT * 
FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

我有两个表，lab_tests和lab_tests_results.所有lab_tests_results行都绑定到lab_tests表中的主键id整数.我正在尝试制作一个数据透视表，其中实验室测试(由整数标识)是行标题，并且相应的结果在表中.我无法绕过整数或整数附近的语法错误.

I have two tables, lab_tests and lab_tests_results. All of the lab_tests_results rows are tied to the primary key id integer in the lab_tests table. I'm trying to make a pivot table where the lab tests (identified by an integer) are row headers and the respective results are in the table. I can't get around a syntax error at or around the integer.

使用当前设置可以做到这一点吗?我在文档中缺少什么吗?还是我需要执行某种内部联接才能使类别成为字符串?还是修改lab_tests_results表以在实验室测试中使用文本标识符?

Is this possible with the current set up? Am I missing something in the documentation? Or do I need to perform an inner join of sorts to make the categories strings? Or modify the lab_tests_results table to use a text identifier for the lab tests?

谢谢您的帮助.非常感谢.

Thanks for the help, all. Much appreciated.

编辑:在Dmitry的帮助下找到了答案.他已经弄清楚了数据布局，但是我不清楚我需要哪种输出.我试图使数据透视表基于lab_tests_results表中的batch_id数字.必须锤击基础查询和转换数据类型.

Got it figured out with the help of Dmitry. He had the data layout figured out, but I was unclear on what kind of output I needed. I was trying to get the pivot table to be based on batch_id numbers in the lab_tests_results table. Had to hammer out the base query and casting data types.

SELECT *
 FROM crosstab('SELECT lab_tests_results.batch_id, lab_tests.test_name, lab_tests_results.test_result::FLOAT
                FROM lab_tests_results, lab_tests
                WHERE lab_tests.id=lab_tests_results.lab_test AND (lab_tests.test_name LIKE ''Test Name 1'' OR lab_tests.test_name LIKE ''Test Name 2'')
                ORDER BY 1,2'
            )   AS final_result(batch_id VARCHAR, test_name_1 FLOAT, test_name_2 FLOAT);

这提供了来自lab_tests_results表的数据透视表，如下所示:

This provides a pivot table from the lab_tests_results table like below:

batch_id   |test_name_1 |test_name_2 
---------------------------------------
batch1     |  result1   |   <null>
batch2     |  result2   |  result3  

推荐答案

如果我正确理解您的表，则如下所示:

If I understand correctly your tables look something like this:

CREATE TABLE lab_tests (
  id INTEGER PRIMARY KEY,
  name VARCHAR(500)
);

CREATE TABLE lab_tests_results (
  id INTEGER PRIMARY KEY,
  lab_tests_id INTEGER REFERENCES lab_tests (id),
  result TEXT
);

您的数据如下所示:

INSERT INTO lab_tests (id, name) 
VALUES (1, 'test1'),
       (2, 'test2');

INSERT INTO lab_tests_results (id, lab_tests_id, result)
VALUES (1,1,'result1'),
       (2,1,'result2'),
       (3,2,'result3'),
       (4,2,'result4'),
       (5,2,'result5');

首先crosstab是tablefunc的一部分，您需要启用它:

First of all crosstab is part of tablefunc, you need to enable it:

CREATE EXTENSION tablefunc;

您需要按照此答案在每个数据库中运行一个.

You need to run it one per database as per this answer.

最终查询如下:

SELECT *
FROM crosstab(
    'SELECT lt.name::TEXT, lt.id, ltr.result
     FROM lab_tests AS lt
     JOIN lab_tests_results ltr ON ltr.lab_tests_id = lt.id'
) AS ct(test_name text, result_1 text, result_2 text, result_3 text);

说明:
crosstab()函数采用查询文本，该查询应返回3列； (1)一列用于表示组的名称，(2)一列用于分组，(3)值.包装查询仅选择crosstab()返回的所有值并定义其后的列列表(AS之后的部分).首先是类别名称(test_name)，然后是值(result_1，result_2).在我的查询中，我最多可以得到3个结果.如果我得到的结果多于3个，那么我将看不到它们.如果我得到的结果少于3个，则我将得到空值.

Explanation:
The crosstab() function takes a text of a query which should return 3 columns; (1) a column for name of a group, (2) a column for grouping, (3) the value. The wrapping query just selects all the values those crosstab() returns and defines the list of columns after (the part after AS). First is the category name (test_name) and then the values (result_1, result_2). In my query I'll get up to 3 results. If I have more then 3 results then I won't see them, If I have less then 3 results I'll get nulls.

此查询的结果是:

test_name |result_1 |result_2 |result_3
---------------------------------------
test1     |result1  |result2  |<null>
test2     |result3  |result4  |result5

这篇关于postgres tablefunc crosstab()函数中的类别可以是整数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！