PostgreSQL,最小值,最大值和日期范围内的计数 [英] PostgreSQL, min, max and count of dates in range
问题描述
This question is based of two previous here and here.
我很努力得到这两个查询:
I am trying very hard to get those two queries:
SELECT min(to_date(nullif(mydatetext,''), 'DD.MM.YYYY')) AS dmin,
max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) AS dmax
FROM mytable
和
SELECT count(*)
FROM mytable
WHERE
to_date(nullif(mydatetxt,'')) 'ERROR HERE
BETWEEN
max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
AND
min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
,所以我可以读取结果作为最小日期,最大日期,和最大日期。
但是这里有几个问题。
in single one so I can read result as minimal date, maximal date, count of dates between and including min and max dates. But here are few problems.
第二次查询不能按预期工作或不工作,所以必须改进。
如果这两个查询可以写在单个查询(?)我可以使用dmin和dmax变量从第一部分作为变量在第二部分?
像这样:
Second query don't work as expected or don't work at all so have to be improved. If those two queries can be writen in single query (?) can I use dmin and dmax variables from first part as variables in second part? Like this:
SELECT count(*)
FROM mytable
WHERE
to_date(nullif(mydatetxt,'')) 'ERROR HERE
BETWEEN
dmin
AND
dmax
请帮助最终解决这种情况。
Please help to solve this situation finally.
可操作代码:
Using cmd As New NpgsqlCommand("SELECT my_id, mydate FROM " & mytable, conn)
Using dr As NpgsqlDataReader = cmd.ExecuteReader()
While dr.Read()
mydate = CStr(dr(1))
If IsDate(mydate) Then
Dim dat As Date = CDate(mydate.Substring(6, 4) & "/" & mydate.Substring(3, 2) & "/" & mydate.Substring(0, 2))
If dat < mindate Or mindate = Nothing Then
mindate = dat
End If
If dat > maxddate Or maxdate = Nothing Then
maxdate = dat
End If
count += 1
End If
End While
End Using
End Using
解决方案:
这是最终非常快, Ervin请提供:
SOLUTION: And this is finally very fast, improved version which Ervin kindly give:
Using cmd As New NpgsqlCommand( _
"WITH base AS (" & _
" SELECT TO_DATE(datum, 'DD.MM.YYYY') AS the_date " & _
" FROM " & myKalkTable & " " & _
" WHERE datum <> '') " & _
" SELECT MIN(the_date) AS dmin, " & _
" MAX(the_date) AS dmax, " & _
" COUNT(*) AS ct_incl, " & _
" (SELECT COUNT(*) " & _
" FROM base b1 " & _
" WHERE(b1.the_date < max(b.the_date)) " & _
" AND b1.the_date > min(b.the_date)) " & _
" AS ct_excl " & _
" FROM base b", conn)
Using dr As NpgsqlDataReader = cmd.ExecuteReader()
While dr.Read()
mindate = CType(CDate(CStr(dr(0))), Date)
maxdate = CType(CDate(CStr(dr(1))), Date)
count = CInt(dr(2))
End While
End Using
End Using
推荐答案
给出这个表格(像你应该提供的):
Given this table (like you should have provided):
CREATE TEMP TABLE tbl (
id int PRIMARY KEY
,mydatetxt text
);
INSERT INTO tbl VALUES
(1, '01.02.2011')
,(2, '05.01.2011')
,(3, '06.03.2012')
,(4, '07.08.2011')
,(5, '04.03.2013')
,(6, '06.08.2011')
,(7, '') -- empty string
,(8, '02.02.2013')
,(9, '04.06.2010')
,(10, '10.10.2012')
,(11, '04.04.2012')
,(12, NULL) -- NULL
,(13, '04.03.2013'); -- min date a 2nd time
查询应产生您所描述的内容:
The query should produce what you describe:
结果为最小日期,最大日期,包括最小和最大日期的日期数
result as minimal date, maximal date, count of dates between and including min and max dates
WITH base AS (
SELECT to_date(mydatetxt, 'DD.MM.YYYY') AS the_date
FROM tbl
WHERE mydatetxt <> '' -- excludes NULL and ''
)
SELECT min(the_date) AS dmin
,max(the_date) AS dmax
,count(*) AS ct_incl
,(SELECT count(*)
FROM base b1
WHERE b1.the_date < max(b.the_date)
AND b1.the_date > min(b.the_date)
) AS ct_excl
FROM base b
CTE 需要Postgres 8.4或更高版本。
考虑升级到9.1的最新版本,目前为 9.1.9 。
CTEs require Postgres 8.4 or later.
Consider to upgrade to the latest point release of 9.1, which is currently 9.1.9.
这篇关于PostgreSQL,最小值,最大值和日期范围内的计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!