String Sequenced Grouping

Question

I have an order table with a column indicating buy or sell, with rows usually ordered by timestamp. What I would like to do is work with consecutive purchase groups and also sell them. egB B S B S B B S -> (B B S) (B S) (B B S)

Example:

order_action |      timestamp      
-------------+---------------------
buy          | 2013-10-03 13:03:02
buy          | 2013-10-08 13:03:02
sell         | 2013-10-10 15:58:02
buy          | 2013-11-01 09:30:02
buy          | 2013-11-01 14:03:02
sell         | 2013-11-07 10:34:02
buy          | 2013-12-03 15:46:02
sell         | 2013-12-09 16:00:03
buy          | 2013-12-11 13:02:02
sell         | 2013-12-18 15:59:03

In the end, I will run the aggregation function (the groups are that I can exclude the whole group based on its sales order), so GROUP BYeither split windows looked like the right way, but I can’t figure out how to get this particular group.

Answer 1

count() :

SELECT *
      ,count(order_action = 'sell' OR NULL) OVER (ORDER BY ts DESC) AS grp
FROM   orders;

ts timestamp . .

count() . order_action = 'sell' OR NULL TRUE "sell" NULL . count() ( ) ( peer of) . .
OVER, "", "". . , .
( !).

: FRAME :

SELECT *
      ,count(order_action = 'sell' OR NULL)
       OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS grp
FROM   orders;

SQL Fiddle, .

Answer 2

PostgreSQL, SQL Fiddle

with sells as (
  select
    rank() over w grp,
    lag(timestamp,1,'2000-01-01') over w sd,
    timestamp td
  from
    orders
  where
    order_action = 'sell'
  window w as (order by timestamp)
)
select
  s.grp,
  o.order_action,
  o.timestamp
from
  orders o
join
  sells s
    on o.timestamp > s.sd
    and o.timestamp <= s.td
order by o.timestamp

, . PostgreSQL, .

Answer 3

, sell . , , . :

select min(timestamp), max(timestamp), sum(case when order_action = 'buy' then 1 else 0 end) as buys
from (select o.*,
             sum(case when order_action = 'sell' then 1 else 0 end) over
                 (order by timestamp desc) as grp
      from orders o
     ) o
group by grp