sql - 具有动态列数的 PostgreSQL 查询

Question

我正在尝试找到一种方法来返回具有动态列数的记录集。我可以编写一个查询来生成我需要的列名列表:

SELECT DISTINCT name FROM tests WHERE group = 'basic';

这将返回一个简短列表，如“戳”、“生产”、“命中”、“掉落”等。然后我想要生成一个表格，显示一系列测试，其中每个测试都在其中运行。每天早上我们都会查看开发人员一直在做的事情，然后戳戳它，以便每天运行每个测试。我可以静态编写此查询:

SELECT (SELECT success FROM test_results AS i
        WHERE i.name = 'poke'
        AND i.date = o.date) AS 'poke',
       (SELECT success FROM test_results AS i
        WHERE i.name = 'prod'
        AND i.date = o.date) AS 'prod',
...
FROM test_results AS o GROUP BY date
HAVING date > now() - '1 week'::interval;

但是，这是硬编码到我们每天运行的测试中。如果我们现在需要开始每天启动设备，我们需要更新查询。如果我们决定不再需要跌落测试，一周后，跌落测试列应该从报告中删除，因为它不再出现在结果中。当只有某些日期有结果条目时，为缺少的测试返回 NULL 是完全可以接受的。

是否有一种方法可以通过在查询中使用常规 SQL 来根据结果创建动态的列列表？

我试图通过使用 WITH 查询来构建我需要的部分数据，但我找不到从动态信息正确构建最后一行的方法。

编辑:这是过去两天的一些示例数据:

CREATE TABLE test_results (
    name TEXT NOT NULL,
    date DATE default now() NOT NULL,
    success BOOLEAN NOT NULL
);

INSERT INTO test_results (name, date, success) VALUES ('hit',  '2017-06-20', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-20', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-20', TRUE);

INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-21', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-21', TRUE);

INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-22', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-22', FALSE);

INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-23', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-23', TRUE);
INSERT INTO test_results (name, date, success) VALUES ('drop', '2017-06-23', TRUE);

如果我针对 2017-06-21 到 2017-06-23 的数据范围运行查询，我希望得到如下结果，包括当时运行的所有测试的矩阵:

date        | poke   | prod   | drop
------------+--------+--------+-----
2017-06-21  | TRUE   | TRUE   | NULL
2017-06-22  | TRUE   | FALSE  | NULL
2017-06-23  | TRUE   | TRUE   | TRUE

名称 poke、prod 和 drop 都是在该时间段内在一行的名称字段中找到的名称。对于没有该日期记录的任何测试的详细查询返回 NULL。

Answer 1

使用了不同的方法，有些已经在此处提到，例如交叉表。此外，您可以构建自己的函数，动态构建查询并返回为 TABLE。以及其他一些方法。

但所有这些都要求您预先定义确切数量的输出及其数据类型。

如果我理解您的情况，那是您所提到的不想要的:

If we now need to start kicking the device each day, we need to update the query.

这与使用交叉表和其他方式的缺点几乎相同。

所以有一种方法可以使用 Cursors .这可能不是最好的方法，如果您可以使用 crosstab 那么可能会更好。
但至少这是一个选项，我将在代码中添加注释。

解决方案:

-- Function for opening cursor
CREATE OR REPLACE
FUNCTION    test_stats(
                c REFCURSOR,    -- cursor name
                sdate date,     -- start date of period wanted (included)
                edate date,     -- end date of period wanted (included)
                gtype text      -- you had in your 'tests' table some group type which I included just in case
            )
RETURNS     REFCURSOR
LANGUAGE    PLPGSQL
AS
$main$
BEGIN
    OPEN    c
    FOR
    -- Following dynamic query building can be
    -- used also if want to go with function that RETURNS TABLE
    EXECUTE format(
            '   SELECT  r.date,
                        %s
                FROM    test_results r
                WHERE   r.date BETWEEN %L AND %L
                GROUP BY 1
            ',
                -- Here we build for each 'name' own statement and 
                -- aggregate together with comma separator to feed
                -- into main query.
                -- P.S. We need to double check result unfortunately
                --      against test_results table once to get pre-filter
                --      for names in specified date range.
                --      With this we eliminate tests that for sure will
                --      not be presented in the range. In given test data
                --      this means eliminating 'hit'.
            (
                SELECT  string_agg(
                            DISTINCT format(
                                '(  SELECT  success
                                    FROM    test_results i
                                    WHERE   i.name = %1$L
                                    AND     i.date = r.date ) AS "%1$s"',
                                t.name
                            ),
                            ','
                        )
                FROM    tests t,
                LATERAL (   SELECT  array_agg( DISTINCT r.name )
                            FROM    test_results r
                            WHERE   r.date BETWEEN sdate AND edate
                        ) a( lst )
                WHERE   t.group = gtype     -- the group type is used here
                AND     t.name = ANY ( a.lst::text[] )
            ),
            sdate,      -- start date for between statement
            edate       -- end date for between statement
        );
    RETURN c;
END;
$main$;

-- Usage example:
BEGIN;
SELECT test_stats( 'teststats1', '2017-06-21'::date, '2017-06-23'::date, 'basic' );
FETCH ALL IN teststats1;
COMMIT;

-- Result (from your given test data set):
    date    | drop | poke | prod
------------+------+------+------
 2017-06-22 |      | t    | f
 2017-06-21 |      | t    | t
 2017-06-23 | t    | t    | t
(3 rows)

正如我提到的，这不是完美的方式，但它可以完成工作:)