查询按日期范围分隔的行数 [英] Querying row counts segregated by date ranges
问题描述
我有一个PostgreSQL 9.2.1数据库，我在其中尝试但未能编写一个SQL查询，该查询将向我显示不同测试的计数（ testname ）失败（ current_status ='FAILED'，如果没有失败则显示0），按月进行分类（ last_update ）。表格定义如下：
表 public.tests
列|类型修饰符
---------------- + ---------------------------- -+ ------------------------------------------------ -------------
id | bigint |不是null默认nextval（’tests_id_seq':: regclass）
测试名称|文字|不为空
last_update |没有时区的时间戳|不为null的默认值now（）
current_status |文字|不是null
我想从中得到的是这样的：
测试名| 2012年1月| 2012年2月| 2012年3月| 2012年4月| 2012年5月| 2012年6月| 2012年7月| 2012年8月| 2012年9月| 2012年10月| 2012年11月| 2012年12月
------------- + ------------------------------- -------------------------------------------------- -------------------------------------------------- ------
abq | 2 | 5 | 2 | 0 | 7 | 4 | 8 | 0 | 6 | 15 | 1 | 0
条| 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
在这一点上，我能想到的最好的是以下内容，但公认的不是关闭：
选择测试名称，计数（当前状态）AS failure_count
FROM测试
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来显示多个月的结果，甚至可能是窗口函数？
crosstab（）具有两个参数的函数。
应该这样工作，以获取2012年的值：
SELECT * FROM crosstab（
$ $ SELECT测试名称，to_char（last_update，'mon_YYYY'），count（*）:: int AS ct
FROM测试
WHERE current_status ='FAILED'
AND last_update> ='2012- 01-01 0：0'
AND last_update<'2013-01-01 0：0'-正确的日期范围！
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（测试名文本
，jan_2012 int，feb_2012 int，mar_2012 int
，apr_2012 int，may_2012 int ，jun_2012 int
，juul_2012 int，aug_2012 int，sep_2012 int
，oct_2012 int，nov_2012 int，dec_2012 int）;
我没有测试。如@Craig所评论，示例值会有所帮助。
现在使用我自己的测试用例进行了测试。
不显示NULL值
主要问题（没有行的月份根本不会出现）由带有两个参数的 crosstab（）函数避免了。 / p>
您不能在内部查询中使用 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（
-从上方查询）
）x;
2。 向左联接是对完整月份列表的主要查询。
在这种情况下，您不需要
对于更大的范围，可以使用 generate_series（）创建值。
SELECT * FROM交叉表（
$$ SELECT t.testname，m.mon，count（x.testname）:: int AS ct
FROM （
值
（'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）
交叉联接（从测试中选择不同的测试名称）t
左联接（
选择测试名称
，to_char（last_update，'mon_YYYY'）AS mon
FROM测试
WHERE current_status ='FAILED'
AND last_update> ='2012-01-01 0：0'
AND la st_update< ‘2013-01-01 0：0’-正确的日期范围！
）x使用（mon）
按组1,2
排序1,2 $$
）
AS ct（测试名文本
，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无法提供的一些示例数据的测试用例。我用它来测试它并使其正常工作。
创建温度表测试（
id bigserial主键
，测试名文本NOT NULL
，没有时区的last_update时间戳NOT NULL缺省now（）
，current_status文本NOT NULL
）;
插入测试（testname，last_update，current_status）
值
（'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'）;
I've got a PostgreSQL 9.2.1 database, where I'm attempting and failing to compose a SQL query which will show me the count of distinct tests (testname) which failed (current_status='FAILED' and showing 0 if there were no failures), segregated by month (last_update). Here's the table definition:
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
What I'd like to get back from that is something like this:
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
At this point, the best that I could come up with is the following, which is admittedly not close:
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 ;
I think I'd need to somehow use COALESCE to get 0 values to show up in the results, plus some crazy JOINs to show multiple months of results, and maybe even a window function?
crosstab() function with two parameters.
Should work like this, to get values for 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.
I didn't test. As @Craig commented, sample values would have helped.
Tested now with my own test case.
Don't display NULL values
The main problem (that months without rows wouldn't show up at all) is averted by the crosstab() function with two parameters.
You cannot use COALESCE in the inner query, because the NULL values are inserted by crosstab() itself. You could ...
1. Wrap the whole thing into a subquery:
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 the primary query to the full list of months.
In this case, you don't need the second parameter by definition.
For a bigger range you could use generate_series() to create the values.
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);
Test case with sample data
Here is a test case with some sample data that the OP failed to provide. I used this to test it and make it work.
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');
这篇关于查询按日期范围分隔的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
