sql - 如何使用 SQL Server 在单行中根据每个用户每周的周末结束日期获取前 2 条记录

Question

我有一个用户事件表，我们在其中捕获用户登录日期。每当用户登录时，都会为该用户记录一个条目以及他/她的电子邮件 ID、访问日期和一些其他信息。

现在我需要在过去 52 周的每个周末(周五)计算该用户的状态。我 已经创建了一个查询来找出过去 52 周的星期五日期或 end_of_week_date。

我想为每个用户一个一个地传递那个日期，以获得小于或等于 end_of_week_date 的第一个访问日期和应该小于第一个访问日期的第二个访问日期。

因为我有 16,000 个用户，所以我想要所有 52 周内每个用户的第一次访问日期和第二次访问日期。

我想在每周结束时一起运行所有这些，这样我只能获得每个用户最近 52 周的快照。

过去 52 周星期五日期查询。

SELECT
    CONVERT(DATE, a.Date_PK) as Week_End_Date, 
    CASE 
        WHEN a.weekno = 2 then  'Week 1' 
        WHEN a.weekno = 3 then  'Week 2' 
        WHEN a.weekno=4 then  'Week 3' 
        WHEN a.weekno=5 then  'Week 4' 
        WHEN a.weekno=6 then  'Week 5' 
        WHEN a.weekno=7 then  'Week 6' 
        WHEN a.weekno=8 then  'Week 7' 
        WHEN a.weekno=9 then  'Week 8' 
        WHEN a.weekno=10 then 'Week 9' 
        WHEN a.weekno=11 then 'Week 10'
        WHEN a.weekno=12 then 'Week 11'
        WHEN a.weekno=13 then 'Week 12'
        WHEN a.weekno=14 then 'Week 13'
        WHEN a.weekno=15 then 'Week 14'
        WHEN a.weekno=16 then 'Week 15'
        WHEN a.weekno=17 then 'Week 16'
        WHEN a.weekno=18 then 'Week 17'
        WHEN a.weekno=19 then 'Week 18'
        WHEN a.weekno=20 then 'Week 19'
        WHEN a.weekno=21 then 'Week 20'
        WHEN a.weekno=22 then 'Week 21'
        WHEN a.weekno=23 then 'Week 22'
        WHEN a.weekno=24 then 'Week 23'
        WHEN a.weekno=25 then 'Week 24'
        WHEN a.weekno=26 then 'Week 25'
        WHEN a.weekno=27 then 'Week 26'
        WHEN a.weekno=28 then 'Week 27'
        WHEN a.weekno=29 then 'Week 28'
        WHEN a.weekno=30 then 'Week 29'
        WHEN a.weekno=31 then 'Week 30'
        WHEN a.weekno=32 then 'Week 31'
        WHEN a.weekno=33 then 'Week 32'
        WHEN a.weekno=34 then 'Week 33'
        WHEN a.weekno=35 then 'Week 34'
        WHEN a.weekno=36 then 'Week 35'
        WHEN a.weekno=37 then 'Week 36'
        WHEN a.weekno=38 then 'Week 37'
        WHEN a.weekno=39 then 'Week 38'
        WHEN a.weekno=40 then 'Week 39'
        WHEN a.weekno=41 then 'Week 40'
        WHEN a.weekno=42 then 'Week 41'
        WHEN a.weekno=43 then 'Week 42'
        WHEN a.weekno=44 then 'Week 43'
        WHEN a.weekno=45 then 'Week 44'
        WHEN a.weekno=46 then 'Week 45'
        WHEN a.weekno=47 then 'Week 46'
        WHEN a.weekno=48 then 'Week 47'
        WHEN a.weekno=49 then 'Week 48'
        WHEN a.weekno=50 then 'Week 49'
        WHEN a.weekno=51 then 'Week 50'
        WHEN a.weekno=52 then 'Week 51'
        WHEN a.weekno=53 then 'Week 52'
        ELSE NULL 
    END AS Week_No 
FROM
    (SELECT 
         DATEPART(week, Date_pk) AS week,
         Date_PK,
         ROW_NUMBER() OVER (ORDER BY date_pk DESC) AS weekno
     FROM  
         dbo.Dim_Date
     WHERE  
         Date_PK BETWEEN DATEADD(Week, -53, GETDATE()) AND GETDATE() 
         AND DATENAME(dw, Date_pk) = 'Friday') a
WHERE 
    a.weekno NOT IN (1)
ORDER BY 
    Week_End_Date DESC

示例数据是这样的:

Emai           First_Name     AccessDate
-----------------------------------------
USER1@GMAIL      ABC          14-02-2019
USER1@GMAIL      ABC          12-02-2019
USER1@GMAIL      ABC          06-02-2019
USER1@GMAIL      ABC          01-02-2019
USER2@GMAIL      CDE          11-01-2019
USER2@GMAIL      CDE          10-02-2019
USER2@GMAIL      CDE          02-02-2019
USER2@GMAIL      CDE          27-01-2019
USER3@GMAIL      EFG          13-02-2019
USER3@GMAIL      EFG          11-02-2019
USER3@GMAIL      EFG          08-02-2019
USER3@GMAIL      EFG          07-02-2019

结果会像下面这样

USER_Email   FIRST_ACCESS_DATE  SECOND_ACCESS_DATE  WEEK_NUMBER    WEEK_END_DATE
---------------------------------------------------------------------------------
USER1@GMAIL      14-02-2019       12-02-2019           WEEK 1       15-02-2019
USER1@GMAIL      06-02-2019       01-02-2019           WEEK 2       08-02-2019
USER2@GMAIL      11-01-2019       10-02-2019           WEEK 1       15-02-2019
USER2@GMAIL      02-02-2019       27-01-2019           WEEK 2       08-02-2019
USER3@GMAIL      13-02-2019       11-02-2019           WEEK 1       15-02-2019
USER3@GMAIL      08-02-2019       07-02-2019           WEEK 2       08-02-2019
USER4@GMAIL      12-02-2019       09-02-2019           WEEK 1       15-02-2019
USER4@GMAIL      07-02-2019       01-02-2019           WEEK 2       08-02-2019

Answer 1

我不明白为什么要将 First_Access_date 和 Second_access_Date 放在不同的列中，如果同一周有更多访问权限怎么办。

--Create Table
CREATE TABLE #TBL(

Email varchar(50),
First_Name varchar(50),
AccessDate DateTime

)

--Insert Values
INSERT INTO #TBL(Email  ,         First_Name  ,   AccessDate)
VALUES
('USER1@GMAIL','ABC','20190214'),
('USER1@GMAIL','ABC','20190212'),
('USER1@GMAIL','ABC','20190206'),
('USER1@GMAIL','ABC','20190201'),
('USER2@GMAIL','CDE','20190111'),
('USER2@GMAIL','CDE','20190210'),
('USER2@GMAIL','CDE','20190202'),
('USER2@GMAIL','CDE','20190127'),
('USER3@GMAIL','EFG','20190213'),
('USER3@GMAIL','EFG','20190211'),
('USER3@GMAIL','EFG','20190208'),
('USER3@GMAIL','EFG','20190207')


--Get User Log info for last 52 week
SELECT Email USER_MAIL,  
       AccessDate ACCESS_DATE, 
       DENSE_RANK() OVER(ORDER BY DATEPART(WW,AccessDate) DESC) WEEK_NUMBER, 
       DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, AccessDate), DATEDIFF(dd, 0, AccessDate))) WEEK_END_DATE
FROM #TBL
WHERE DATEDIFF(WW,AccessDate,GETDATE()) >= 0 AND 
      DATEDIFF(WW,AccessDate,GETDATE()) <= 52
ORDER BY Email,WEEK_NUMBER,AccessDate 

结果

如果您每周只想要前两行:

WITH CTE_1 AS ( SELECT Email USER_MAIL,  
           AccessDate ACCESS_DATE, 
           DENSE_RANK() OVER(ORDER BY DATEPART(WW,AccessDate) DESC) WEEK_NUMBER, 
           DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, AccessDate), DATEDIFF(dd, 0, AccessDate))) WEEK_END_DATE
    FROM #TBL
    WHERE DATEDIFF(WW,AccessDate,GETDATE()) >= 0 AND 
          DATEDIFF(WW,AccessDate,GETDATE()) <= 52
    ) ,CTE_2 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY USER_MAIL,WEEK_NUMBER ORDER BY ACCESS_DATE DESC) as ACCESS_ORDER  FROM CTE_1 )
    SELECT * FROM CTE_2 WHERE ACCESS_ORDER <= 2 ORDER BY     USER_MAIL,WEEK_NUMBER,ACCESS_ORDER 

结果

如果你想显示第一个和第二个值。

WITH CTE_1 AS ( SELECT Email USER_MAIL,  
           AccessDate ACCESS_DATE, 
           DENSE_RANK() OVER(ORDER BY DATEPART(WW,AccessDate) DESC) WEEK_NUMBER, 
           DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, AccessDate), DATEDIFF(dd, 0, AccessDate))) WEEK_END_DATE
    FROM #TBL
    WHERE DATEDIFF(WW,AccessDate,GETDATE()) >= 0 AND 
          DATEDIFF(WW,AccessDate,GETDATE()) <= 52
    ) ,CTE_2 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY USER_MAIL,WEEK_NUMBER ORDER BY ACCESS_DATE DESC) as ACCESS_ORDER  FROM CTE_1 )
    SELECT DISTINCT USER_MAIL,  MIN(ACCESS_DATE) OVER(PARTITION BY USER_MAIL, WEEK_NUMBER) FIRST_ACCESS_DATE, MAX(ACCESS_DATE) OVER(PARTITION BY USER_MAIL, WEEK_NUMBER) SECOND_ACCESS_DATE, WEEK_NUMBER, WEEK_END_DATE FROM CTE_2 WHERE ACCESS_ORDER <= 2 ORDER BY      USER_MAIL,WEEK_NUMBER

• dbfiddle

引用资料

• How to Get First and Last Day of a Week in SQL Server