使用postgres tablefunc crosstab()返回空的单个值 [英] Returning null individual values with postgres tablefunc crosstab()

问题描述

我正在尝试将空值合并到返回的列表中，例如:

I am trying to incorporate the null values within the returned lists, such that:

batch_id   |test_name   |test_value
-----------------------------------
10         | pH         | 4.7
10         | Temp       | 154
11         | pH         | 4.8
11         | Temp       | 152
12         | pH         | 4.5
13         | Temp       | 155
14         | pH         | 4.9
14         | Temp       | 152
15         | Temp       | 149
16         | pH         | 4.7
16         | Temp       | 150

将返回:

batch_id   | pH         |Temp 
---------------------------------------
10         |  4.7       |   154
11         |  4.8       |   152
12         |  4.5       |   <null>
13         |  <null>    |   155
14         |  4.9       |   152
15         |  <null>    |   149
16         |  4.7       |   150

但是，它当前返回此值:

However, it currently returns this:

batch_id   | pH         |Temp 
---------------------------------------
10         |  4.7       |   154
11         |  4.8       |   152
12         |  4.5       |   <null>
13         |  155       |   <null>
14         |  4.9       |   152
15         |  149       |   <null>
16         |  4.7       |   150

这是先前问题的扩展- postgres tablefunc crosstab()中的类别是否可以函数是整数吗?-导致当前查询:

This is an extension of a prior question - Can the categories in the postgres tablefunc crosstab() function be integers? - which led to this current query:

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);

我也知道，我并不是第一个提出这个问题的人，但是我还没有找到适合这些情况的解决方案.例如，这一个如何在其中包含空值PostgreSQL中的"tablefunc"查询?-每次都假定相同的批次ID.我不想指定批次ID，而是要指定所有可用的ID.

I also know that I am not the first to ask this question generally, but I have yet to find a solution that works for these circumstances. For example, this one - How to include null values in `tablefunc` query in postgresql? - assumes the same Batch IDs each time. I do not want to specify the Batch IDs, but rather all that are available.

这导致了我在那里找到的另一组解决方案，它们解决了指定类别的空列表结果.但是，由于我只是拿走已有的东西，所以这不是问题.是空的 individual 值引起了问题，并导致数据透视表的值向左移动.

This leads into the other set of solutions I've found out there, which address a null list result from specified categories. Since I'm just taking what's already there, however, this isn't an issue. It's the null individual values causing the problem and resulting in a pivot table with values shifted to the left.

任何建议都将不胜感激！

Any suggestions are much appreciated!

在克林(Klin)的帮助下，进行了整理.需要注意的是，VALUES部分必须与您追求的实际lab_tests.test_name值匹配，例如:

With Klin's help, got it sorted out. Something to note is that the VALUES section must match the actual lab_tests.test_name values you're after, such that:

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_results.lab_test = 1 
                OR lab_tests_results.lab_test = 2 
                OR lab_tests_results.lab_test = 3 
                OR lab_tests_results.lab_test = 4 
                OR lab_tests_results.lab_test = 5 
                OR lab_tests_results.lab_test = 50 )
            ORDER BY 1 DESC, 2
        $$,
        $$
                VALUES('Mash pH'),
                    ('Sparge pH'),
                    ('Final Lauter pH'),
                    ('Wort pH'),
                    ('Wort FAN'),
                    ('Original Gravity'),
                    ('Mash Temperature')
        $$
        )   AS final_result(batch_id VARCHAR,
                            ph_mash FLOAT, 
                            ph_sparge FLOAT, 
                            ph_final_lauter FLOAT, 
                            ph_wort FLOAT, 
                            FAN_wort FLOAT, 
                            original_gravity FLOAT, 
                            mash_temperature FLOAT)

感谢您的帮助！

推荐答案

使用第二种形式的功能:

crosstab(文本source_sql，文本category_sql)-生成一个数据透视表"，其中包含第二个查询指定的值列.

crosstab(text source_sql, text category_sql) - Produces a "pivot table" with the value columns specified by a second query.

例如:

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
    $$,
    $$ 
        VALUES('pH'), ('Temp') 
    $$
) AS final_result(batch_id VARCHAR, "pH" FLOAT, "Temp" FLOAT);

这篇关于使用postgres tablefunc crosstab()返回空的单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！