【 在 nmamtf (nmamtf) 的大作中提到: 】 : The logic is little confused. : On row 3, why new_p1 = 3 ? it should be the pre-row vlaue = 0 right? : Could you double check your logic? Is it related to current row p1 value?
SELECT PRODUCT, SEQ, P1, P2, LAG (P1) OVER (ORDER BY PRODUCT, SEQ) PRE_P1 FROM THEMATRIXDATA ORDER BY PRODUCT, SEQ;
SELECT PRODUCT, SEQ, P1, P2, DECODE (P1, NULL, -1, 0, -1, 1) AS P1_FLAG, -- CHECK IF P1 VALUE IS USABLE DECODE (P2, NULL, -1, 0, -1, 1) AS P2_FLAG, -- CHECK IF P2 VALUE IS USABLE LAG (P1) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P1, LAG (P2) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P2 FROM THEMATRIXDATA ORDER BY PRODUCT, SEQ;
SELECT PRODUCT, SEQ, P1, P2, P1_FLAG, P2_FLAG, PRE_ROW_P1, PRE_ROW_P2, PRE_ROW_P1_FLAG, PRE_ROW_P2_FLAG, DECODE ( P1_FLAG, 1, P1, DECODE (PRE_ROW_P1_FLAG, 1, PRE_ROW_P1, LAG (PRE_ROW_P1) OVER (ORDER BY PRODUCT, SEQ))) AS NEW_P1 FROM ( SELECT PRODUCT, SEQ, P1, P2, DECODE (P1, NULL, -1, 0, -1, 1) AS P1_FLAG, -- CHECK IF P1 VALUE IS USABLE DECODE (P2, NULL, -1, 0, -1, 1) AS P2_FLAG, -- CHECK IF P2 VALUE IS USABLE LAG (P1) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P1, LAG (P2) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P2, DECODE (LAG (P1) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P1_FLAG, DECODE (LAG (P2) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P2_FLAG FROM THEMATRIXDATA ORDER BY PRODUCT, SEQ);
DECODE(PRODUCT_FLAG, 1, P1, DECODE (P1_FLAG,1, P1, DECODE (PRE_ROW_P1_FLAG, 1, PRE_ROW_P1, LAG (PRE_ROW_P1) OVER (ORDER BY PRODUCT, SEQ)))) AS NEW_ P1 FROM ( SELECT PRODUCT, SEQ, P1, P2, DECODE (P1, NULL, -1, 0, -1, 1) AS P1_FLAG,
DECODE (P2, NULL, -1, 0, -1, 1)AS P2_FLAG, LAG (P1) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P1, LAG (P2) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P2, DECODE (LAG (P1) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P1_FLAG, DECODE (LAG (P2) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P2_FLAG, DECODE (LAG (PRODUCT) OVER (ORDER BY PRODUCT, SEQ), PRODUCT, 0, 1) AS PRODUCT_FLAG FROM THEMATRIXDATA ORDER BY PRODUCT, SEQ);
我出题之前就已经做出来了。我的代码比你的简练的多，而且是百分之百的标准SQL， 而且是one single SQL statement。
你这个答案的正确性我现在只能目测，我有所怀疑。因为one single lag function应 该是解决不了问题的。
【 在 nmamtf (nmamtf) 的大作中提到: 】 : This one separated product value. : You can keep working on it. I don't have much time to complete all of them. : SELECT PRODUCT, : SEQ, : P1, : P2, : P1_FLAG, : P2_FLAG, : PRE_ROW_P1, : PRE_ROW_P2, : ...................
【 在 nmamtf (nmamtf) 的大作中提到: 】 : This one separated product value. : You can keep working on it. I don't have much time to complete all of them. : SELECT PRODUCT, : SEQ, : P1, : P2, : P1_FLAG, : P2_FLAG, : PRE_ROW_P1, : PRE_ROW_P2, : ...................
with data(product, seq, p1, p2) as ( select 'A', 1, 0, 5 union all select 'A', 2, 0, 0 union all select 'A', 3, 3, 0 union all select 'A', 4, 3, 0 union all select 'A', 5, 0, 0 union all select 'A', 6, 0, 6 union all select 'A', 7, 0, 0 union all select 'A', 8, 4, 0 union all select 'B', 1, 0, 5 union all select 'B', 2, 0, 0 union all select 'B', 3, 3, 0 union all select 'B', 4, 3, 0 union all select 'B', 5, 0, 0 union all select 'B', 6, 0, 6 union all select 'B', 7, 0, 0 union all select 'B', 8, 4, 0 ), -- get p1, p2 on next row -- t1 as ( select product, seq, p1, p2, min(p1) over (partition by product order by seq rows between 1 following and 1 following) as next_p1, min(p2) over (partition by product order by seq rows between 1 following and 1 following) as next_p2, count(*) over (partition by product) as product_cnt from data ), -- for p1, select all rows where current_p1 is not 0 and next_p1 is 0 -- t2_p1 as ( select product, seq, p1, next_p1, product_cnt from t1 where p1 <> 0 and coalesce(next_p1, 0) = 0 ), -- get the seq and next seq -- t3_p1 as ( select product, seq as seq_begin, min(seq) over (partition by product order by seq rows between 1 following and 1 following) - 1 as seq_end, p1, next_p1, product_cnt from t2_p1 ), -- handle the last record in a product group -- t4_p1 as ( select product, seq_begin, coalesce(seq_end, product_cnt) as seq_end, p1, next_p1 from t3_p1 ), -- repeat the same for p2 -- t2_p2 as ( select product, seq, p2, next_p2, product_cnt from t1 where p2 <> 0 and coalesce(next_p2, 0) = 0 ), t3_p2 as ( select product, seq as seq_begin, min(seq) over (partition by product order by seq rows between 1 following and 1 following) - 1 as seq_end, p2, next_p2, product_cnt from t2_p2 ), t4_p2 as ( select product, seq_begin, coalesce(seq_end, product_cnt) as seq_end, p2, next_p2 from t3_p2 ) select a.product, a.seq, a.p1, a.p2, coalesce(b.p1, a.p1) as new_p1, coalesce(c.p2, a.p2) as new_p2 from data a left join t4_p1 b on a.product = b.product and a.seq between b.seq_begin and b.seq_end left join t4_p2 c on a.product = c.product and a.seq between c.seq_begin and c.seq_end
SELECT X.[PRODUCT],[SEQ],[P2]+ISNULL(P2_Change,0) P2 FROM (SELECT [PRODUCT],[SEQ],[P2] ,[SEQ]-RANK() OVER(PARTITION BY [PRODUCT],[P2] ORDER BY [SEQ])RK_P2 FROM [dbo].[THEMATRIXDATA])X LEFT OUTER JOIN (SELECT * FROM (SELECT A.[PRODUCT] ,A.[SEQ]-RANK() OVER(PARTITION BY A.[PRODUCT],A.[P2] ORDER BY A.[SEQ])RK_P2 ,B.[P2] P2_Change FROM [dbo].[THEMATRIXDATA] A LEFT OUTER JOIN [dbo].[THEMATRIXDATA] B ON A.[PRODUCT]=B.[PRODUCT] AND A.SEQ=B.SEQ+1) Z WHERE [P2_Change]>0) Y ON X.[PRODUCT]=Y.[PRODUCT] AND X.RK_P2=Y.RK_P2 ORDER BY X.[PRODUCT],[SEQ]
前面版上有一个sql题比较难，是把record有效区间根据内容扩展，这样使碎片的有效
区间可以合并成大的有效区间。这个问题还是一个很常见的问题，我后来又遇到过好几次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案，但是比这个神来之笔差不少。现在我自己的答案我已经不记得了，每次都是用这个神来之笔的答案。
最近又碰到一个相关的问题。更难。但是有了前面的基础，应该还是能做出来的。出给大家玩一下。先看一下附图中的数据。
这个数据中有一个product和seq。seq是序号，也可以换成data effective date，就是一个顺序的标志，不一定连续。p1,p2是数据内容。但是内容中有空白，用0表示。不是0的地方才是真正的数据。
要求是写一个sql把空白的内容填上，填入的值，是同一个product前面最近的一个不是空白的值。目标答案在第二张地图，new_p1和new_p2两列。
in oracle is very easy. I wrote one for friend for SQL server. using LAG
function.
用analytic function是对的。这是标准SQL的一部分了吧？Oracle有特殊的方法吗？
【 在 nmamtf (nmamtf) 的大作中提到: 】
: in oracle is very easy. I wrote one for friend for SQL server. using LAG
: function.
没人贴代码？
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难，是把record有效区间根据内容扩展，这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题，我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ，但是比这个神来之笔差不少。现在我自己的答案我已经不记得了，每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础，应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号，也可以换成data effective date，就是
: 一个顺序的标志，不一定连续。p1,p2是数据内容。但是内容中有空白，用0表示。不是
: 0的地方才是真正的数据。
: ...................
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 没人贴代码？
The logic is little confused.
On row 3, why new_p1 = 3 ? it should be the pre-row vlaue = 0 right?
Could you double check your logic? Is it related to current row p1 value?
p1是本来的值，new_p1是填入空白之后的值，将来是用来代替p1的。对，new_p1的值和p1的值有关。
规则是这样的：本来不是空白的地方不动；空白的地方填入前面最近的一个不是空白的值，如果没有，那也不动，还是空白。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: The logic is little confused.
: On row 3, why new_p1 = 3 ? it should be the pre-row vlaue = 0 right?
: Could you double check your logic? Is it related to current row p1 value?
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: p1是本来的值，new_p1是填入空白之后的值，将来是用来代替p1的。对，new_p1的值和
: p1的值有关。
: 规则是这样的：本来不是空白的地方不动；空白的地方填入前面最近的一个不是空白的
: 值，如果没有，那也不动，还是空白。
so, if p1 <> null and p1 <> 0, then new_p1 = p1
else new_p1 = 前面最近的一个不是空白的值
right?
对。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: so, if p1 <> null and p1 <> 0, then new_p1 = p1
: else new_p1 = 前面最近的一个不是空白的值
: right?
CREATE TABLE THEMATRIXDATA
(
PRODUCT VARCHAR2 (1),
SEQ INTEGER,
P1 INTEGER,
P2 INTEGER
);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
1,
0,
5);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
2,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
3,
3,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
4,
3,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
5,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
6,
0,
6);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
7,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('A',
8,
4,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
1,
0,
5);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
2,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
3,
3,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
4,
3,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
5,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
6,
0,
6);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
7,
0,
0);
INSERT INTO THEMATRIXDATA (PRODUCT,
SEQ,
P1,
P2)
VALUES ('B',
8,
4,
0);
COMMIT;
SELECT PRODUCT,
SEQ,
P1,
P2,
LAG (P1) OVER (ORDER BY PRODUCT, SEQ) PRE_P1
FROM THEMATRIXDATA
ORDER BY PRODUCT, SEQ;
SELECT PRODUCT,
SEQ,
P1,
P2,
DECODE (P1, NULL, -1, 0, -1, 1) AS P1_FLAG, -- CHECK IF
P1 VALUE IS USABLE
DECODE (P2, NULL, -1, 0, -1, 1) AS P2_FLAG, -- CHECK IF
P2 VALUE IS USABLE
LAG (P1) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P1,
LAG (P2) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P2
FROM THEMATRIXDATA
ORDER BY PRODUCT, SEQ;
SELECT PRODUCT,
SEQ,
P1,
P2,
P1_FLAG,
P2_FLAG,
PRE_ROW_P1,
PRE_ROW_P2,
PRE_ROW_P1_FLAG,
PRE_ROW_P2_FLAG,
DECODE (
P1_FLAG,
1, P1,
DECODE (PRE_ROW_P1_FLAG,
1, PRE_ROW_P1,
LAG (PRE_ROW_P1) OVER (ORDER BY PRODUCT, SEQ))) AS NEW_P1
FROM ( SELECT PRODUCT,
SEQ,
P1,
P2,
DECODE (P1, NULL, -1, 0, -1, 1)
AS P1_FLAG, -- CHECK IF P1 VALUE IS
USABLE
DECODE (P2, NULL, -1, 0, -1, 1)
AS P2_FLAG, -- CHECK IF P2 VALUE IS
USABLE
LAG (P1) OVER (ORDER BY PRODUCT, SEQ)
AS PRE_ROW_P1,
LAG (P2) OVER (ORDER BY PRODUCT, SEQ)
AS PRE_ROW_P2,
DECODE (LAG (P1) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1)
AS PRE_ROW_P1_FLAG,
DECODE (LAG (P2) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1)
AS PRE_ROW_P2_FLAG
FROM THEMATRIXDATA
ORDER BY PRODUCT, SEQ);
我把幾乎每一步拆分給你。 以免你看不清楚。
最後一步你要自己完成， 呵呵。 那就是， 要是 NEW_P1 是 NULL就設為0 ， 是 0
你要使用上一個非零的值。 用DECODE inline 就可以完成。
另外， 黑色箭頭是涉及 A B 類別的重設， 您自己簡單處理一下
see attached img
Oracle decode function 是非常好用的。 不知道 SQL 有沒有。
代碼僅供參考。我只是想您能容易理解， 因此寫的比較囉嗦。您自己可以簡化很多。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 我把幾乎每一步拆分給你。 以免你看不清楚。
: 最後一步你要自己完成， 呵呵。 那就是， 要是 NEW_P1 是 NULL就設為0 ， 是 0: 你要使用上一個非零的值。 用DECODE inline 就可以完成。
: 另外， 黑色箭頭是涉及 A B 類別的重設， 您自己簡單處理一下
: see attached img
This one separated product value.
You can keep working on it. I don't have much time to complete all of them.
SELECT PRODUCT,
SEQ,
P1,
P2,
P1_FLAG,
P2_FLAG,
PRE_ROW_P1,
PRE_ROW_P2,
PRE_ROW_P1_FLAG,
PRE_ROW_P2_FLAG,
PRODUCT_FLAG,
DECODE(PRODUCT_FLAG, 1, P1, DECODE (P1_FLAG,1, P1,
DECODE (PRE_ROW_P1_FLAG,
1, PRE_ROW_P1,
LAG (PRE_ROW_P1) OVER (ORDER BY PRODUCT, SEQ)))) AS NEW_
P1
FROM ( SELECT PRODUCT,
SEQ,
P1,
P2,
DECODE (P1, NULL, -1, 0, -1, 1) AS P1_FLAG,
DECODE (P2, NULL, -1, 0, -1, 1)AS P2_FLAG,
LAG (P1) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P1,
LAG (P2) OVER (ORDER BY PRODUCT, SEQ) AS PRE_ROW_P2,
DECODE (LAG (P1) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P1_FLAG,
DECODE (LAG (P2) OVER (ORDER BY PRODUCT, SEQ), 0, -1, 1) AS PRE_ROW_P2_FLAG,
DECODE (LAG (PRODUCT) OVER (ORDER BY PRODUCT, SEQ), PRODUCT, 0, 1) AS PRODUCT_FLAG
FROM THEMATRIXDATA
ORDER BY PRODUCT, SEQ);
我出题之前就已经做出来了。我的代码比你的简练的多，而且是百分之百的标准SQL，
而且是one single SQL statement。
你这个答案的正确性我现在只能目测，我有所怀疑。因为one single lag function应
该是解决不了问题的。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: This one separated product value.
: You can keep working on it. I don't have much time to complete all of them.
: SELECT PRODUCT,
: SEQ,
: P1,
: P2,
: P1_FLAG,
: P2_FLAG,
: PRE_ROW_P1,
: PRE_ROW_P2,
: ...................
我寫的複雜時怕你看不懂， 卻不知道你這是閒著沒事幹逗大家玩？！
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 我出题之前就已经做出来了。我的代码比你的简练的多，而且是百分之百的标准SQL，
: 而且是one single SQL statement。
: 你这个答案的正确性我现在只能目测，我有所怀疑。因为one single lag function应
: 该是解决不了问题的。
我验证了，你的不对。你的答案只填了连续的两个空白，如果空白超过两个，你的还是空白。
空白数目不定，任何固定个数的用法都不能解决问题。
我手上没有oracle，但是decode function可以用case when改写。附图是你最后一个
sql的改写和结果。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: This one separated product value.
: You can keep working on it. I don't have much time to complete all of them.
: SELECT PRODUCT,
: SEQ,
: P1,
: P2,
: P1_FLAG,
: P2_FLAG,
: PRE_ROW_P1,
: PRE_ROW_P2,
: ...................
这怎么是逗大家玩？程序员切磋算法和语言，以码会友，有毛病吗？
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 我寫的複雜時怕你看不懂， 卻不知道你這是閒著沒事幹逗大家玩？！
呵呵， 沒毛病， 是我自己賤。
其實， 更簡單， 而且靈活的方法可以不用嵌套表 AND LAG， 一步就得到理想的值。 因為在數據量超大的情況下， LAG 會造成速度下降的問題。嵌套的 LAG 會問題更大。可惜你不是實際需要， 就沒必要深入說了。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 这怎么是逗大家玩？程序员切磋算法和语言，以码会友，有毛病吗？
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 我验证了，你的不对。你的答案只填了连续的两个空白，如果空白超过两个，你的还是空白。
你這人不好好看帖子。 我樓上已經告訴你：
『最後一步你要自己完成， 呵呵。 那就是， 要是 NEW_P1 是 NULL就設為0 ， 是 0 你要使用上一個非零的值。 用DECODE inline 就可以完成。』
並附上圖， 用箭頭標注出你需要自己完成的部分。
: 空白数目不定，任何固定个数的用法都不能解决问题。
: 我手上没有oracle，但是decode function可以用case when改写。附图是你最后一个: sql的改写和结果。
哦。那我也不好意思啊。我原贴说的是出个题大家玩一下，并没有说请人帮忙，不过我话说的太软，可能给你的印象是求人帮忙，看到你第一个回贴我估计你是这么想的，但是我也没澄清，不过你后来说话有点居高临下了，所以我就没客气。这里有误会。
我贴我的算法吧。
用的是两个row_number函数相减的方法，得到内容的grouping，同时把每一个grouping中第一个值得到，用的是lead desc，然后再用first_value把这个值spread出去。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 呵呵， 沒毛病， 是我自己賤。
: 其實， 更簡單， 而且靈活的方法可以不用嵌套表 AND LAG， 一步就得到理想的值
。
: 因為在數據量超大的情況下， LAG 會造成速度下降的問題。嵌套的 LAG 會問題更大。
: 可惜你不是實際需要， 就沒必要深入說了。
你这样说我觉得不行。decode inline完成。怎么完成？空白的数目不定，这里必须有
一个相当于循环的东西。你完成一下看看嘛。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 是空白。
: 你這人不好好看帖子。 我樓上已經告訴你：
: 『最後一步你要自己完成， 呵呵。 那就是， 要是 NEW_P1 是 NULL就設為0 ， 是 0
: 你要使用上一個非零的值。 用DECODE inline 就可以完成。』
: 並附上圖， 用箭頭標注出你需要自己完成的部分。
呵呵， 我的代碼僅僅是說明一個邏輯判斷過程。
對你的數據， inline 就夠了。 對不定數據， 用我上面說的， 簡單分區就可以了， 連 LAG 都不用。 不過， 初學恐怕不能理解。
要是用 Oracle MODEL 和 DIMENSION BY 和 RULES 就更簡單。
不過我對 SQL server 所知甚少， 不知道它有沒有類似的功能。
Hint is here:
P1[ANY] = DECODE(P1[CV(RN)], 0, NVL(P1[CV(RN) - 1], 0), P1[CV(RN)]),
Can you convert above into SQL ? 呵呵
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你这样说我觉得不行。decode inline完成。怎么完成？空白的数目不定，这里必须有
: 一个相当于循环的东西。你完成一下看看嘛。
: 0
又想了一下，这个问题实际上没有本版前面出现的那个问题难。用两个row_number相减的技巧当然也可以，但是实际上是over kill。用普通的区间的办法就可以解决。见附
图。
考虑这个问题和本版前面那个问题的区别，这个问题是要解决一个特殊值，就是空白。而那个问题每个值都可能形成小的区间。所以这个问题实际上没有那个问题难。
这几天在写一个sql，刚好碰到这个问题，row_number相减的技巧实在忍不住不用。不
过我实际问题中比这个复杂，单线索order by还不行，所以我又改了，改成了目前的做法。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难，是把record有效区间根据内容扩展，这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题，我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ，但是比这个神来之笔差不少。现在我自己的答案我已经不记得了，每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础，应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号，也可以换成data effective date，就是
: 一个顺序的标志，不一定连续。p1,p2是数据内容。但是内容中有空白，用0表示。不是
: 0的地方才是真正的数据。
: ...................
SQL Server里没有这些。这些不是函数，是关键字，改不了。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 呵呵， 我的代碼僅僅是說明一個邏輯判斷過程。
: 對你的數據， inline 就夠了。 對不定數據， 用我上面說的， 簡單分區就可以了
，
: 連 LAG 都不用。 不過， 初學恐怕不能理解。
: 要是用 Oracle MODEL 和 DIMENSION BY 和 RULES 就更簡單。
: 不過我對 SQL server 所知甚少， 不知道它有沒有類似的功能。
: Hint is here:
: P1[ANY] = DECODE(P1[CV(RN)], 0, NVL(P1[CV(RN) - 1], 0), P1[CV(RN)]),
: Can you convert above into SQL ? 呵呵
ORACLE 很簡單。 一句話就OK 了。 比如， 對於單一產品 A，
你比較一下， 看看那個簡單？
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: SQL Server里没有这些。这些不是函数，是关键字，改不了。
: ，
不错。谢谢。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: ORACLE 很簡單。 一句話就OK 了。 比如， 對於單一產品 A，
: 你比較一下， 看看那個簡單？
with data(product, seq, p1, p2) as (
select 'A', 1, 0, 5
union all select 'A', 2, 0, 0
union all select 'A', 3, 3, 0
union all select 'A', 4, 3, 0
union all select 'A', 5, 0, 0
union all select 'A', 6, 0, 6
union all select 'A', 7, 0, 0
union all select 'A', 8, 4, 0
union all select 'B', 1, 0, 5
union all select 'B', 2, 0, 0
union all select 'B', 3, 3, 0
union all select 'B', 4, 3, 0
union all select 'B', 5, 0, 0
union all select 'B', 6, 0, 6
union all select 'B', 7, 0, 0
union all select 'B', 8, 4, 0
),
-- get p1, p2 on next row --
t1 as
(
select product, seq,
p1, p2,
min(p1) over (partition by product order by seq rows between 1
following and 1 following) as next_p1,
min(p2) over (partition by product order by seq rows between 1
following and 1 following) as next_p2,
count(*) over (partition by product) as product_cnt
from data
),
-- for p1, select all rows where current_p1 is not 0 and next_p1 is 0 --
t2_p1 as
(
select product, seq, p1, next_p1, product_cnt
from t1
where p1 <> 0 and coalesce(next_p1, 0) = 0
),
-- get the seq and next seq --
t3_p1 as
(
select product, seq as seq_begin,
min(seq) over (partition by product order by seq rows between 1
following and 1 following) - 1 as seq_end,
p1, next_p1, product_cnt
from t2_p1
),
-- handle the last record in a product group --
t4_p1 as
(
select product, seq_begin,
coalesce(seq_end, product_cnt) as seq_end,
p1, next_p1
from t3_p1
),
-- repeat the same for p2 --
t2_p2 as
(
select product, seq, p2, next_p2, product_cnt
from t1
where p2 <> 0 and coalesce(next_p2, 0) = 0
),
t3_p2 as
(
select product, seq as seq_begin,
min(seq) over (partition by product order by seq rows between 1
following and 1 following) - 1 as seq_end,
p2, next_p2, product_cnt
from t2_p2
),
t4_p2 as
(
select product, seq_begin,
coalesce(seq_end, product_cnt) as seq_end,
p2, next_p2
from t3_p2
)
select a.product, a.seq, a.p1, a.p2,
coalesce(b.p1, a.p1) as new_p1,
coalesce(c.p2, a.p2) as new_p2
from data a
left join t4_p1 b
on a.product = b.product and a.seq between b.seq_begin and b.seq_end
left join t4_p2 c
on a.product = c.product and a.seq between c.seq_begin and c.seq_end
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 不错。谢谢。
比较啰嗦，思路还是比较简单的：
1. 用 rows 1 following and 1 following 去取同组的下一个
2. 用 当前p非空，下个p空找到所有准备当 new_p的
3. 想办法凑 seq 的区间
4. join，如果原表seq落在区间内则取代空的
还有一个思路就是用不等于去join，A的seq <= B 的 seq 且 A.p 非空且 B.p 空， 然后用analytic function取最接近的一个。
你这个没有处理好区间的结尾。
【 在 mario (Mario) 的大作中提到: 】
: 比较啰嗦，思路还是比较简单的：
: 1. 用 rows 1 following and 1 following 去取同组的下一个
: 2. 用 当前p非空，下个p空找到所有准备当 new_p的
: 3. 想办法凑 seq 的区间
: 4. join，如果原表seq落在区间内则取代空的
: 还有一个思路就是用不等于去join，A的seq
給你一個也許 SQL 能做的方法。 假如還是沒有相應的方法， 只能說 SQL server 太
爛。
--剛剛查了一下， SQL server 有這個功能。 也許這是比較簡單的解決方法。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难，是把record有效区间根据内容扩展，这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题，我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ，但是比这个神来之笔差不少。现在我自己的答案我已经不记得了，每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础，应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号，也可以换成data effective date，就是
: 一个顺序的标志，不一定连续。p1,p2是数据内容。但是内容中有空白，用0表示。不是
: 0的地方才是真正的数据。
: ...................
nvl和nullif是函数，可以改成sql server。
SQL Server 没有ignore nulls，这是一个关键词，改不了。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 給你一個也許 SQL 能做的方法。 假如還是沒有相應的方法， 只能說 SQL server 太
: 爛。
: --剛剛查了一下， SQL server 有這個功能。 也許這是比較簡單的解決方法。
儘管這裡有一個關於轉化的信息。 但是， 一個簡單的 QUERY 改成那個樣子， 就沒什麼意義了。
https://it.toolbox.com/blogs/ganotedp/sql-server-2012-equivalent-to-first-
value-ignore-nulls-031317
SQL server 比甲骨文還是差了很多。 甲骨文的模塊query 可以處理非常複雜的
的運算. 相比之下， SQL server 不知道有什麼相應的產品。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: nvl和nullif是函数，可以改成sql server。
: SQL Server 没有ignore nulls，这是一个关键词，改不了。
本贴是几乎一样的问题。
oracle功能是多一些，但很多不是标准SQL，用了就lock in了。
【 在 nmamtf (nmamtf) 的大作中提到: 】
: 儘管這裡有一個關於轉化的信息。 但是， 一個簡單的 QUERY 改成那個樣子， 就沒什
: 麼意義了。
: https://it.toolbox.com/blogs/ganotedp/sql-server-2012-equivalent-to-first-: value-ignore-nulls-031317
: SQL server 比甲骨文還是差了很多。 甲骨文的模塊query 可以處理非常複雜的
: 的咚. 相比之下， SQL server 不知道有什麼相應的產品。
这个问题很自然的有两个部分：第一部分是本题，也就是把数值延伸到空白的地方去。第二部分就是本版前面那个问题，把record按照内容合并，这是一个化简table的步骤
，也可以叫normalize。
所以两个问题是相关的。两个row_number相减虽然是神来之笔，但是本着小步走的原则，拆成两步，第一步把每个row_number命名，第二步把两个相减用作grouping，这样概念连续，结构更清晰。来看一下代码。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难，是把record有效区间根据内容扩展，这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题，我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ，但是比这个神来之笔差不少。现在我自己的答案我已经不记得了，每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础，应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号，也可以换成data effective date，就是
: 一个顺序的标志，不一定连续。p1,p2是数据内容。但是内容中有空白，用0表示。不是
: 0的地方才是真正的数据。
: ...................
new_p1好像是跟上一个new_p1和P1有没有值有关系
new_p1是p1把空白填上之后的值：如果p1是0（表示空白）那么new_p1等于前面最近的
一个p1非零p1值。
【 在 smallburrito (smallburrito) 的大作中提到: 】
: new_p1好像是跟上一个new_p1和P1有没有值有关系
一个query可以搞定，下面是P2的,P1的逻辑一样，我懒的写了。
SELECT
X.[PRODUCT],[SEQ],[P2]+ISNULL(P2_Change,0) P2
FROM
(SELECT [PRODUCT],[SEQ],[P2]
,[SEQ]-RANK() OVER(PARTITION BY [PRODUCT],[P2] ORDER BY [SEQ])RK_P2
FROM [dbo].[THEMATRIXDATA])X
LEFT OUTER JOIN
(SELECT * FROM
(SELECT A.[PRODUCT]
,A.[SEQ]-RANK() OVER(PARTITION BY A.[PRODUCT],A.[P2] ORDER BY A.[SEQ])RK_P2
,B.[P2] P2_Change
FROM [dbo].[THEMATRIXDATA] A
LEFT OUTER JOIN [dbo].[THEMATRIXDATA] B
ON A.[PRODUCT]=B.[PRODUCT] AND A.SEQ=B.SEQ+1) Z
WHERE [P2_Change]>0) Y
ON X.[PRODUCT]=Y.[PRODUCT] AND X.RK_P2=Y.RK_P2
ORDER BY X.[PRODUCT],[SEQ]
可以。
这些属于range处理的问题，在通用编程语言中也常见，据说属于leecode中中等难度题。
【 在 BlueTigerBL (tiger) 的大作中提到: 】
: 一个query可以搞定，下面是P2的,P1的逻辑一样，我懒的写了。
: SELECT
: X.[PRODUCT],[SEQ],[P2]+ISNULL(P2_Change,0) P2
: FROM
: (SELECT [PRODUCT],[SEQ],[P2]
: ,[SEQ]-RANK() OVER(PARTITION BY [PRODUCT],[P2] ORDER BY [SEQ])RK_P2
: FROM [dbo].[THEMATRIXDATA])X
: LEFT OUTER JOIN
: (SELECT * FROM
: (SELECT A.[PRODUCT]
: ...................
No window functions needed.
I cannot run your sql in sqlite manager.
You can use the chrome sqlite plugin to reproduce the error:
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 我出题之前就已经做出来了。我的代码比你的简练的多，而且是百分之百的标准SQL，
: 而且是one single SQL statement。
: 你这个答案的正确性我现在只能目测，我有所怀疑。因为one single lag function应
: 该是解决不了问题的。
可以。
window function的确可以完全用join实现。逻辑上会复杂一点。
【 在 webbew (未必) 的大作中提到: 】
: No window functions needed.
你是用这个代码吗？
http://www.mitbbs.com/article/Database/31191511_0.html
我写的时候是用SQL Server的，但是这都是标准SQL，刚才我又重新敲一遍，用sqlite
也可以执行啊，没有error。
我把它放在codeshare了，你可以copy一下。https://codeshare.io/5ZlYpN
【 在 shuaide (卖火柴的小女孩) 的大作中提到: 】
: I cannot run your sql in sqlite manager.
: You can use the chrome sqlite plugin to reproduce the error:
SQLite 执行结果是这样的。
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你是用这个代码吗？
: http://www.mitbbs.com/article/Database/31191511_0.html
: 我写的时候是用SQL Server的，但是这都是标准SQL，刚才我又重新敲一遍，用
sqlite
: 也可以执行啊，没有error。
: 我把它放在codeshare了，你可以copy一下。
: https://codeshare.io/5ZlYpN