Oracle 为图表创建虚拟数据

ASKTOM 2019-04-09

249

问题描述

嗨，

我在Apex中生成条形图，我的数据有很少的差距，因此，如果我用零值填充查询中的数据，那么我的条形图将被正确显示。以下是数据和所需的输出

create table tab1(job varchar2(10),tdate date,value number)

insert into tab1 values('A',to_date('02-APR-19','DD-MON-YY'),100.32)
insert into tab1 values('B',to_date('02-APR-19','DD-MON-YY'),56.3)
insert into tab1 values('C',to_date('02-APR-19','DD-MON-YY'),85.3)
insert into tab1 values('X',to_date('03-APR-19','DD-MON-YY'),10.25)
insert into tab1 values('Y',to_date('03-APR-19','DD-MON-YY'),9.25)
insert into tab1 values('Z',to_date('03-APR-19','DD-MON-YY'),24.25)

A 02-APR-19 100.32
B 02-APR-19 56.3
C 02-APR-19 85.3
X 03-APR-19 10.25
Y 03-APR-19 9.25
Z 03-APR-19 24.25

Output
A 02-APR-19 100.32
B 02-APR-19 56.3
C 02-APR-19 85.3
A 03-APR-19 0
B 03-APR-19 0
C 03-APR-19 0
X 03-APR-19 10.25
Y 03-APR-19 9.25
Z 03-APR-19 24.25
X 02-APR-19 0
Y 02-APR-19 0
Z 02-APR-19 0复制

专家解答

我为4月5日添加了一行，因此我们可以进一步扩展。

SQL> create table tab1(job varchar2(10),tdate date,value number);

Table created.

SQL>
SQL> insert into tab1 values('A',to_date('02-APR-19','DD-MON-YY'),100.32);

1 row created.

SQL> insert into tab1 values('B',to_date('02-APR-19','DD-MON-YY'),56.3);

1 row created.

SQL> insert into tab1 values('C',to_date('02-APR-19','DD-MON-YY'),85.3);

1 row created.

SQL> insert into tab1 values('X',to_date('03-APR-19','DD-MON-YY'),10.25);

1 row created.

SQL> insert into tab1 values('Y',to_date('03-APR-19','DD-MON-YY'),9.25);

1 row created.

SQL> insert into tab1 values('Z',to_date('03-APR-19','DD-MON-YY'),24.25);

1 row created.

SQL> insert into tab1 values('Z',to_date('05-APR-19','DD-MON-YY'),24.25);

1 row created.
复制

所以我们需要

1) 不同的工作列表

SQL> select distinct job from tab1;

JOB
----------
B
C
Z
A
Y
X

6 rows selected.

复制

2) 日期的下限和上限

SQL> select min(tdate) start_date, max(tdate) end_date from tab1;

START_DAT END_DATE
--------- ---------
02-APR-19 05-APR-19

1 row selected.
复制

3) 然后我们可以使用CONNECT BY的技巧进行扩展 (请参见底部的视频)

SQL> with date_range as
  2  ( select min(tdate) start_date, max(tdate) end_date from tab1 )
  3  select start_date+rownum-1 date_val
  4  from date_range
  5  connect by level <= end_date - start_date + 1;

DATE_VAL
---------
02-APR-19
03-APR-19
04-APR-19
05-APR-19
复制

然后我们可以把这一切

SQL> with
  2    date_range as
  3    ( select min(tdate) start_date, max(tdate) end_date from tab1 ),
  4    all_dates as
  5    ( select start_date+rownum-1 date_val
  6      from date_range
  7      connect by level <= end_date - start_date + 1
  8    ),
  9    job_list as
 10    ( select distinct job from tab1 )
 11  select *
 12  from  all_dates,
 13        job_list,
 14        tab1
 15  where job_list.job = tab1.job(+)
 16  and   all_dates.date_val = tab1.tdate(+)
 17  order by 2,1;

DATE_VAL  JOB        JOB        TDATE          VALUE
--------- ---------- ---------- --------- ----------
02-APR-19 A          A          02-APR-19     100.32
03-APR-19 A
04-APR-19 A
05-APR-19 A
02-APR-19 B          B          02-APR-19       56.3
03-APR-19 B
04-APR-19 B
05-APR-19 B
02-APR-19 C          C          02-APR-19       85.3
03-APR-19 C
04-APR-19 C
05-APR-19 C
02-APR-19 X
03-APR-19 X          X          03-APR-19      10.25
04-APR-19 X
05-APR-19 X
02-APR-19 Y
03-APR-19 Y          Y          03-APR-19       9.25
04-APR-19 Y
05-APR-19 Y
02-APR-19 Z
03-APR-19 Z          Z          03-APR-19      24.25
04-APR-19 Z
05-APR-19 Z          Z          05-APR-19      24.25

24 rows selected.
复制