具有动态列数的PostgreSQL查询

一尘不染

具有动态列数的PostgreSQL查询

sql

我试图找到一种方法来返回具有动态列数的记录集。我可以编写一个查询来生成需要的列名列表，如下所示：

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

这将返回一个简短的列表，例如’poke’，’prod’，’hit’，’drop’等。然后，我希望生成一个表，其中显示了一系列测试，其中每个测试都在其中运行。每天早晨，我们查看开发人员的工作并对其进行戳戳和刺戳，以便每天进行每次测试。我可以静态编写此查询：

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

名称戳，针刺和放置都是在该时间段内在行的名称字段中找到的所有名称。对于没有该日期记录的任何测试，将为详细查询返回NULL。

阅读 272

2021-03-08

共1个答案

一尘不染

使用了不同的方法，这里已经提到了一些方法，例如交叉表。另外，您可以构建自己的函数，该函数可以动态构建查询并以TABLE和其他几种方法返回。

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

如果我了解您的情况，那么您就不会像您提到的那样：

如果现在我们需要每天开始踢设备，则需要更新查询。

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

因此，有一种使用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)

正如我所提到的，这不是完美的方法，但是它确实可以完成工作：)

2021-03-08