时间戳

首页 > 解决方案 > 添加 PIT Id

sql - 添加 PIT Id

问题描述

我有一个 Postgresql 9.3 查询，如下所示：

select dateSoS, timeSoS
from workSchedules
where timeSoS between '02:00' and '11:00'
group by dateSoS, timeSoS, createdTs
order by dateSoS desc, createdTs desc, timeSoS desc;

它输出大约 10k 条记录，如下所示：

"2021-04-15";"08:00:00"
"2021-04-15";"08:00:00"
"2021-04-15";"08:00:00"
"2021-04-15";"08:00:00"
"2021-04-15";"08:00:00"
"2021-04-15";"07:30:00"
"2021-04-15";"07:30:00"
"2021-04-15";"07:30:00"
"2021-04-14";"07:30:00"
"2021-04-14";"07:30:00"
"2021-04-14";"07:30:00"
"2021-04-14";"07:30:00"
"2021-04-13";"06:30:00" <== not an error
"2021-04-13";"08:00:00"
"2021-04-13";"08:00:00"
"2021-04-13";"08:00:00"
...

其中，workSchedules 有一个名为 PITId（时间点 ID）的附加字段。我需要的是 PITId 填充每个日期的唯一 ID。换句话说，使用上面的示例，我希望 dateSoS 为 2021-04-15 的所有记录的 PITId 为 1，dateSoS 为 2021-04-14 的 PITId 为 2，dateSoS 为 2021-04-13 PITId 为 3，并且...

我尝试编写一些 PHP 来完成这项工作，但它永远运行。

对于 timeSoS 的时间范围，我还有四个范围可以运行。我不介意运行四次查询，但一次运行必须添加前一次。

问候，

标签： sqlpostgresql-9.3

解决方案

使用dense_rank()：

select t.*,
       dense_rank() over (order by dateSos::date desc) as pitid
from t;

在更新中，您可以改为使用：

update t
    set pitid = tt.new_pitid
    from (select dateSos::date as dateSos,
                 row_number() over (order by dateSos::date) as new_pitid
          from t
          group by 1
         ) tt
    where tt.dateSos = dateSos::date

推荐阅读