Oracle SQL帮助数据汇总

Question

我在Oracle12c上，需要简单查询方面的帮助。

以下是我目前拥有的样本数据：

Table Name: customer

表DDL

create table customer(
    customer_id    varchar2(50),
    name           varchar2(50),
    activation_dt  date,
    space_occupied number(50)
    );

样本表数据：

customer_id  name     activation_dt  space_occupied 
abc          abc-001  2016-09-12                 20
xyz          xyz-001  2016-09-12                 10

样本数据输出

我正在寻找的查询将提供以下内容：

customer_id  name     activation_dt  space_occupied
abc          abc-001  2016-09-12                 20
xyz          xyz-001  2016-09-12                 10
Total_Space  null     null                       30

Answer 1

您想要的是一些不寻常的东西，就好像customer_id是整数一样，那么您必须将它转换为string等等，但这是您的要求，如果是这样实现的话。

SELECT customer_id,
       name,
       activation_dt,
       space_occupied
FROM
  (SELECT   1   AS seq,
                customer_id,
                name,
                activation_dt,
                space_occupied
   FROM customer

    UNION ALL 

   SELECT 2             AS seq,
    'Total_Space'       AS customer_id,
    NULL                AS name,
    NULL                AS activation_dt,
    sum(space_occupied) AS space_occupied
   FROM customer
   )
ORDER BY seq

解释：

• 内部查询：
  • union的第一部分；我添加了1 as seq，让1从客户那里得到硬编码的结果集。
  • 第二部分:我只是计算和(Space_occupied)和硬编码其他列，包括2 as seq。

​

• 外部查询；选择数据列并按seq排序，最终返回Total_Space。

输出

    +-------------+---------+---------------+----------------+
    | CUSTOMER_ID |  NAME   | ACTIVATION_DT | SPACE_OCCUPIED |
    +-------------+---------+---------------+----------------+
    | abc         | abc-001 | 12-SEP-16     |             20 |
    | xyz         | xyz-001 | 12-SEP-16     |             10 |
    | Total_Space | null    | null          |             30 |
    +-------------+---------+---------------+----------------+

Answer 2

下面是一种使用分组函数ROLLUP()对此进行稍微hack-y处理的方法。了解更多信息。

SQL> select coalesce(customer_id, 'Total Space') as customer_id
  2         , name
  3        , activation_dt
  4        , sum(space_occupied) as space_occupied
  5  from customer
  6  group by ROLLUP(customer_id, name, activation_dt)
  7  having grouping(customer_id) = 1
  8  or (grouping(name) + grouping(customer_id)+ grouping(activation_dt)) = 0;

CUSTOMER_ID  NAME         ACTIVATIO SPACE_OCCUPIED
------------ ------------ --------- --------------
abc          abc-001      12-SEP-16             20
xyz          xyz-001      12-SEP-16             10
Total Space                                     30

SQL> 

ROLLUP()为每个列组合生成中间总计；详细的HAVING子句过滤掉它们，只保留总计。

Answer 3

似乎是一个很好的地方使用group by grouping sets，似乎这就是他们设计的目的。Doc链接

SELECT coalesce(Customer_Id,'Total_Space') as Customer_ID
     , Name
     , ActiviatioN_DT
     , sum(Space_occupied) space_Occupied
FROM customer
GROUP BY GROUPING SETS ((Customer_ID, Name, Activation_DT, Space_Occupied)
                       ,())

这里的关键是我们正在总结占用的空间。这两种不同的分组机制告诉引擎保留每一行的原始形式，并将1条记录与space_occupied相加；因为我们按()空集分组；将只返回聚合值；以及常量(合并硬编码值表示总计！)

​

​

这样做的好处是，如果您也需要按其他事物进行分组，则可以有多个分组集。想象一个有产品部门、组和线的材料，我想要一个按部门、组和线列出销售总额的报告。您可以简单地按()分组以获得总计，(product_division、Product_Group、line)以获得产品线(product_Divsion、product_group)以获得product_group总计，(product_division)获得产品部门总计。对于部分立方体的生成来说非常强大的东西。