使用了不同的方法，这里已经提到了一些方法，例如交叉表。另外，您可以构建自己的函数，该函数可以动态构建查询并以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)
正如我所提到的，这不是完美的方法，但是它确实可以完成工作：)
602392714
清零编程群