目前，我能够跟踪用户以唯一身份登录的第一个实例。但我希望能够在7天后让它们再次变得独特，每过7天就重复一次。SQL可以做到这一点吗？
原始表格：
| UserID | 日期记录日期模拟差异 | |
|---|---|---|
| 1 | 2022年1月 | 空|
| 1 | 2022年2月1日 | 1|
| 1 | 2022年3月1日 | 2|
| 1 | 2022年4月1日 | 3|
| 1 | 2022年5月1日 | 4|
| 1 | 2022年6月1日 | 5 |
| 1 | 2022年7月1日 | 6|
| 1 | 2022年8月1日 | 7|
| 1 | 2022年10月1日 | 9|
| 1 | 2022年1月13日 | 12|
| 1 | 2022年1月15日 | 14 |
利用具有窗口函数的CTE按用户ID对datelogged进行密集排序，按datelogged对date_trunc(week,datelogged)进行密集排序并按1 对qualify进行密集排序
然后将CTE结果左联接回userid和datelogged上的主表中。
注意：一周开始的日期是Snowflake中的一个参数。因此，确保它是为你认为的一周中的第一天设置的(例如，周日是第一天，或者周一是第一天。(
create or replace table logins(userid int, datelogged date);
INSERT INTO logins(UserID, dateLogged)
VALUES(1, '01/01/2022')
,(1, '01/02/2022')
,(1, '01/03/2022')
,(1, '01/04/2022')
,(1, '01/05/2022')
,(1, '01/06/2022')
,(1, '01/07/2022')
,(1, '01/08/2022')
,(1, '01/10/2022')
,(1, '01/13/2022')
,(1, '01/15/2022');
alter session set WEEK_START = 7; -- Sunday is the first day (this can be set at an account level as well.
-- create a cte to dense rank datelogged by userid, and week
-- qualify by 1 (fist instance of login )
with week_first_login as (
select
userid
, datelogged
, dense_rank() over (partition by userID, date_trunc(week,datelogged) order by datelogged asc) as IsUnique
from logins
qualify IsUnique = 1)
select
l.userid
, l.datelogged
, dayname(l.datelogged) as day_name
, nvl(wfl.IsUnique,0) as IsUnique
from logins l
left join week_first_login wfl
on l.userid = wfl.userid
and l.datelogged = wfl.datelogged
order by userid, datelogged asc;
结果：
| USERID | DATELOGGED | DAY_NAME | ISUNIQUE||
|---|---|---|---|---|
| 1 | 2022-021-01 | 周六 | 1 | |
| 1 | 2022-01-02 | 周日1 | ||
| 1 | 2022-01-03 | 周一0 | ||
| 1 | 2022-01-04 | 周二0 | ||
| 1 | 2022-01-05 | 周三0 | ||
| 1 | 2022-01-06 | 周四0 | ||
| 1 | 2022-021-07 | 周五0 | ||
| 1 | 2022-021-08 | 周六0 | ||
| 1 | 2022-01-10 | 周一 | 1 | |
| 1 | 2022-013 | 周四0 | ||
| 1 | 2022-015 | 周六0 |