Sql windowing to save a record on a given condition

Question

I have some data around the website where the website has different sections of the store, but when the user checks at the end, we only know what the section of the store is, looking for the last click in the section

For example, if I have data that looks like

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

I want to save the last store they went to (where it exists and only when it is in the purchase section of a web page (for example, the page name starts with "buy")

The output I expect is:

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 i.e, , '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

Query:

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

Answer 2

:

create table weblog
(session varchar(10)
,hit_number int
,page varchar(30)
);

INSERT INTO weblog 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, :

SELECT "session"
, hit_number
, page
, CASE 
  WHEN page like 'buy%' THEN 
  max(CASE 
        WHEN page like '%store' THEN page 
        ELSE NULL
      END) OVER (PARTITION BY session ORDER BY hit_number)
  ELSE NULL
  END as last_store
FROM weblog;

( postgres 9.6, ?)

, @SteveKline, .

Answer 3

, " " persistet , . - , ​​ . . , add. , ( , ).

" " . , :

• , " " , , - .
• , " " .

, , , , IMHO , , . . "null". , ( - ) . , . .

If you want it to apply to all existing and future data, a computed column in the view would be a better idea. At least a DBMS can do a decent amount of caching for them. But again, it depends on the DBMS and whether it supports something like representations with computed columns.