代码之家 › 专栏 › 技术社区 › mayank

按(天)分组数据,然后在Oracle中将这两个表连接在一起

plsql oracle sql

mayank · 技术社区 · 8 年前

有两张桌子 许可证 和

许可证表

oracle_apps       tableau      sab_bi      tririga     time_snapshot
0                  1            1           1          2017-06-13 08:12:02.640
0                  0            0           1          2017-06-13 09:12:02.640
0                  0            1           0          2017-06-13 11:52:02.640
0                  1            0           1          2017-06-14 09:12:02.640   
0                  0            1           0          2017-06-14 10:12:02.640

有2列

license_name                 license_count
oracle_                      5.0000000
tableau_                     1.0000000
sab_                         20.0000000 
tririga_                     10.0000000

因此,输出将是一个select查询或pl SQL存储过程,该过程将包含两个表的连接,并显示 (计数)

输出:

 oracle_apps    tableau    sab_bi    tririga    time_snapshot  oracle_    tableau_    sap_        tririga_  

 0              1          2         2          2017-06-13     5.0000000  1.0000000   20.0000000  10.0000000
 0              1          1         1          2017-06-14     5.0000000  1.0000000   20.0000000  10.0000000

在…上全部的 5. 0,1,2,2 i、 e.sum(oracle_应用程序)、sum(tableau)、sum(sab_bi)、sum(tririga) (0, 1, 1, 1)

使用起来很容易 分组依据to\u char(时间快照,yyyy-mm-dd) 但我无法连接表并将行用作列,因为在组织表中,行将列在输出中,我知道一种使用枢轴但我很困惑如何在结果中得到它,对于每一行数值相同,即(5.0000000 | 1.0000000 | 20.0000000
| 10.0000000)

将数据分组并将表连接在一起?

2 回复 | 直到 8 年前

krokodilko 8 年前

使用 CROSS JOIN , SUM , MAX CASE..WHEN..THEN.. 和 GROUP BY

SELECT trunc( l.time_snapshot) As time_snapshot,
       sum( l.oracle_apps ) As oracle_apps,
       sum( l.tableau ) As tableau,
       sum( l.sab_bi  ) As sab_bi,
       sum( l.tririga ) As tririga,
       max( CASE WHEN o.license_name = 'oracle_' THEN o.license_count END ) As oracle_,
       max( CASE WHEN o.license_name = 'tableau_' THEN o.license_count END ) As tableau_,
       max( CASE WHEN o.license_name = 'sab_' THEN o.license_count END ) As sab_,
       max( CASE WHEN o.license_name = 'tririga_' THEN o.license_count END ) As tririga_
FROM licenses l
CROSS JOIN organization o 
GROUP BY trunc( l.time_snapshot)

演示: http://sqlfiddle.com/#!4/24b53/5

Gordon Linoff 8 年前

之前

select l.dte, l.oracle_apps, l.tableau, l.sab_bi, l.tririga,,
       o.oracle_, o.tableau_, o.sab_, o.tririga_
from (select trunc(l.time_snapshot) as dte,
             sum(l.oracle_apps) as oracle_apps,
             sum(l.tableau) as tableau,
             sum(l.sab_bi) as sab_bi,
             sum(l.tririga) as tririga
      from licenses l
      group by trunc(l.time_snapshot)
     ) l cross join
     (select sum(case when license_name = 'oracle_' then license_count else 0 end) as oracle_,
             sum(case when license_name = 'tableau_' then license_count else 0 end) as tableau_,
             sum(case when license_name = 'sab_' then license_count else 0 end) as sab_,
             sum(case when license_name = 'tririga_' then license_count else 0 end) as tririga_        
      from organization o
     ) o;

在单个表上进行聚合对我来说似乎更安全(如果 oracle_ 在组织表中出现两次,您的计数将关闭)。此外,生成的查询可能更快,因为对单个表的优化通常比对多个表的优化更容易。