Oracle SQL查询组合字段百分比

Question

我有一个这样的表

NAME   CATEGORY    PERCENT
Black  Color       0.10
Blue   Color       0.30
Green  Color       0.60
Fast   Speed       0.40
Slow   Speed       0.60

我想要的输出是

COMBINEDCAT   COMBINEDPC
BlackFast      0.04
BlackSlow      0.06
BlueFast       0.12
BlueSlow       0.18
GreenFast      0.24
GreenSlow      0.36

所以本质上我在寻找一种方法将这些名字相乘来形成所有可能的分类结果，这有意义吗?我已经为此挣扎了一段时间，任何帮助都是感激的!

谢谢!

编辑:可能有无限的类别，所以我正在寻找一些不需要在查询中引用每个类别的东西。

Answer 1

select t1.Name || t2.Name as CombinedCat,
       t1.Percent * t2.Percent as CombinedPc
  from your_table t1
  join your_table t2
    on t2.Category = 'Speed'
 where t1.Category = 'Color'
 order by CombinedCat

编辑:调整问题描述

如果您希望对动态数量的类别执行此操作，可以使用以下查询，该查询使用递归CTE:

with Categories as (
  select category,
         row_number() over (order by category) as seq
    from your_table
   group by category),
RecursiveCTE (Name, Percent, seq) as (
  select t.Name,
         t.Percent,
         c.seq
    from your_table t
    join Categories c
      on c.category = t.category
     and c.seq = 1
   union all   
  select r.Name || t.Name as Name,
         r.Percent * t.Percent as Percent,
         c.seq
    from RecursiveCTE r
    join Categories c
      on c.seq = r.seq + 1
    join your_table t
      on t.category = c.category
)
select t.Name as CombinedCat,
       Percent as CombinedPc
  from RecursiveCTE t
 where t.seq = (select max(seq) from Categories)
order by t.Name

SQLFiddle演示

上面的查询按字母顺序连接类别名称，但是您可以通过更改Categories CTE中row_number()函数中的order by子句来进行调整:

row_number() over (order by category) as seq

Answer 2

这个怎么样

• 您加入了除您以外的所有"<>"类别。

• 但只加入比你"大"的，以避免反向重复。

.

   SELECT 
       T1."CATEGORY", 
       T2."CATEGORY", 
       T1."NAME"||T2."NAME" as  "COMBINEDCAT", 
       T1."PERCENT"*T2."PERCENT" as "COMBINEDPC"
   FROM Table1 T1
   INNER JOIN Table1 T2
      ON T1."CATEGORY" < T2."CATEGORY"
   ORDER BY T1."CATEGORY", T2."CATEGORY", T1."NAME"||T2."NAME"

SQL Fiddle Demo

我在select中包含了类别，这样你就可以验证连接，还包含了一个新的类别COST

| CATEGORY | CATEGORY | COMBINEDCAT | COMBINEDPC |
|----------|----------|-------------|------------|
|    Color |     Cost |  BlackCheap |       0.03 |
|    Color |     Cost |   BlackHigh |       0.04 |
|    Color |     Cost |    BlackLow |       0.03 |
|    Color |     Cost |   BlueCheap |       0.09 |
|    Color |     Cost |    BlueHigh |       0.12 |
|    Color |     Cost |     BlueLow |       0.09 |
|    Color |     Cost |  GreenCheap |       0.18 |
|    Color |     Cost |   GreenHigh |       0.24 |
|    Color |     Cost |    GreenLow |       0.18 |
|    Color |    Speed |   BlackFast |       0.04 |
|    Color |    Speed |   BlackSlow |       0.06 |
|    Color |    Speed |    BlueFast |       0.12 |
|    Color |    Speed |    BlueSlow |       0.18 |
|    Color |    Speed |   GreenFast |       0.24 |
|    Color |    Speed |   GreenSlow |       0.36 |
|     Cost |    Speed |   CheapFast |       0.12 |
|     Cost |    Speed |   CheapSlow |       0.18 |
|     Cost |    Speed |    HighFast |       0.16 |
|     Cost |    Speed |    HighSlow |       0.24 |
|     Cost |    Speed |     LowFast |       0.12 |
|     Cost |    Speed |     LowSlow |       0.18 |

Answer 3

您可以cross join以获得所需的结果。

修改样本数据

select 
n.name||s.name as combinedcat,
n.percent*s.percent
from (select distinct name, percent from tablename where category = 'Color') n
cross join (select distinct name, percent from tablename where category = 'Speed') s