sql - 查询按日期范围分隔的行数

Question

我有一个 PostgreSQL 9.2.1 数据库，我在其中尝试编写 SQL 查询但失败了，该查询将显示失败的不同测试 (testname) 的计数 (current_status='FAILED' 并在没有失败时显示 0)，按月分隔(last_update)。这是表定义:

                                       Table "public.tests"
     Column     |            Type             |                          Modifiers                          
----------------+-----------------------------+-------------------------------------------------------------
 id             | bigint                      | not null default nextval('tests_id_seq'::regclass)
 testname       | text                        | not null
 last_update    | timestamp without time zone | not null default now()
 current_status | text                        | not null

我想从中得到的是这样的:

 testname    | Jan2012  | Feb2012  | Mar2012  | Apr2012  | May2012   | Jun2012   | Jul2012   | Aug2012   | Sep2012   | Oct2012   | Nov2012   | Dec2012
-------------+-----------------------------------------------------------------------------------------------------------------------------------------
 abq         |   2      |   5      |   2      |   0      |   7       |  4        |   8       |   0       |     6     |   15      |  1        |  0
 bar         |   0      |   0      |   2      |   0      |   9       |  8        |   8       |   2       |     6     |   15      |  1        |  1
 cho         |   15     |   1      |   2      |   3      |   4       |  8        |   7       |   3       |     6     |   1       |  5        |  6

在这一点上，我能想到的最好的是以下内容，这当然不是很接近:

SELECT testname, count(current_status) AS failure_count
FROM tests
WHERE current_status='FAILED'
AND last_update>'2012-09-01'
AND last_update<='2012-09-30'
GROUP by testname
ORDER BY testname ;

我想我需要以某种方式使用 COALESCE 来获得 0 值以显示在结果中，加上一些疯狂的 JOIN 来显示多个月的结果，并且甚至可能是一个窗口函数？

Answer 1

crosstab() 函数有两个参数。

应该像这样工作，以获得 2012 年的值:

SELECT * FROM crosstab(
     $$SELECT testname, to_char(last_update, 'mon_YYYY'), count(*)::int AS ct
        FROM   tests
        WHERE  current_status = 'FAILED'
        AND    last_update >= '2012-01-01 0:0'
        AND    last_update <  '2013-01-01 0:0'  -- proper date range!
        GROUP  BY 1,2
        ORDER  BY 1,2$$

    ,$$VALUES
      ('jan_2012'::text), ('feb_2012'), ('mar_2012')
    , ('apr_2012'), ('may_2012'), ('jun_2012')
    , ('jul_2012'), ('aug_2012'), ('sep_2012')
    , ('oct_2012'), ('nov_2012'), ('dec_2012')$$)
AS ct (testname  text
   , jan_2012 int, feb_2012 int, mar_2012 int
   , apr_2012 int, may_2012 int, jun_2012 int
   , jul_2012 int, aug_2012 int, sep_2012 int
   , oct_2012 int, nov_2012 int, dec_2012 int);

Find detailed explanation under this related question.

我没有测试。 正如@Craig 评论的那样，样本值会有所帮助。
现在用我自己的测试用例进行测试。

不显示 NULL 值

具有两个参数的 crosstab() 函数避免了主要问题(没有行的月份根本不会显示)。

您不能在内部查询中使用 COALESCE，因为 NULL 值是由 crosstab() 本身插入的。你可以……

1。将整个事情包装到一个子查询中:

SELECT testname
      ,COALESCE(jan_2012, 0) AS jan_2012
      ,COALESCE(feb_2012, 0) AS feb_2012
      ,COALESCE(mar_2012, 0) AS mar_2012
      , ...
FROM (
    -- query from above)
    ) x;

2。 LEFT JOIN 对完整月份列表的主要查询。

在这种情况下，您不需要定义第二个参数。
对于更大的范围，您可以使用 generate_series() 来创建值。

SELECT * FROM crosstab(
     $$SELECT t.testname, m.mon, count(x.testname)::int AS ct
       FROM  (
          VALUES
           ('jan_2012'::text), ('feb_2012'), ('mar_2012')
          ,('apr_2012'), ('may_2012'), ('jun_2012')
          ,('jul_2012'), ('aug_2012'), ('sep_2012')
          ,('oct_2012'), ('nov_2012'), ('dec_2012')
       ) m(mon)
       CROSS JOIN (SELECT DISTINCT testname FROM tests) t
       LEFT JOIN (
          SELECT testname
                ,to_char(last_update, 'mon_YYYY') AS mon
          FROM   tests
          WHERE  current_status = 'FAILED'
          AND    last_update >= '2012-01-01 0:0'
          AND    last_update <  '2013-01-01 0:0'  -- proper date range!
          ) x USING (mon)
       GROUP  BY 1,2
       ORDER  BY 1,2$$
     )
AS ct (testname  text
   , jan_2012 int, feb_2012 int, mar_2012 int
   , apr_2012 int, may_2012 int, jun_2012 int
   , jul_2012 int, aug_2012 int, sep_2012 int
   , oct_2012 int, nov_2012 int, dec_2012 int);

带有示例数据的测试用例

这是一个测试用例，其中包含 OP 未能提供的一些示例数据。我用它来测试它并让它工作。

CREATE TEMP TABLE tests (
  id             bigserial PRIMARY KEY
 ,testname       text NOT NULL
 ,last_update    timestamp without time zone NOT NULL DEFAULT now()
 ,current_status text NOT NULL
 );

INSERT INTO tests (testname, last_update, current_status)
VALUES
  ('foo', '2012-12-05 21:01', 'FAILED')
 ,('foo', '2012-12-05 21:01', 'FAILED')
 ,('foo', '2012-11-05 21:01', 'FAILED')
 ,('bar', '2012-02-05 21:01', 'FAILED')
 ,('bar', '2012-02-05 21:01', 'FAILED')
 ,('bar', '2012-03-05 21:01', 'FAILED')
 ,('bar', '2012-04-05 21:01', 'FAILED')
 ,('bar', '2012-05-05 21:01', 'FAILED');