时间戳

首页 > 解决方案 > 计算相对于当前行按天分组的每一行的先前登录

sql - 计算相对于当前行按天分组的每一行的先前登录

问题描述

目标：我想收集按天分组的每个用户的登录信息。

问题：我正在努力计算最后一列的函数，该函数是相对于登录列的当前行的最后一次登录（有点像滞后函数，但不知道如何使用它）。问题是我只需要显示过去三个月的登录信息，那么如果我在过去三个月设置 where 条件，它将如何计算下表中 days_last_login 列的第五次观察？：

期望的输出：

+----+---------------------+-----------------+
| id |        login        | days_last_login |
+----+---------------------+-----------------+
|  1 | 2018-12-10 05:00:00 |               5 |
|  1 | 2018-12-07 05:30:00 |               3 |
|  1 | 2018-12-01 05:30:00 |               6 |
|  2 | 2019-08-01 05:30:00 |               7 |
|  2 | 2019-01-01 05:30:00 |             365 |
+----+---------------------+-----------------+

当前查询：

    SELECT id
        ,YEAR(login) as yr, MONTH(login) as mm, DAY(login) as dd
        ,CAST(login AS DATE) as logins
        ,FUNCTION FOR DAYS_LAST_LOGIN
    FROM database.table 
    WHERE login > DATEADD(month,-3,getdate())
    GROUP BY YEAR(login), MONTH(login), DAY(login), id
    ORDER BY id, yr desc, mm desc, dd desc

注意： 我省略了在表格中显示年、月和日列以使其更清楚。

标签： sqlsql-server

解决方案

据我所知，逻辑是从给定登录日期到下一个登录日期的天数，大概是最近日期到当前日期为止。

这表明这样的查询：

SELECT id, CONVERT(date, login) as dte,
    DATEDIFF(day, login, LEAD(MAX(login), 1, GETDATE()) OVER (PARTITION BY id)) as DAYS_LAST_LOGIN
FROM database.table 
WHERE login > DATEADD(month, -3, getdate())
GROUP BY id, CONVERT(date, login)
ORDER BY id, CONVERT(date, login) DESC;

我删除了日期部分，因为我觉得它们没有用，但你当然可以包括它们。

推荐阅读