Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Create procedure with variables, temp tables, while loop, etc.
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Comments
create table ashley(id int, login_dt timestamp);
data: Distinct days per id (id=2 has gap: no 2013-09-23, so seq braked) daniel=> select id, date(login_dt) from ashley group by id, date(login_dt) order by id, date(login_dt); id | date ----+------------ 1 | 2013-09-22 1 | 2013-09-23 1 | 2013-09-24 2 | 2013-09-22 2 | 2013-09-24 2 | 2013-09-25 (6 rows) Create sequence daniel=> select id, date, 1 + CONDITIONAL_TRUE_EVENT(date - lg = 1) over (partition by id order by date) seq from (select id, date(login_dt), lag(date(login_dt)) over (partition by id order by date(login_dt)) lg from ashley group by id, date(login_dt) order by id, date(login_dt)) foo; id | date | seq ----+------------+----- 1 | 2013-09-22 | 1 1 | 2013-09-23 | 2 1 | 2013-09-24 | 3 2 | 2013-09-22 | 1 2 | 2013-09-24 | 1 2 | 2013-09-25 | 2 (6 rows)