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

Question

这个问题是基于之前的两个here和 here .

我非常努力地尝试获得这两个查询:

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'))

在单个中，因此我可以将结果读取为最小日期、最大日期、最小日期和最大日期之间(包括最小日期和最大日期)之间的日期计数。但这里几乎没有问题。

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

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

最后请大家帮忙解决这个问题。

可行的代码:

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 亲切提供的最终非常快速的改进版本:

        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

Answer 1

给定这张表(就像你应该提供的一样):

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

查询应该产生您所描述的内容:

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

CTEs需要 Postgres 8.4 或更高版本。
考虑upgrade to the latest point release 9.1，当前为 9.1.9。