sql - 如何在 Informix SQL 中创建汇总？

Question

我经常在不同的报告中遇到这样的中间汇总：

|calltypename     |rating                                  |number |
+-----------------+----------------------------------------+-------+
|sales            |1.0                                     |1      |
|sales            |5.0                                     |2      |
|                 |3.666666666666666666666666666667        |3      |
|service          |1.0                                     |1      |
|service          |3.0                                     |1      |
|service          |5.0                                     |3      |
|service          |9.0                                     |1      |
|                 |4.666666666666666666666666666667        |6      |

这里的记录按 calltypename 和中间汇总分组：平均评分和数字总和。Informix SQL 没有 ROLLUP 运算符，因此我尝试使用 UNION 实现类似的结果：

select  calltypename, TO_NUMBER(datavalue) as rating, count(*) as number
from calldata
where datakey="qrate1" 
group by calltypename, rating
union all
select calltypename, AVG(TO_NUMBER(datavalue)) as rating, count(*) as number
from calldata
where datakey="qrate1" 
group by calltypename
order by calltypename, rating

它产生以下结果：

|calltypename     |rating                                  |number |
+-----------------+----------------------------------------+-------+
|sales            |1.0                                     |1      |
|sales            |3.666666666666666666666666666667        |3      |
|sales            |5.0                                     |2      |
|service          |1.0                                     |1      |
|service          |3.0                                     |1      |
|service          |4.666666666666666666666666666667        |6      |
|service          |5.0                                     |3      |
|service          |9.0                                     |1      |

是否有任何提示如何对记录进行排序，以便汇总始终位于相关组下方？

一段时间后，我找到了一个我不太喜欢的解决方案。想法是添加一个将在 ORDER BY 语句中使用的假列“ROLLUP”：

select calltypename as queue, "" as rollup,
datavalue as rating, count(*) as number
from calldata
where datakey="qrate1" 
group by queue, rating
union all
select calltypename as queue, "rollup" as rollup,
TO_CHAR(AVG(TO_NUMBER(datavalue)),"*.*") as rating, count(*) as number
from calldata
where datakey="qrate1"
group by queue
order by queue, rollup, rating

这会产生一个结果：

 |queue  |rollup    |rating|number |
 +-------+----------+------+-------+
 |sales  |          |1     |1      |
 |sales  |          |5     |2      |
 |sales  |rollup    |3.7   |3      |
 |service|          |1     |1      |
 |service|          |3     |1      |
 |service|          |5     |3      |
 |service|          |9     |1      |
 |service|rollup    |4.7   |6      |

但我希望它没有 ROLLUP 列...

标签： sqlinformix