原始数据:
edw=# select * from ods.my_test2;
i_a | i_b | i_c
-----+-----+--------
b | 5 | 201501
b | 5 | 201502
b | 6 | 201503
a | 1 | 201502
a | 1 | 201503
a | 1 | 201508
a | 2 | 201505
a | 2 | 201507
a | 2 | 201506
a | 1 | 201509
a | 1 | 201501
a | 1 | 201501
SELECT
m.*,
case
when lag(m.i_a) over(PARTITION by m.i_a ORDER BY m.i_c) = m.i_a and lag(m.i_b) over(PARTITION by m.i_a order by m.i_c)=m.i_b
then case
when m.id=min(m.id) over(PARTITION by m.i_a ORDER BY m.i_c) then id else NULL
end else id end flag
FROM (select i_a,i_b,i_c,row_number() over(ORDER BY i_c) as id from ods.my_test2 ) m
结果
i_a | i_b | i_c | id | flag
-----+-----+--------+----+------
a | 1 | 201501 | 3 | 3
a | 1 | 201501 | 2 | 2
a | 1 | 201502 | 4 |
a | 1 | 201503 | 7 |
a | 2 | 201505 | 8 | 8
a | 2 | 201506 | 9 |
a | 2 | 201507 | 10 |
a | 1 | 201508 | 11 | 11
a | 1 | 201509 | 12 |
b | 5 | 201501 | 1 | 1
b | 5 | 201502 | 5 |
b | 6 | 201503 | 6 | 6
从语句上来说得出这个结果是有问题的,第一行数据的flag应该是空的,不应该是3
将子查询变成实表
create table ods.my_test as select i_a,i_b,i_c,row_number() over(ORDER BY i_c) as id from ods.my_test2;
再从实表中查询
SELECT
m.*,
case
when lag(m.i_a) over(PARTITION by m.i_a ORDER BY m.i_c) = m.i_a and lag(m.i_b) over(PARTITION by m.i_a order by m.i_c)=m.i_b
then case
when m.id=min(m.id) over(PARTITION by m.i_a ORDER BY m.i_c) then id else NULL
end else id end flag
FROM ods.my_test m
结果:
i_a | i_b | i_c | id | flag
-----+-----+--------+----+------
a | 1 | 201501 | 2 | 2
a | 1 | 201501 | 3 |
a | 1 | 201502 | 4 |
a | 1 | 201503 | 7 |
a | 2 | 201505 | 8 | 8
a | 2 | 201506 | 9 |
a | 2 | 201507 | 10 |
a | 1 | 201508 | 11 | 11
a | 1 | 201509 | 12 |
b | 5 | 201501 | 1 | 1
b | 5 | 201502 | 5 |
b | 6 | 201503 | 6 | 6
这个结果和上面的结果有差异,这种情况和oracle的查询转换很相似。但在GP中没有找到oracle的/*+NO_MERGE(m)*/功能,只能在存储过程中将这个子查询用临时表去解决了!
edw=# select * from ods.my_test2;
i_a | i_b | i_c
-----+-----+--------
b | 5 | 201501
b | 5 | 201502
b | 6 | 201503
a | 1 | 201502
a | 1 | 201503
a | 1 | 201508
a | 2 | 201505
a | 2 | 201507
a | 2 | 201506
a | 1 | 201509
a | 1 | 201501
a | 1 | 201501
SELECT
m.*,
case
when lag(m.i_a) over(PARTITION by m.i_a ORDER BY m.i_c) = m.i_a and lag(m.i_b) over(PARTITION by m.i_a order by m.i_c)=m.i_b
then case
when m.id=min(m.id) over(PARTITION by m.i_a ORDER BY m.i_c) then id else NULL
end else id end flag
FROM (select i_a,i_b,i_c,row_number() over(ORDER BY i_c) as id from ods.my_test2 ) m
结果
i_a | i_b | i_c | id | flag
-----+-----+--------+----+------
a | 1 | 201501 | 3 | 3
a | 1 | 201501 | 2 | 2
a | 1 | 201502 | 4 |
a | 1 | 201503 | 7 |
a | 2 | 201505 | 8 | 8
a | 2 | 201506 | 9 |
a | 2 | 201507 | 10 |
a | 1 | 201508 | 11 | 11
a | 1 | 201509 | 12 |
b | 5 | 201501 | 1 | 1
b | 5 | 201502 | 5 |
b | 6 | 201503 | 6 | 6
从语句上来说得出这个结果是有问题的,第一行数据的flag应该是空的,不应该是3
将子查询变成实表
create table ods.my_test as select i_a,i_b,i_c,row_number() over(ORDER BY i_c) as id from ods.my_test2;
再从实表中查询
SELECT
m.*,
case
when lag(m.i_a) over(PARTITION by m.i_a ORDER BY m.i_c) = m.i_a and lag(m.i_b) over(PARTITION by m.i_a order by m.i_c)=m.i_b
then case
when m.id=min(m.id) over(PARTITION by m.i_a ORDER BY m.i_c) then id else NULL
end else id end flag
FROM ods.my_test m
结果:
i_a | i_b | i_c | id | flag
-----+-----+--------+----+------
a | 1 | 201501 | 2 | 2
a | 1 | 201501 | 3 |
a | 1 | 201502 | 4 |
a | 1 | 201503 | 7 |
a | 2 | 201505 | 8 | 8
a | 2 | 201506 | 9 |
a | 2 | 201507 | 10 |
a | 1 | 201508 | 11 | 11
a | 1 | 201509 | 12 |
b | 5 | 201501 | 1 | 1
b | 5 | 201502 | 5 |
b | 6 | 201503 | 6 | 6
这个结果和上面的结果有差异,这种情况和oracle的查询转换很相似。但在GP中没有找到oracle的/*+NO_MERGE(m)*/功能,只能在存储过程中将这个子查询用临时表去解决了!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29989552/viewspace-2125249/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29989552/viewspace-2125249/