Oracle SQL多列与单个范围/图例对齐 [英] Oracle SQL multiple columns aligned to a single range/legend
本文介绍了Oracle SQL多列与单个范围/图例对齐的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想要计数每个日期列中每个项目的活动ID。然后我想要采取这些日期列中的每一个，并将它们相互排列。
最终结果将是一个曲线，显示第2周相对于第二周的实际活动数量与第二周的计划活动数量以及第二周的最终活动次数相比。
我可以做到这一点获取数据显示...
SELECT ACTIVITY.PROJECTOBJECTID，
ACTIVITY.OBJECTID，
TRUNC（ACTIVITY.BASELINEFINISHDATE，'W'）+ 7 AS BLFIN，
TRUNC（ACTIVITY.ACTUALFINISHDATE，'W'）+ 7 AS AFIN，
TRUNC（ACTIVITY.FINISHDATE，'W'）+ 7 AS FIN，
TRUNC（ACTIVITY.REMAININGLATEFINISHDATE，'W'）+ 7 AS LFIN
FROM PXRPTUSER.ACTIVITY ACTIVITY
WHERE（ACTIVITY.PROJECTOBJECTID =：POID）
但是，现在我有四个日期列。理想情况下，我需要一个日期列的四个计数列。以下是我要处理的数据的样本。
PID AID BLFIN AFIN FIN LFIN
39987 5874494 2 / 22/2015 2/22/2015 6/15/2015
39987 5874495 2/22/2015 2/22/2015 6/15/2015
39987 5874496 2/22/2015 2/22 / 2015 6/15/2015
39987 5874497 2/22/2015 2/22/2015 6/15/2015
39987 5874498 2/22/2015 2/22/2015 6/15/2015
39987 5874499 4/22/2015 4/22/2015 6/15/2015
39987 5874500 2/22/2015 2/22/2015 6/15/2015
39987 5874501 2/22 / 2015 2/22/2015 6/15/2015
39987 5874502 4/8/2015 4/8/2015 6/15/2015
39987 5874503 4/8/2015 4/8/2015 6 / 15/2015
39987 5874504 4/8/2015 4/8/2015 6/15/2015
39987 5874505 5/15/2015 5/15/2015 6/15/2015
39987 5874506 5/15/2015 5/15/2015 6/15/2015
39987 5874507 4/8/2015 4/8/2015 6/15/2015
39987 5874508 4/8/2015 4 / 8/2015 6/15/2015
39987 5874509 4/8/2015 4/8/2015 6/15/2015
39987 5874510 4/8/2015 4/8/2015 6/15/2015
39987 5874511 4/8/2015 4/8/2015 6/15/2015
39987 5874512 4/8/2015 4/8/2015 6/15/2015
39987 5874513 4/8/2015 4/8/2015 6/15/2015
39987 5874514 4/8/2015 4/8/2015 6/15/2015
39987 5874515 4/8/2015 4/8/2015 6/15 / 2015
39987 5874516 4/8/2015 4/8/2015 6/15/2015
39987 5874517 4/8/2015 4/8/2015 6/15/2015
39987 5874537 2/22/2015 2/22/2015 6/15/2015
39987 5874538 2/22/2015 2/22/2015 6/15/2015
39987 5874539 2/22/2015 2/22 / 2015 6/15/2015
39987 5874540 2/22/2015 2/22/2015 2/22/2015
39987 5874542 2/22/2015 2/22/2015 6/15/2015
39987 5874543 2/22/2015 2/22/2015 6/15/2015
39987 5874544 2/22/2015 2/22/2015 6/15/2015
39987 5874545 2/22 / 2015 2/22/201 5 6/15/2015
39987 5874546 2/22/2015 2/22/2015 6/15/2015
39987 5874547 2/22/2015 2/22/2015 6/15/2015
39987 5874548 2/22/2015 2/22/2015 6/15/2015
39987 5874549 2/22/2015 2/22/2015 6/15/2015
39987 5874550 2/22 / 2015 2/22/2015 6/15/2015
39987 5874551 2/22/2015 2/22/2015 6/15/2015
39987 5874552 2/22/2015 2/22/2015 6 / 15/2015
39987 5874553 2/22/2015 2/22/2015 6/15/2015
39987 5874554 2/22/2015 2/22/2015 6/15/2015
解决方案
下面的查询给出了描述的结果。 WKN列是 ISO标准中的周号，您可能希望将其更改为否则，例如。 'WW'。
如果您有不同年份的日期，您必须连接年份查询（在bf，af，pf和lf子查询中附加年份）。
bf as（select to_char（blfin，'iw'）wkn，count（aid）cnt
from activity group by to_char（blfin，'iw'）） ，
af as（select to_char（afin，'iw'）wkn，count（aid）cnt
from activity group by to_char（afin，'iw'）），
pf as（select to_char（fin，'iw'）wkn，count（aid）cnt
从活动组通过to_char（fin，'iw'）），
lf as（select to_char（lfin，'iw'）wkn ，从活动组的count（aid）cnt
to_char（lfin，'iw'））
选择wkn，nvl（bf.cnt，0）作为基线，nvl（af.cnt，0）as实际，
nvl（pf.cnt，0）按计划，nvl（lf.cnt，0）as b $
从bf
完全连接af使用（wkn）
完全连接pf using（wkn）
full join lf using（wkn）
其中wkn不为空
order by wkn
结果：
WKN基线计划实际计划
--- -------- -------- ----- --- --------
08 24 0 24 1
15 14 0 14 0
17 1 0 1 0
20 2 0 2 0
25 0 0 0 40
I have an activity table in my application. It has a project ID, an activity ID, a baseline finish date, an actual finish date a planned finish date and a late finish date.
I want to take the count of activity ID for each week of the project in each date column. I then want to take each of these date columns and plot them against each other.
The end result would be a curve that shows the number of activities in week 2 compared to the actual number of activities in week 2 compared to the number of planned activities in week two and the number of late-finish activities in week 2.
I can do this to get the data to show up...
SELECT ACTIVITY.PROJECTOBJECTID,
ACTIVITY.OBJECTID,
TRUNC(ACTIVITY.BASELINEFINISHDATE,'W')+7 AS BLFIN,
TRUNC(ACTIVITY.ACTUALFINISHDATE,'W')+7 AS AFIN,
TRUNC(ACTIVITY.FINISHDATE,'W')+7 AS FIN,
TRUNC(ACTIVITY.REMAININGLATEFINISHDATE,'W')+7 AS LFIN
FROM PXRPTUSER.ACTIVITY ACTIVITY
WHERE (ACTIVITY.PROJECTOBJECTID = :POID)
But, now I have four date columns. Ideally I want four count columns with a single date column. Here is a sample of the data I have to work with.
PID AID BLFIN AFIN FIN LFIN
39987 5874494 2/22/2015 2/22/2015 6/15/2015
39987 5874495 2/22/2015 2/22/2015 6/15/2015
39987 5874496 2/22/2015 2/22/2015 6/15/2015
39987 5874497 2/22/2015 2/22/2015 6/15/2015
39987 5874498 2/22/2015 2/22/2015 6/15/2015
39987 5874499 4/22/2015 4/22/2015 6/15/2015
39987 5874500 2/22/2015 2/22/2015 6/15/2015
39987 5874501 2/22/2015 2/22/2015 6/15/2015
39987 5874502 4/8/2015 4/8/2015 6/15/2015
39987 5874503 4/8/2015 4/8/2015 6/15/2015
39987 5874504 4/8/2015 4/8/2015 6/15/2015
39987 5874505 5/15/2015 5/15/2015 6/15/2015
39987 5874506 5/15/2015 5/15/2015 6/15/2015
39987 5874507 4/8/2015 4/8/2015 6/15/2015
39987 5874508 4/8/2015 4/8/2015 6/15/2015
39987 5874509 4/8/2015 4/8/2015 6/15/2015
39987 5874510 4/8/2015 4/8/2015 6/15/2015
39987 5874511 4/8/2015 4/8/2015 6/15/2015
39987 5874512 4/8/2015 4/8/2015 6/15/2015
39987 5874513 4/8/2015 4/8/2015 6/15/2015
39987 5874514 4/8/2015 4/8/2015 6/15/2015
39987 5874515 4/8/2015 4/8/2015 6/15/2015
39987 5874516 4/8/2015 4/8/2015 6/15/2015
39987 5874517 4/8/2015 4/8/2015 6/15/2015
39987 5874537 2/22/2015 2/22/2015 6/15/2015
39987 5874538 2/22/2015 2/22/2015 6/15/2015
39987 5874539 2/22/2015 2/22/2015 6/15/2015
39987 5874540 2/22/2015 2/22/2015 2/22/2015
39987 5874542 2/22/2015 2/22/2015 6/15/2015
39987 5874543 2/22/2015 2/22/2015 6/15/2015
39987 5874544 2/22/2015 2/22/2015 6/15/2015
39987 5874545 2/22/2015 2/22/2015 6/15/2015
39987 5874546 2/22/2015 2/22/2015 6/15/2015
39987 5874547 2/22/2015 2/22/2015 6/15/2015
39987 5874548 2/22/2015 2/22/2015 6/15/2015
39987 5874549 2/22/2015 2/22/2015 6/15/2015
39987 5874550 2/22/2015 2/22/2015 6/15/2015
39987 5874551 2/22/2015 2/22/2015 6/15/2015
39987 5874552 2/22/2015 2/22/2015 6/15/2015
39987 5874553 2/22/2015 2/22/2015 6/15/2015
39987 5874554 2/22/2015 2/22/2015 6/15/2015
解决方案
Query below gives described results. Column WKN is week number in ISO standard, you may want to change it to something else, eg. 'WW'. Also if you have dates from different years you have to concatenate year in query (attach year to wkn in bf, af, pf and lf subqueries).
with
bf as (select to_char(blfin, 'iw') wkn, count(aid) cnt
from activity group by to_char(blfin, 'iw')),
af as (select to_char(afin, 'iw') wkn, count(aid) cnt
from activity group by to_char(afin, 'iw')),
pf as (select to_char(fin, 'iw') wkn, count(aid) cnt
from activity group by to_char(fin, 'iw')),
lf as (select to_char(lfin, 'iw') wkn, count(aid) cnt
from activity group by to_char(lfin, 'iw'))
select wkn, nvl(bf.cnt, 0) as baseline, nvl(af.cnt, 0) as actual,
nvl(pf.cnt, 0) as planned, nvl(lf.cnt, 0) as late
from bf
full join af using (wkn)
full join pf using (wkn)
full join lf using (wkn)
where wkn is not null
order by wkn
Results:
WKN BASELINE ACTUAL PLANNED LATE
--- -------- -------- -------- --------
08 24 0 24 1
15 14 0 14 0
17 1 0 1 0
20 2 0 2 0
25 0 0 0 40
这篇关于Oracle SQL多列与单个范围/图例对齐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
