在PostgreSQL中，每个用户的最新价值填充丢失的日期

Question

我有一个表 dayload 标记用户每日时间更改时的标记。

| 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;

我加入了用户表等。

我认为这可以做您想要的：

select generate_series(date,
                       lead(date, 1, current_date) over (partition by user_id order by date) - interval '1 day',
                       interval '1 day'
                      ) as date,
       user_id, hours
from (values (1, '2019-01-27'::date, 1, 4),
             (2, '2019-02-01'::date, 1, 8),
             (3, '2018-06-30'::date, 2, 5)
     ) v(id, date, user_id, hours);

这是generate_series()的"简单"应用。lead()正在为用户获得下一个日期。减去一天的并发症，所有这些都没有重叠。

这是一个db＆lt;>小提琴。

所以，有一点播放，并提出以下查询，我认为这些查询会做您想要的：

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，或 c oummon t able e xpression，对此的简单解释是基本上是一种说法在这种情况下的内联临时表。请小心使用它们，因为它们专门存储在内存中，因此大数据返回可能会导致分页过多，将您的数据库带入爬网。

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

这是在我们加入Adload的当前行中的时间。如果那是无效的，那么它将从前行加入的Agload获得最高的时间。如果那是空的，它将返回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'::日期和'2019-02-28'::日期之间获得每个日期

交叉加入将连接两张表的每个记录，而没有过滤器。它在情况上很有用，但请记住，它将产生每个表中的记录数量。粗心的使用可能会产生比服务器的内存更多的记录。

一旦它交叉加入（给我们每个日期和每个用户ID），我们将留在Agload上。

因此，听起来关键是在实际日期和上一个更改日期之间建立关系（我们称其为目标日期）。我的两分钱是构建一个有两个列的辅助表：实际日期和目标日期。首先使用实际日期填充辅助表，目标日期可以空白。然后使用更新查询填充目标日期：

update HelperTable set TargetDate = 
(select Date from YourOriginalTable where 
HelperTable.ActualDate >= YourOriginalTable.Date 
order by YourOriginalTable.Date desc limit 1)

这样，您就可以建立上面提到的日期关系。然后，您可以利用此辅助表来构建目标表。或者，您只需在目标表中添加目标日期，如果您愿意，您可以选择以后删除列。