database - 如何将三个选择查询合并为一个，以在 Oracle 中实现如下所需的结果？

Question

目的是在每个项目的计划增量和实际增量的第一个负值出现上分别推送“N”标志。'Y' 标志应推入所有其余行。Rank 列定义顺序，Project ID 定义一个项目的数据。

只能使用选择查询

使用下表结构'''

CREATE TABLE payBackTable (projectID varchar2(10), quarterYear varchar2(10), Rank int, planned_delta int, actual_delta int ) 
INSERT INTO payBackTable values
( 'P001','Q1-2017', 1, 2000, 900)
( 'P001','Q2-2017', 2, 18000, 800)
( 'P001','Q3-2017', 3, 0, 7000)
( 'P001','Q4-2017', 4, 9000, -90)
( 'P001','Q1-2018', 5, -10, 9000)
( 'P001','Q2-2018', 6, 100, 70)
( 'P001','Q3-2018', 7, -90, -900)
( 'P001','Q4-2018', 8, 200, -8)
( 'P002', 'Q3-2016', 1, 1000, 90 )
( 'P002', 'Q4-2016', 2, -200, 90 )
( 'P002', 'Q1-2017', 3, 4000, -500 )
( 'P002', 'Q2-2017', 4, 10, -90 )
( 'P003', 'Q3-2021', 1, -10, 700 )
( 'P003', 'Q4-2021', 2, 100, -800 )
( 'P003', 'Q1-2022', 3, -100, -900 )
( 'P003', 'Q2-2022', 3, -90, 100 )

'''

源表

项目编号	季度	秩	计划三角洲	实际三角洲
P001	2017年第一季度	1	2000	900
P001	2017年第二季度	2	18000	800
P001	2017年第三季度	3	0	7000
P001	2017年第四季度	4	9000	-90
P001	2018年第一季度	5	-10	9000
P001	2018年第二季度	6	100	70
P001	2018年第三季度	7	-90	-900
P001	2018年第四季度	8	200	-8
P002	2016年第三季度	1	1000	90
P002	2016年第四季度	2	-200	90
P002	2017年第一季度	3	4000	-500
P002	2017年第二季度	4	10	-90
P003	2021 年第三季度	1	-10	700
P003	2021 年第四季度	2	100	-800
P003	2022 年第一季度	3	-100	-900
P003	2022 年第二季度	4	-90	100

期望的结果

项目编号	季度	秩	计划三角洲	实际三角洲	计划标志	实际标志
P001	2017年第一季度	1	2000	900	是	是
P001	2017年第二季度	2	18000	800	是	是
P001	2017年第三季度	3	0	7000	是	是
P001	2017年第四季度	4	9000	-90	是	ñ
P001	2018年第一季度	5	-10	9000	ñ	是
P001	2018年第二季度	6	100	70	是	是
P001	2018年第三季度	7	-90	-900	是	是
P001	2018年第四季度	8	200	-8	是	是
P002	2016年第三季度	1	1000	90	是	是
P002	2016年第四季度	2	-200	90	ñ	是
P002	2017年第一季度	3	4000	-500	是	ñ
P002	2017年第二季度	4	10	-90	是	是
P003	2021 年第三季度	1	-10	700	ñ	是
P003	2021 年第四季度	2	100	-800	是	ñ
P003	2022 年第一季度	3	-100	-900	是	是
P003	2022 年第二季度	4	-90	100	是	是

我已经能够使用三个不同的查询/批处理作业来实现表以推送所需的结果，但不确定如何在单个查询/批处理作业中实现这一点。在计划标志中推动“N”标志

select projectID,quarterYear, 'N' from(
select  projectID, quarterYear,
Row_number() OVER(PARTITION BY projectID ORDER BY to_number(Rank)) 
as rownumm from payBackTable 
where  to_number(planned_delta) < 0) where  rownumm = 1

在实际标志中推送“N”标志

select projectID,quarterYear, 'N' from(
select  projectID, quarterYear,
Row_number() OVER(PARTITION BY projectID ORDER BY to_number(Rank)) 
as rownumm from payBackTable 
where  to_number(actual_delta) < 0) where  rownumm = 1

在计划标志和实际标志中推动“Y”标志

select projectID,
quarterYear,
case planned_Flag when NULL then 'Y' ELSE planned_Flag END, 
case actual_Flag when NULL then 'Y' ELSE actual_Flag END
from payBackTable

标签： databaseoracleoracle11goracle10g

Answer 1

您可以根据每个值的符号获得项目中每个值的排名：

select projectid, quarteryear, rank, planned_delta, actual_delta,
  dense_rank() over (partition by projectid, sign(planned_delta) order by rank) as flag1,
  dense_rank() over (partition by projectid, sign(actual_delta) order by rank) as flag2
from paybacktable
order by projectid, rank

项目编号	季度	秩	PLANNED_DELTA	ACTUAL_DELTA	标志1	标志2
P001	2017年第一季度	1	2000	900	1	1
P001	2017年第二季度	2	18000	800	2	2
P001	2017年第三季度	3	0	7000	1	3
P001	2017年第四季度	4	9000	-90	3	1
P001	2018年第一季度	5	-10	9000	1	4
P001	2018年第二季度	6	100	70	4	5
P001	2018年第三季度	7	-90	-900	2	2
P001	2018年第四季度	8	200	-8	5	3

...

然后将它们用作 case 表达式的一部分；如果排名 i1并且值为负，则它是第一个负值：

select projectid, quarteryear, rank, planned_delta, actual_delta,
  case
    when sign(planned_delta) = -1
      and dense_rank() over (partition by projectid, sign(planned_delta) order by rank) = 1
    then 'N'
    else 'Y'
  end as planned_flag,
  case
    when sign(actual_delta) = -1
      and dense_rank() over (partition by projectid, sign(actual_delta) order by rank) = 1
    then 'N'
    else 'Y'
  end as actual_flag
from paybacktable
order by projectid, rank

项目编号	季度	秩	PLANNED_DELTA	ACTUAL_DELTA	PLANNED_FLAG	ACTUAL_FLAG
P001	2017年第一季度	1	2000	900	是	是
P001	2017年第二季度	2	18000	800	是	是
P001	2017年第三季度	3	0	7000	是	是
P001	2017年第四季度	4	9000	-90	是	ñ
P001	2018年第一季度	5	-10	9000	ñ	是
P001	2018年第二季度	6	100	70	是	是
P001	2018年第三季度	7	-90	-900	是	是
P001	2018年第四季度	8	200	-8	是	是
P002	2016年第三季度	1	1000	90	是	是
P002	2016年第四季度	2	-200	90	ñ	是
P002	2017年第一季度	3	4000	-500	是	ñ
P002	2017年第二季度	4	10	-90	是	是
P003	2021 年第三季度	1	-10	700	ñ	是
P003	2021 年第四季度	2	100	-800	是	ñ
P003	2022 年第二季度	3	-90	100	是	是
P003	2022 年第一季度	3	-100	-900	是	是

db<>小提琴