时间戳

首页 > 解决方案 > Postgresql Window函数获取账户登录和账户切换行为

sql - Postgresql Window函数获取账户登录和账户切换行为

问题描述

假设表格为下面的 DDL。有没有办法通过窗口函数得到下面的SQL查询结果？

有关输出的更多详细信息：尝试获取某个帐户在某个浏览器上的第一次登录时间，直到另一个帐户稍后登录。换句话说，尝试捕获在 2020 年 1 月 1 日登录的“e12345”然后在 2020 年 1 月 3 日在同一浏览器上登录的“e67890”。然后在 2020 年 1 月 5 日，“e12345”再次在浏览器“123”上登录。基本上从原始数据库中删除第 2 行和第 4 行。

create table timeline_log_in
    (date text,
    account_id text,
    browser_cookie_id text
    );

insert into timeline_log_in
    values
        ('2020-1-1','e12345','123'),
        ('2020-1-2','e12345','123'),
        ('2020-1-3','e67890','123'),
        ('2020-1-4','e67890','123'),
        ('2020-1-5','e12345','123'),
        ('2020-1-5','e67890','456'),
        ('2020-1-6','e12345','789');

期望的输出：

-----------------------------------------
browser_cookie_id | account_id | date   |
-----------------------------------------
123               |e12345      |2020-1-1|
123               |e67890      |2020-1-3|
123               |e12345      |2020-1-5|
456               |e67890      |2020-1-5|
789               |e12345      |2020-1-6|
-----------------------------------------

标签： sqlpostgresqlwindow-functions

解决方案

您似乎正在基于account_id和定义组browser_cookie_id。然后，您只需要第一行。这表明lag()：

select tli.*
from (select tli.*,
             lag(date) over (partition by account_id, browser_cookie_id order by date) as prev_group_date,
             lag(date) over (order by date) as prev_date
      from timeline_log_in tli
     ) tli
where prev_group_date is null or prev_group_date <> prev_date;

这是一个 db<>fiddle。

推荐阅读