时间戳

首页 > 解决方案 > 在 PostgreSQL 中用每个用户的最新值填充缺失的日期

sql - 在 PostgreSQL 中用每个用户的最新值填充缺失的日期

问题描述

我有一个表日负荷标记用户的每日工作时间何时更改。

| id | date       | user_id | hours |
| 1  | 2019-01-27 | 1       | 4     |
| 2  | 2019-02-01 | 1       | 8     |
| 3  | 2018-06-30 | 2       | 5     |
| 4  | 2018-07-02 | 2       | 8     |

因此该表仅跟踪更改。我想得到的是一系列连续的日期，其中包含当前有效的时间。

例如，我想知道 2018-01-01 和 2019-02-28 之间每个用户的时间和日期，这将是

| id  | date       | user_id | hours |
| ..  | 2018-01-27 | 1       | 4     |
| ..  | 2018-01-28 | 1       | 4     |
| ..  | 2018-01-29 | 1       | 4     |
| ..  | 2018-01-30 | 1       | 4     |
| ..  | 2018-01-31 | 1       | 4     |
| ..  | 2019-02-01 | 1       | 8     |
| ..  | 2019-02-02 | 1       | 8     |
| ..  | 2019-02-03 | 1       | 8     |
| ..  | 2019-02-04 | 1       | 8     |
           ...
| ..  | 2018-06-30 | 2       | 5     |
| ..  | 2018-07-01 | 2       | 5     |
| ..  | 2018-07-02 | 2       | 8     |
| ..  | 2018-07-03 | 2       | 8     |
           ...

我不知道如何填写空白，正如我所描述的那样。我曾考虑过创建一个仅包含 1900 年到 2100 年之间日期的表格，但我不知道如何使用日期表填写空白。

我已经阅读了 generate_series，我尝试过以不同的方式加入数据，并且我还尝试过使用 PostgresSQL 的窗口函数。但我不知道怎么做。

我是最接近日期表的，但问题是如果用户的最新行的日期超出了我要查询的范围，则不会显示在结果中。这是我尝试的查询：

SELECT user_id, d.date, minutes

    FROM day d

    JOIN dayload dl

    ON dl.date = (
        SELECT MAX(date) from DAYLOAD where date <= d.date
    )
    order by d.date;

我将用户表等加入到这个关系中，但是当我将日期范围过滤应用于查询时，那些具有日期范围之外的最新日负载的行被遗漏了。

标签： sqlpostgresqlwindow-functionsgenerate-series

解决方案

所以，玩了一会儿，想出了以下查询，我认为它会做你想做的事：

with
    __users as(
        select distinct
            user_id
        from
            dayload
    )
select
    row_number() over(order by __users.user_id asc, gs.date asc) as id,
    gs.date::date,
    __users.user_id,
    coalesce(dayload.hours, max(hours) over(partition by __users.user_id order by gs.date asc), 0) as hours
from
    generate_series('2018-01-01'::date, '2019-02-28'::date, interval '1 day') as gs("date")
    cross join __users
    left join dayload using(date, user_id)
order by
    __users.user_id asc,
    gs.date asc;

查询说明：

with
    __users as(
        select distinct
            user_id
        from
            dayload
    )

这被称为 CTE 或通用表表达式，对其进行简单的解释是说它在这种情况下基本上是一个内联临时表。使用它们时要小心，因为它们专门存储在内存中，因此返回的大量数据可能会导致分页过多，从而使您的数据库陷入爬网状态。

generate_series('2018-01-01'::date, '2019-02-28'::date, interval '1 day') as gs("date")

这是在传入的第一个和第二个参数之间生成空白日期。这是您定义要查询的日期范围的地方。

coalesce(dayload.hours, max(hours) over(partition by user_id order by date asc), 0) as hours

这是获取我们在 dayload 中加入的当前行中的小时数。如果为空，则它会从前几行加入的 dayload 中获取最高小时数。如果为空，则返回 0。

generate_series('2018-01-01'::date, '2019-02-28'::date, interval '1 day') as gs("date")
cross join __users
left join dayload using(date, user_id)

这首先获取 '2018-01-01'::date 和 '2019-02-28'::date 之间的每个日期，然后它从之前交叉连接到我们的 CTE。

交叉连接将把两个表中的每条记录连接在一起，没有过滤器。它在情况下很有用，但请记住，它将产生每个表中的记录数相乘。粗心的使用可能会导致比服务器内存更多的记录。

一旦它交叉加入（给我们每个日期和每个用户 ID），我们就离开加入到 dayload。

推荐阅读