如何在Oracle11g中将ROLLUP，RANK()与数据透视表一起使用 [英] How to use ROLLUP, RANK() with pivot table in Oracle11g

问题描述

表架构

CREATE TABLE customer ( 
 id          NUMERIC, 
 lname       VARCHAR (30), 
 fname       VARCHAR (30) NOT NULL, 
 street      VARCHAR (30) NOT NULL, 
 city        VARCHAR (30) NOT NULL, 
 zipcode     NUMERIC (5) NOT NULL, 
 state       VARCHAR (2) NOT NULL, 
 phone       VARCHAR (12) NOT NULL, 
 creditscore NUMERIC, 
 credit_org  VARCHAR (30), 
 cs_date     DATE, 
 CONSTRAINT customer_pk PRIMARY KEY (id) 
);

要求:

第1部分: 创建一个数据透视表，以按地点("PA"，"CA"，"NY"，"MD")以及信用评分范围列出客户数量.对于信用评分范围，请创建3个细分，信用等级介于500-600之间的是较低范围(500-600)"，信用等级介于600-700之间的是平均范围(600-700)"， RANGE(700+)'定义为信用得分为700+的那些.数据透视表的结果应包括用于状态的4列和用于信用评分范围的3行.

Part 1: Create a pivot table to list the number of customers by location ('PA', 'CA', 'NY', 'MD') and also by creditscore range. For creditscore range, create 3 segments, 'LOWER RANGE(500-600)' defined as those with credit score between 500-600, 'AVERAGE RANGE(600-700)' defined as those with credit score between 600-700, 'PREMIUM RANGE(700+)' defined as those with credit score of 700+. The pivot table result should include 4 columns for states and 3 rows for creditscore range.

第2部分: 添加总计行以得出各个州的客户总数 根据客户总数对州进行排名.

Part 2: Add a total row to give total no of customers for respective states Rank the states based on the total no of customers.

额外要求 根据客户总数对州进行排名，首先是高级范围，然后是平均范围，然后是较低范围.

Extra requirement Rank the states based on the total no of customers first in premium range, then average range and then lower range.

到目前为止我的收获

SELECT * FROM (
  SELECT case
           when CREDITSCORE between 500 and 600 then 'LOWER RANGE(500-600)'
           when CREDITSCORE <= 700 then 'AVERAGE RANGE(600-700)'
           else 'PREMIUM RANGE(700+)'
    end as CREDITSCORE_RANGE,
    state
  FROM customer
) 
PIVOT (
  count(state) FOR state IN ('PA', 'CA', 'NY', 'MD')
);

第1部分是正确的

我的问题是如何解决第二部分，甚至有可能吗?

My question is How to tackle Part 2 and Is it even possible?

我使用DECODE进行上述操作

MY take on above using DECODE

选择解码(大小写 当CREDITSCORE< = 600时，则显示"LOWER RANGE(500-600)" 当CREDITSCORE< = 700时，则为"AVERAGE RANGE(600-700)" 否则为高级范围(700+)" end，NULL，'TOTAL'，大小写 当CREDITSCORE在500到600之间时，则显示"LOWER RANGE(500-600)" 当CREDITSCORE< = 700时，则为"AVERAGE RANGE(600-700)" 否则为高级范围(700+)" 结束)"CREDITSCORE_RANGE"， SUM(DECODE(state，'PA'，1，0))宾夕法尼亚州"， SUM(DECODE(state，'CA'，1，0))加利福尼亚"， SUM(DECODE(state，'NY'，1，0))纽约"， SUM(DECODE(state，'MD'，1，0))马里兰州"， count(CREDITSCORE)客户总数"， RANK()超过(按Count(CREDITSCORE)DESC排序)按无客户排名" 来自客户 GROUP BY ROLLUP(案例 当CREDITSCORE在500到600之间时，则显示"LOWER RANGE(500-600)" 当CREDITSCORE< = 700时，则为"AVERAGE RANGE(600-700)" 否则为高级范围(700+)" 结束);

SELECT DECODE(case when CREDITSCORE <= 600 then 'LOWER RANGE(500-600)' when CREDITSCORE <= 700 then 'AVERAGE RANGE(600-700)' else 'PREMIUM RANGE(700+)' end, NULL, 'TOTAL',case when CREDITSCORE between 500 and 600 then 'LOWER RANGE(500-600)' when CREDITSCORE <= 700 then 'AVERAGE RANGE(600-700)' else 'PREMIUM RANGE(700+)' end) "CREDITSCORE_RANGE", SUM(DECODE(state, 'PA', 1, 0)) "Pennsylvania", SUM(DECODE(state, 'CA', 1, 0)) "California", SUM(DECODE(state, 'NY', 1, 0)) "New York", SUM(DECODE(state, 'MD', 1, 0)) "Maryland", count(CREDITSCORE) "Total No of Customers", RANK() OVER (ORDER BY Count(CREDITSCORE) DESC) "RANK BY NO OF CUSTOMERS" FROM customer GROUP BY ROLLUP(case when CREDITSCORE between 500 and 600 then 'LOWER RANGE(500-600)' when CREDITSCORE <= 700 then 'AVERAGE RANGE(600-700)' else 'PREMIUM RANGE(700+)' end);

推荐答案

我认为您的范围不适合您编写的查询，尽管赋值的措词含糊不清，因为'between'包含在内-因此，用措辞来回答问题时，较低"和平均"两方都会出现正好为600的信用评分.您的版本会将600放在较低"的括号中，但是应该放在哪个位置尚有待商;.我认为这应该是其他定义的平均值"，但尚不清楚.问题中没有分数小于500的括号，但是如果您有任何分数，那么您的当前代码会将它们包括在平均"括号中，因为它们小于700，但不在500到600之间.

I don't think your ranges are quite right for the query you've written, though the wording of the assignment is ambiguous as 'between' is inclusive - so as the question is worded, a credit score of exactly 600 would appear in both the 'lower' and 'average' brackets. Your version will put 600 in the 'lower' bracket, but it's debatable which it should be in; I'd think it should be 'average' from the other definitions, but it's unclear. There is no bracket in the question for scores less than 500, but if you have any of those then your current code will include them in the 'average' bracket, since they are less than 700 but not between 500 and 600.

所以我将其解释为:

SELECT * FROM (
  SELECT case
           when CREDITSCORE >= 500 and CREDITSCORE < 600 then 'LOWER RANGE(500-600)'
           when CREDITSCORE >= 600 and CREDITSCORE < 700 then 'AVERAGE RANGE(600-700)'
           when CREDITSCORE >= 700 then 'PREMIUM RANGE(700+)'
    end as CREDITSCORE_RANGE,
    state
  FROM customer
) 
PIVOT (
  count(state) FOR state IN ('PA' as pa, 'CA' as ca, 'NY' as ny, 'MD' as md)
);

您的问题标题涉及ROLLUP，要获得总行数，您可以使用该函数:

Your question title refers to ROLLUP, and to get the total row you can use that function:

SELECT creditscore_range, sum(pa) AS pa, sum(ca) AS ca, sum(ny) AS ny, sum(md) AS md
FROM (
  SELECT * FROM (
    SELECT CASE
             WHEN creditscore >= 500 AND creditscore < 600 THEN 'LOWER RANGE(500-600)'
             WHEN creditscore >= 600 AND creditscore < 700 THEN 'AVERAGE RANGE(600-700)'
             WHEN creditscore >= 700 THEN 'PREMIUM RANGE(700+)'
      END AS creditscore_range,
      state
    FROM customer
  ) 
  PIVOT (
    COUNT(state) FOR state IN ('PA' AS pa, 'CA' AS ca, 'NY' AS ny, 'MD' AS md)
  )
)
GROUP BY ROLLUP (creditscore_range);

如果您的分数确实低于500，那么两者都将在creditscore_range为null的那一行中包含一行；这与ROLLUP版本混淆.您可能希望从最里面的查询中过滤掉所有分数小于500的分数，但是同样不清楚还是有必要的.

If you do have any scores below 500 then Both will include a line for those with the creditscore_range as null; which is confusing with the ROLLUP version. You may want to filter any scores less than 500 out from the innermost query, but again it isn't clear if that is necessary or desirable.

我不确定在谈论排名时是否正在寻找作业.这意味着根据列包含的值更改列顺序.如果数据以其他方式进行数据透视，则按州进行排名会更有意义.

I'm not sure that the assignment is looking for when it talks about ranking though. That implies changing the column order based on the values they contain. Ranking by state would make more sense if the data was pivoted the other way.

这篇关于如何在Oracle11g中将ROLLUP，RANK()与数据透视表一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！