我有一个奇怪的场景要处理。我有如下数据
Col1 col2 col3
a b 201921
a b 201923
a b 201924
a b 201925
a b 201927
Col1和col2等是用于分区的，有很多这样的列。我有一个动态参数，它将在LEAD函数中提供偏移量。
LEAD所做的是对于每一行，它将根据偏移量找到下一个值。但我需要的有点不同。当第一行找到偏移量时，下一行应该跳过偏移量行数。
例如，lead of 201921, 1为201923。所以，下一个计算应该从含有201924的那行开始。然后是201927，并进一步。
我现在写的是
Lead(col3,<dynamic param>,col3) over (partition by col1,col2 order by col3)
是否有这样的事情跳过行并从下一个继续?我有点好奇。
期望输出(偏移量1):
Col1 col2 col3 col4
a b 201921 201923
a b 201923 skip
a b 201924 201925
a b 201925 skip
a b 201927 201927
期望输出(偏移量2):
Col1 col2 col3 col4
a b 201921 201924
a b 201923 skip
a b 201924 skip
a b 201925 201925
a b 201927 201927
您可以使用CASE语句来实现确定COL4的查询。这将是基本查询。<dynamic param>是需要用你的动态参数替换的。
SELECT col1,
col2,
col3,
CASE
WHEN ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3) + <dynamic param> >
COUNT (*) OVER (PARTITION BY col1, col2)
THEN
col3
WHEN MOD (ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3), <dynamic param> + 1) = 1
THEN
LEAD (col3, <dynamic param>) OVER (PARTITION BY col1, col2 ORDER BY col3)
END AS col4
FROM t;
下面是使用您提供的示例的示例
SQL> --offset of 1
SQL> WITH
2 t (col1, col2, col3)
3 AS
4 (SELECT 'a', 'b', 201921 FROM DUAL
5 UNION ALL
6 SELECT 'a', 'b', 201923 FROM DUAL
7 UNION ALL
8 SELECT 'a', 'b', 201924 FROM DUAL
9 UNION ALL
10 SELECT 'a', 'b', 201925 FROM DUAL
11 UNION ALL
12 SELECT 'a', 'b', 201927 FROM DUAL)
13 SELECT col1,
14 col2,
15 col3,
16 CASE
17 WHEN ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3) + 1 >
18 COUNT (*) OVER (PARTITION BY col1, col2)
19 THEN
20 col3
21 WHEN MOD (ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3), 1 + 1) = 1
22 THEN
23 LEAD (col3, 1) OVER (PARTITION BY col1, col2 ORDER BY col3)
24 END AS col4
25 FROM t;
COL1 COL2 COL3 COL4
_______ _______ _________ _________
a b 201921 201923
a b 201923
a b 201924 201925
a b 201925
a b 201927 201927
SQL> --offset of 2
SQL> WITH
2 t (col1, col2, col3)
3 AS
4 (SELECT 'a', 'b', 201921 FROM DUAL
5 UNION ALL
6 SELECT 'a', 'b', 201923 FROM DUAL
7 UNION ALL
8 SELECT 'a', 'b', 201924 FROM DUAL
9 UNION ALL
10 SELECT 'a', 'b', 201925 FROM DUAL
11 UNION ALL
12 SELECT 'a', 'b', 201927 FROM DUAL)
13 SELECT col1,
14 col2,
15 col3,
16 CASE
17 WHEN ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3) + 2 >
18 COUNT (*) OVER (PARTITION BY col1, col2)
19 THEN
20 col3
21 WHEN MOD (ROW_NUMBER () OVER (PARTITION BY col1, col2 ORDER BY col3), 2 + 1) = 1
22 THEN
23 LEAD (col3, 2) OVER (PARTITION BY col1, col2 ORDER BY col3)
24 END AS col4
25 FROM t;
COL1 COL2 COL3 COL4
_______ _______ _________ _________
a b 201921 201924
a b 201923
a b 201924
a b 201925 201925
a b 201927 201927
如果我遵循您的逻辑，您可以使用case表达式和解析row_number()计算只填充col4每偏移行;类似于(以n作为动态值):
select col1, col2, col3,
case when mod(row_number() over (partition by col1, col2 order by col3) + n, n + 1) = 0
then
lead(col3, n) over (partition by col1, col2 order by col3)
end as col4
from your_table
order by col1, col2, col3;
,db<的在小提琴
但是这使得分区最后一行的值为空。根据您的第二个示例，您似乎实际上希望最后的n行始终具有自己的col3值，您可以从lead()确定为空，然后合并:
case when
mod(row_number() over (partition by col1, col2 order by col3) + n, n + 1) = 0
or lead(col3, n) over (partition by col1, col2 order by col3) is null
then
coalesce(lead(col3, n) over (partition by col1, col2 order by col3), col3)
end as col4
,db<的在小提琴
或使用额外的案例分支:
case when
lead(col3, n) over (partition by col1, col2 order by col3) is null
then
col3
when
mod(row_number() over (partition by col1, col2 order by col3) + n, n + 1) = 0
then
lead(col3, n) over (partition by col1, col2 order by col3)
end as col4
,db<的在小提琴
如果col3可以为空，那么您可以始终将分区中最后的n行设置为它们的col3答案，而不是检查前导是否为空:
case when
row_number() over (partition by col1, col2 order by col3 desc) <= n
then
col3
when
mod(row_number() over (partition by col1, col2 order by col3) + n, n + 1) = 0
then
lead(col3, n) over (partition by col1, col2 order by col3)
end as col4
,db<的在小提琴