sql - 选择相同列的日期在 7 天组中的行

Question

对于下表（称为表），我想创建一个名为“trans_flag”的列，它指示每组 7 天中最新的行（每个客户端的 trans_number）。问题是 7 天不是固定的，而是在上一组的最后一次出现结束时开始。下面的例子。

trans_number    client      trans_date  
-----------------------------------------------------
abc001          client1     2019-07-23
abc003          client1     2019-06-05      
abc005          client1     2019-05-30          
abc007          client1     2019-05-28          
abc012          client1     2019-05-20          
abc018          client1     2019-05-16      
abc020          client1     2019-05-15          
abc023          client1     2019-05-14          
abc027          client1     2019-05-13      
abc028          client1     2019-04-16          
abc032          client1     2019-04-15          

我已经创建了 date_diff 列，它指示了“trans_date”列的相应日期之间的差异。

SELECT DISTINCT trans_number, client, trans_date,
        -ISNULL(DATEDIFF(day,
                    (SELECT MIN(trans_date)
                     FROM table
                     WHERE client= T.client AND trans_date> T.trans_date),
                    trans_date),
           0) date_diff
FROM table T
WHERE client = 123
ORDER BY trans_date DESC

trans_number    client      trans_date  date_diff
---------------------------------------------------------------------------
abc001          client1     2019-07-23      0
abc003          client1     2019-06-05      48
abc005          client1     2019-05-30      6
abc007          client1     2019-05-28      2
abc012          client1     2019-05-20      8
abc018          client1     2019-05-16      4
abc020          client1     2019-05-15      1
abc023          client1     2019-05-14      1
abc027          client1     2019-05-13      1
abc028          client1     2019-04-16      27
abc032          client1     2019-04-15      1

目的是查看最近日期的客户端级别，然后返回最近 7 天，以获取此类组中最近日期的记录。下一次迭代应该从不属于上一次迭代的 7 天组的下一条记录开始。

trans_number    client      trans_date  date_diff   trans_flag
---------------------------------------------------------------------------
abc001          client1     2019-07-23      0       1
abc003          client1     2019-06-05      48      1
abc005          client1     2019-05-30      6       0
abc007          client1     2019-05-28      2       1
abc012          client1     2019-05-20      8       1
abc018          client1     2019-05-16      4       0
abc020          client1     2019-05-15      1       0
abc023          client1     2019-05-14      1       0
abc027          client1     2019-05-13      1       1
abc028          client1     2019-04-16      27      1
abc032          client1     2019-04-15      1       0

对上面的例子的解释很少。

1) 事务 abc001 是该客户端的最新事务，因此 flag = 1

2) 事务 abc003 发生在 abc001 前 7 天以上，因此 flag = 1

3) 事务 abc005 发生在 abc003 之前不到 7 天，因此标志 = 0

4) 事务 abc007 的标志为 1，因为 abc003 是上一组指示的记录，并且 abc007 和 abc003 之间的日期差超过 7 天（abc005 因 abc003 而省略）。

如何创建“trans_flag”列？

标签： sqldatediff