Such problems encountered in daily work, there is a table test data analysis, sorted by account and transaction time, if the same account tran_date acc time more than ten days, was assigned to a group, if a deal and a deal on no more than ten days, but the subject account has changed, and new assigned to a group, how to portable script, data style is as follows:
The acc tran_date
1111111, 20191201,
1111111, 20191202,
1111111, 20200201,
1111112, 20200101,
1111112, 20200501,

Here, try to use lag function
Tran_date selct acc, lag (acc, 1, 0) over (partity by acc order by acc, tran_date) from the test where the acc!=lag (acc, 1, 0) over (partity by acc order by acc, tran_date), it is an error
Please people

CodePudding user response:

Lag (acc, 1, 0) over (partity by acc order by acc, tran_date) inside partity spelling wrong, partition, selct is select,

In addition the where the acc!=lag (acc, 1, 0) over (partity by acc order by acc, tran_date) cannot directly in the use, should write in the subquery
Select * from
(select acc, tran_date, lag (acc, 1, 0) over (partition by acc order by acc, tran_date) l_tran_date from test) t
Where tran_date!=l_tran_date;

CodePudding user response:

I wrote a create table temp_huichu2 as select c *, the sum (jg) OVER (PARTITION BY NULL ORDER BY rn) zu_no from
(select ROWNUM rn,
B. *,
CASE
The WHEN riqicha & gt; 10 the or riqicha & lt; 0 THEN
1
The ELSE
0
END jg
The from
(select a. *, NVL ((tran_date - lag (tran_date, 1, 0) over (partition by acc order by acc, tran_date asc)), 0) as riqicha from temp_jizihuichu a
C) b)

Select a. *, NVL ((to_date (clr_date, '- dd yyyy - mm) - lag (to_date (clr_date,' - dd yyyy - mm), 1, 0) over (partition by inter_acct order by inter_acct, clr_date asc)), 0) as riqicha from temp_shijiacha_fenzu_ceshi a;
- here is to realize the different account groups, because the inside of the lag parameter into 0 instead of empty, so the difference is big more than ten days, you can compare with the effect of the following statements
Select a. *, NVL ((tran_date - lag (tran_date, 1, 1) over (order by acc, tran_date asc)), 0) as riqicha from temp_shijiacha_fenzu_ceshi a;
Select a. *, NVL ((tran_date - lag (tran_date, 1, 1) over (partition by acc order by acc, tran_date asc)), 0) as riqicha from temp_shijiacha_fenzu_ceshi a;
Select a. *, NVL ((tran_date - lag (tran_date, 1, null) over (order by acc, tran_date asc)), 0) as riqicha from temp_shijiacha_fenzu_ceshi a;
Select a. *, NVL ((tran_date - lag (tran_date, 1, null) over (partition by acc order by acc, tran_date asc)), 0) as riqicha from temp_shijiacha_fenzu_ceshi a;

It can be seen that different new account group is the key to partion by acc and lag in the parameters of the null set

But it took a coincidence, is the use of grouping, subtracting the date will automatically add group is larger than 10 days, rather than to judge according to the account, and then grouping, to judge if the account to group, how to write ah, you can think about it

CodePudding user response:

What do you mean subject account changed?

CodePudding user response:

Table structure didn't stick full, obvious lack of something,

If the subject account will change, the upper and lower time trading is determined by what rules there is no clear