时间戳

首页 > 解决方案 > 如何在 SQL 中添加基于日期的标识符列？

sql - 如何在 SQL 中添加基于日期的标识符列？

问题描述

我有下表（这里是它的一个示例）：

name date_time
jon  10/01/2019
jon  11/01/2019
jon  12/01/2019
jon  25/01/2019
sam  03/04/2019
sam  18/04/2019
sam  19/04/2019
sam  29/04/2019
fred 02/02/2019
fred 03/02/2019
fred 12/02/2019
fred 13/02/2019

在 postgresql 中有一个名称列 var char 和 date_time 列，它们是日期格式。

我想创建一个新列，让我确定用户是否处于“会话”中。

“会话”是用户首次记录 date_time 值的前 10 天，前 10 天之后的任何内容都是另一个“会话”。

这并不意味着每 10 天一个新标识符，它意味着只需要制作 2 个标识符 - 从第一个 date_time 值开始的前 10 天一个，一个从第 11 天开始。

例如，它应该看起来像这样：

name date_time  session_identifier
jon  10/01/2019 jon_session_1_id
jon  11/01/2019 jon_session_1_id
jon  12/01/2019 jon_session_1_id
jon  25/01/2019 jon_session_2_id
sam  03/04/2019 sam_session_1_id
sam  18/04/2019 sam_session_2_id
sam  19/04/2019 sam_session_2_id
sam  29/04/2019 sam_session_2_id
fred 02/02/2019 fred_session_1_id
fred 03/02/2019 fred_session_1_id
fred 12/02/2019 fred_session_2_id
fred 13/02/2019 fred_session_2_id

如您所见，以用户 jon 为例：

我想在这里创建一个标识符（我称之为 jon_session_1_id），并且从第一个 date_time 值开始的 10 天内重复该标识符 - 这是 2019 年 10 月 1 日，因此 jon_session_1_id 将作为 session_identifier 重复，直到 20/01 /2019 然后它将更改为 job_session_2_id。

对于表中的其他名称，依此类推。

这如何在 SQL 中完成？我使用了一个 case when 声明但没有结果接近！

标签： sqlpostgresql

解决方案

Fred 和 Same 的结果不一致。在一种情况下，9 天的延迟会导致新的会话。另一个允许 10 天的延迟。我将弗雷德的解释为一个错字。

您可以使用lag()和累积总和：

select t.*,
       sum(case when prev_dt >= date_time - interval '10 day' then 0 else 1 end) over
          (partition by name order by date_time) as session_number
from (select t.*,
             lag(date_time) over (partition by name order by date_time) as prev_dt
      from t
     ) t;

由于使用sum()this 从 1 开始计数。您也可以将其写为：

select t.*,
       1 + count(*) filter (where prev_dt < date_time - interval '10 day') over
              (partition by name order by date_time) as session_number
from (select t.*,
             lag(date_time) over (partition by name order by date_time) as prev_dt
      from t
     ) t;

这是一个 db<>fiddle。

推荐阅读