英文:

SQL - Update column based on date comparasion with previous occurrence

问题

我有一个庞大的表格；

我想创建第三列，基于相同ID的两个日期之间的时间差异。如果差异小于一个月，则为活跃；如果在1-2个月之间，则为非活跃；如果超过2个月，则为休眠。预期结果如下；（请注意，最后的条目没有活动定义，因为我没有先前的发生。）

我的问题是，如何执行此类操作。

case 
  when date_ >= date_add((select max(date_) from schema.table),-30) then 'Active'
  when date_ < date_add((select max(date_) from schema.table),-30) and date_ >= date_add((select max(date_) from schema.table),-60) then 'Inactive'
  when date_ < date_add((select max(date_) from schema.table),-60) then 'Dormant3' 
end as Activity

我提出的代码不符合我的需求，因为它只检查表中的最后一个条目日期。我需要的更类似于一个循环，比较每一行并将其与前一次出现进行比较。

编辑：

通过在ID上进行分区并对其进行密集排名，我得到了几乎可以工作的东西。我只需要将其与密集排名组中的前一个元素进行比较。

英文:

I have a huge table;

I want to create a third column based on the time difference between two dates for the same id. If the difference is less than a month, then it's active, if it is between 1-2 months then inactive and anything more than 2 is dormant. The expected outcome is below;( note last entries don't have activity definitions as I don't have previous occurrences.)

My question would be, how to do such operation.

case when date_>=date_add((select max(date_) from schema.table),-30) then 'Active'
when date_<date_add((select max(date_) from schema.table),-30) and date_>= date_add((select max(date_) from schema.table),-60) then 'Inactive'
when date_<date_add((select max(date_) from schema.table),-60) then 'Dormant3' end as Activity

the code I came up with is not what I need as it only checks for the final entry date in the table. What I need is more akin to a for loop and checking the each row and comparing it to the previous occurrence.

edit:

By partitioning over id and dense ranking them, I reached something that almost works. I just need to compare to the previous element in the dense rank groups.

答案1

得分: 1

1. 首先使用 LEAD() 创建基本数据。
2. 然后将其与原始行进行比较。

SELECT ID, DATE, 
CASE 
WHEN DATEDIFF(DATE, PREVIOUS_DATE) <= 30 THEN 'Active'
DATEDIFF(DATE, PREVIOUS_DATE) between 31 and 60 'Active'
ELSE 'Dormant'
END as Activity
(SELECT ID, DATE, LEAD(DATE) OVER (PARTITION BY ID ORDER BY DATE) PREVIOUS_DATE FROM MYTABLE) RS

<details>
<summary>英文:</summary>

1. Create base data first using LEAD()
2. Then compare than with original row.
````
SELECT ID, DATE, 
CASE 
WHEN DATEDIFF(DATE,PREVIOUS_DATE) &lt;=30 THEN &#39;Active&#39;
DATEDIFF(DATE,PREVIOUS_DATE) between 31 and 60 &#39;Active&#39;
ELSE &#39;Dormant&#39;
END as Activity
(SELECT ID, DATE, LEAD(DATE) OVER( partition by id ORDER BY DATE) PREVIOUS_DATE FROM MYTABLE) RS

</details>