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

- > SQLfiddle演示

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屋！