PostgreSQL：使用'with子句'遍历日期范围 [英] Postgresql: using 'with clause' to iterate over a range of dates

问题描述

我有一个数据库表，其中包含开始访问和结束访问。如果日期在此范围内，则资产被标记为可用。资产属于用户。我的查询输入一个日期范围（开始和结束日期）。我需要返回数据，以便在一个日期范围内它将查询数据库，并在资产可用的日期范围内每天返回每天的资产计数。

I have a database table that contains a start visdate and an end visdate. If a date is within this range the asset is marked available. Assets belong to a user. My query takes in a date range (start and end date). I need to return data so that for a date range it will query the database and return a count of assets for each day in the date range that assets are available.

我知道有一些示例，我想知道是否有可能仅将它作为查询/公用表表达式来执行，而不是使用函数或临时表来执行。我还发现它很复杂，因为资产表没有包含资产可用的日期。我要在可见性窗口中查询日期范围。做这个的最好方式是什么？我应该只对给出的日期范围内的每一天进行单独查询吗？

I know there are a few examples, I was wondering if it's possible to just execute this as a query/common table expression rather than using a function or a temporary table. I'm also finding it quite complicated because the assets table does not contain one date which an asset is available on. I'm querying a range of dates against a visibility window. What is the best way to do this? Should I just do a separate query for each day in the date range I'm given?

Asset Table
StartvisDate Timestamp
EndvisDate   Timestamp
ID           int

User Table
ID

User & Asset Join table
UserID
AssetID


Date       | Number of Assets Available | User
11/11/14              5                   UK
12/11/14              6                   Greece
13/11/14              4                   America
14/11/14              0                   Italy

推荐答案

您需要使用设置返回函数以生成所需的行。看到以下相关问题：

You need to use a set returning function to generate the needed rows. See this related question:

SQL / Postgres日期时间除法/规范化

示例查询可帮助您入门：

Example query to get you started:

with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 15:25:00+00'::timestamptz, '2014-12-05 07:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
       count(data.id)
from data
join generate_series(
      date_trunc('day', data.start_date),
      date_trunc('day', data.end_date),
      '1 day'
      ) as days (d)
      on days.d >= date_trunc('day', data.start_date)
      and days.d <= date_trunc('day', data.end_date)
group by data.id

 id | count 
----+-------
  1 |     2
  2 |     1
(2 rows)

您需要将其转换为使用范围，并使其适应您自己的模式和数据，但基本上与您想要的查询相同。

You'll want to convert it to using ranges instead, and adapt it to your own schema and data, but it's basically the same kind of query as the one you want.

这篇关于PostgreSQL：使用'with子句'遍历日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！