Gorionovic
Gorionovic

Reputation: 185

Finding previous records after specified one and joining them

We have this table, where players actions are being recorded. I would like to find out where people buy their equipment (Item Bow or Sword). The equipment can be bought in Shop or in Auction (the place can be found in Action column). So when the player buys an item, we need to find Action Shop or Auction (depends on which one of them occured last before an item was bought)

**User       Time        Action         Item** 
  1          12:00       Auction
  2          12:01       Shop
  3          12:04       Shop
  4          12:09       Shop        
  4          12:15       Buy             Bow
  2          12:15       Auction
  2          12:19       Auction
  1          12:25       Chat    
  4          12:33       Auction         
  3          12:47       Chat
  1          12:47       Buy             Sword
  2          12:47       Buy             Bow
  3          12:50       Buy             Sword
  4          12:52       Buy             Bow
  3          12:56       Buy             Bow

The resulut should be

**Time        Item         Place**
 12:15        Bow          Shop
 12:47        Sword        Auction
 12:47        Bow          Auction
 12:50        Sword        Shop
 12:52        Bow          Auction
 12:56        Bow          Shop

I think I migh have a clue how to solve it with cross apply in mssql, but is it possible to solve it without it? I might have to use the query in hive as well. I would be grateful for any answer. Thank you!

Upvotes: 3

Views: 110

Answers (2)

WarrenT
WarrenT

Reputation: 4532

You may want something like this (and I'm guessing you might like user in there as well, eh?)

UPDATED ANSWER thanks to Tim's suggestion

with p as    -- pick purchases
(SELECT [user], [time] purchased, [item]
   FROM actions 
   WHERE [action] = 'Buy'
), e as      -- pick entrances where something can be bought
(SELECT [user], [time] entered, [action] place
   FROM actions
   WHERE [action] IN ('Auction', 'Shop')
), j as      -- join purchases with all prior entrances
(SELECT p.[user], p.[purchased], p.[item],
        e.[entered], e.[place]
   FROM p
   JOIN e   on p.[user]=e.[user]
           and p.[purchased]>=e.[entered]
), r as      -- rank entrance closeness to purchase
(SELECT *, row_number() over( partition by [user],[purchased],[item] 
                              order by [entered] desc ) as rnk
   FROM j
)            -- select only where entrance is the closest
SELECT [user],[purchased],[item],[place]
  FROM r
  WHERE rnk = 1
  order by [user],[purchased],[item]

Caveat: TSQL is not my native dialect ;-)

Upvotes: 1

koriander
koriander

Reputation: 3258

Try:

select time, item, (
  select top 1 Actions.action
  from   Actions
  where  Actions.[User] = buy.[user] and
         Actions.action in ('shop', 'auction') and
         Actions.time < buy.time
  order by Actions.time desc
                   )
from Actions as buy
where action = 'buy'

tested thanks to sqlfiddle provided by Tim Schmelter (great tool, I did not know about!!)

Upvotes: 0

Related Questions