sql 窗口化以在给定条件下保留记录

Question

我在一个网站上有一些数据，该网站有不同的商店部分，但是当用户最后结账时，我们只能通过查找他们最近点击的部分来知道它是哪个商店部分

例如，如果我的数据看起来像

session, hit_number, page
a,1,homepage
a,2,generic_page
a,3,shoe_store,
a,4,buy_add_basket
a,5,buy_checkout
b,1,sock_store
b,2,shoe_store,
b,3,buy_add_to_basket
b,4,buy_checkout
c,1,homepage
c,2,sock_store
c,3,sock_store
c,4,buy_add_to_basket
c,5,home_page
c,6,shoe_store
a,5,home_page

我想保留他们去的最后一家商店(存在的地方并且仅当他们在网页的购买部分时(即页面名称以“购买”开头)

我期待的输出是:

session, hit_number, page
a,1,homepage,null
a,2,generic_page,null
a,3,shoe_store,null
a,4,buy_add_basket,shoe_store
a,5,buy_checkout,shoe_store
b,1,sock_store,null
b,2,shoe_store,null
b,3,buy_add_to_basket,shoe_store
b,4,buy_checkout,shoe_store
c,1,homepage,null
c,2,sock_store,null
c,3,sock_store,null
c,4,buy_add_to_basket,sock_store,
c,5,home_page,null
c,6,shoe_store,null
a,5,home_page,null

Answer 1

我在 SQL Server 中执行了此操作，但是获取结果的查询在大多数供应商中都可以使用。它背后的逻辑是检查何时 page列包含 'buy'然后得到min值，即上面页面名称包含 'store' 的值

创建并填充表:

DECLARE @table TABLE
(
    session    VARCHAR(1),
    hit_number INT,
    page       VARCHAR(50)
);
INSERT INTO @table VALUES 
('a',1,'homepage'),
('a',2,'generic_page'),
('a',3,'shoe_store'),
('a',4,'buy_add_basket'),
('a',5,'buy_checkout'),
('b',1,'sock_store'),
('b',2,'shoe_store'),
('b',3,'buy_add_to_basket'),
('b',4,'buy_checkout'),
('c',1,'homepage'),
('c',2,'sock_store'),
('c',3,'sock_store'),
('c',4,'buy_add_to_basket'),
('c',5,'home_page'),
('c',6,'shoe_store'),
('a',5,'home_page');

Select * From @table将给出以下结果:

session hit_number  page
a       1           homepage
a       2           generic_page
a       3           shoe_store
a       4           buy_add_basket
a       5           buy_checkout
b       1           sock_store
b       2           shoe_store
b       3           buy_add_to_basket
b       4           buy_checkout
c       1           homepage
c       2           sock_store
c       3           sock_store
c       4           buy_add_to_basket
c       5           home_page
c       6           shoe_store
a       5           home_page

询问:

SELECT
    session,
    hit_number,
    page,
    CASE
        WHEN page LIKE 'buy%'
        THEN MIN(CASE
                     WHEN page LIKE '%store'
                     THEN page
                     ELSE NULL
                 END) OVER(PARTITION BY session ORDER BY hit_number)
        ELSE NULL
    END AS previous_buy_page
FROM @table;

返回所需的结果:

session hit_number  page                previous_buy_page
a       1           homepage            NULL
a       2           generic_page        NULL
a       3           shoe_store          NULL
a       4           buy_add_basket      shoe_store
a       5           buy_checkout        shoe_store
a       5           home_page           NULL
b       1           sock_store          NULL
b       2           shoe_store          NULL
b       3           buy_add_to_basket   shoe_store
b       4           buy_checkout        shoe_store
c       1           homepage            NULL
c       2           sock_store          NULL
c       3           sock_store          NULL
c       4           buy_add_to_basket   sock_store
c       5           home_page           NULL
c       6           shoe_store          NULL