Как использовать ROLLUP, RANK () со сводной таблицей в 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, «ПРЕМИУМ». ДИАПАЗОН (700+) 'определяется как лица с кредитным рейтингом 700+. Результат сводной таблицы должен включать 4 столбца для состояний и 3 строки для диапазона кредитов.

Часть 2: Добавьте итоговую строку, чтобы указать общее количество клиентов для соответствующих состояний. Ранжируйте состояния на основе общего количества клиентов.

Дополнительные требования Оцените штаты по общему количеству клиентов сначала в премиальном диапазоне, затем в среднем диапазоне, а затем в более низком диапазоне.

Мое мнение

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

Мой вопрос: как решить часть 2 и возможно ли это вообще?

Изменить 1:

МОЙ подход к вышеизложенному с использованием DECODE

ВЫБРАТЬ ДЕКОДИРОВАНИЕ (случай, когда CREDITSCORE ‹= 600, затем« НИЖНИЙ ДИАПАЗОН (500-600) », когда CREDITSCORE‹ = 700, затем «СРЕДНИЙ ДИАПАЗОН (600-700)» иначе «ПРЕМИУМ ДИАПАЗОН (700+)» конец, NULL, «ИТОГО» , случай, когда CREDITSCORE между 500 и 600, затем «НИЖНИЙ ДИАПАЗОН (500-600)», когда CREDITSCORE ‹= 700, затем« СРЕДНИЙ ДИАПАЗОН (600-700) », иначе« ПРЕМИУМ ДИАПАЗОН (700+) »конец)« CREDITSCORE_RANGE », СУММ ( DECODE (штат, 'PA', 1, 0)) «Пенсильвания», SUM (DECODE (штат, 'CA', 1, 0)) «Калифорния», SUM (DECODE (штат, 'NY', 1, 0) ) «Нью-Йорк», SUM (DECODE (state, 'MD', 1, 0)) «Мэриленд», count (CREDITSCORE) «Общее количество клиентов», RANK () OVER (ORDER BY Count (CREDITSCORE) DESC) » РАНГ ПО КОЛИЧЕСТВУ КЛИЕНТОВ "ИЗ ГРУППЫ клиентов ПО РОЛИКЕ (случай, когда CREDITSCORE от 500 до 600, затем" НИЖНИЙ ДИАПАЗОН (500-600) ", когда CREDITSCORE‹ = 700, затем "СРЕДНИЙ ДИАПАЗОН (600-700)" иначе "ПРЕМИУМ ДИАПАЗОН (700+) 'конец);


sql rollup pivot-table oracle11g
person Shankar Bansal    schedule 13.04.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я не думаю, что ваши диапазоны вполне подходят для написанного вами запроса, хотя формулировка назначения неоднозначна, так как «между» является включающим - так что, как вопрос сформулирован, кредитная оценка ровно 600 будет отображаться в обоих скобки «нижняя» и «средняя». Ваша версия поместит 600 в «нижнюю» скобку, но вопрос о том, в какой именно скобке, остается спорным; Я бы подумал, что это должно быть «среднее» из других определений, но это неясно. В вопросе нет скобок для оценок меньше 500, но если они у вас есть, то ваш текущий код будет включать их в «среднюю» скобку, поскольку они меньше 700, но не между 500 и 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, и для получения итоговой строки вы можете использовать эту функцию:

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 из самого внутреннего запроса, но опять же неясно, необходимо ли это или желательно.

Я не уверен, что это задание ищут, когда речь идет о рейтинге. Это подразумевает изменение порядка столбцов в зависимости от содержащихся в них значений. Ранжирование по штатам имело бы больше смысла, если бы данные были повернуты в другую сторону.

person Alex Poole    schedule 15.04.2016
comment
Большое спасибо за ваш ответ. И у данных выборки нет creditscore ‹500, но имеет смысл изменить диапазоны, как вы предложили. Я сделал попытку через DECODE. Постулирую с редактированием. Но я попрошу вас, можете ли вы предоставить решение, развернувшись в другую сторону, как было предложено. В этом больше смысла, но я не уверен, как к этому подойти. Спасибо - person Shankar Bansal; 17.04.2016