PostgreSQL，最小值，最大值和范围内的日期计数

PostgreSQL, min, max and count of dates in range

提问by Wine Too

This question is based of two previous hereand here.

这个问题基于之前的两个here和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

and

和

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.

在单个中，所以我可以将结果读取为最小日期、最大日期、介于（包括最小和最大日期）之间的日期计数。但这里有几个问题。

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:

第二个查询没有按预期工作或根本不起作用，因此必须改进。如果这两个查询可以在单个查询中编写（？），我可以将第一部分中的 dmin 和 dmax 变量用作第二部分中的变量吗？像这样：

SELECT count(*)
FROM mytable
WHERE
to_date(nullif(mydatetxt,''))  'ERROR HERE
BETWEEN 
dmin
AND
dmax

Please help to solve this situation finally.

请帮助最终解决这种情况。

Workable code:

可用代码：

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

SOLUTION: And this is finally very fast, improved version which Ervin kindly give:

解决方案：这最终是非常快速的改进版本，Ervin 亲切地给出了：

        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

回答by Erwin Brandstetter

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

-> SQLfiddle demo

-> SQLfiddle 演示

CTEsrequire Postgres 8.4 or later.
Consider to upgrade to the latest point releaseof 9.1, which is currently 9.1.9.

CTE需要 Postgres 8.4 或更高版本。
考虑升级到9.1的最新版本，目前是9.1.9。