Reputation: 185
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
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
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