Как я могу написать SQL в Oracle в моем случае?

Итак, вот таблицы -

create table person (
id number,
name varchar2(50)
);
create table injury_place (
id number,
place varchar2(50)
);
create table person_injuryPlace_map (
person_id number,
injury_id number
);
insert into person values (1, 'Adam');
insert into person values (2, 'Lohan');
insert into person values (3, 'Mary');
insert into person values (4, 'John');
insert into person values (5, 'Sam');
insert into injury_place values (1, 'kitchen');
insert into injury_place values (2, 'Washroom');
insert into injury_place values (3, 'Rooftop');
insert into injury_place values (4, 'Garden');
insert into person_injuryPlace_map values (1, 2);
insert into person_injuryPlace_map values (2, 3);
insert into person_injuryPlace_map values (1, 4);
insert into person_injuryPlace_map values (3, 2);
insert into person_injuryPlace_map values (4, 4);
insert into person_injuryPlace_map values (5, 2);
insert into person_injuryPlace_map values (1, 1);

Здесь таблица person_injuryPlace_map будет просто отображать обе другие таблицы.

Как я хотел показать данные -

Kitchen Pct Washroom Pct Rooftop Pct Garden Pct
-----------------------------------------------------------------------
1 14.29% 3 42.86% 1 14.29% 2 28.57%

Здесь значение столбца Kitchen, Washroom, Rooftop, Garden - это общие инциденты. В столбцах Pct будет отображаться процент от общего количества.

Как это сделать в Oracle SQL?

4 ответа

Вам нужно использовать стандартный PIVOT запрос.

В зависимости от вашей версии версии базы данных Oracle вы можете сделать это двумя способами:

Использование PIVOT для версии 11g и вверх:

SQL> SELECT *
 2 FROM
 3 (SELECT c.place place,
 4 row_number() OVER(PARTITION BY c.place ORDER BY NULL) cnt,
 5 (row_number() OVER(PARTITION BY c.place ORDER BY NULL)/
 6 COUNT(place) OVER(ORDER BY NULL))*100 pct
 7 FROM person_injuryPlace_map A
 8 JOIN person b
 9 ON(A.person_id = b.ID)
 10 JOIN injury_place c
 11 ON(A.injury_id = c.ID)
 12 ORDER BY c.place
 13 ) PIVOT (MAX(cnt),
 14 MAX(pct) pct
 15 FOR (place) IN ('kitchen' AS kitchen,
 16 'Washroom' AS Washroom,
 17 'Rooftop' AS Rooftop,
 18 'Garden' AS Garden));
 KITCHEN KITCHEN_PCT WASHROOM WASHROOM_PCT ROOFTOP ROOFTOP_PCT GARDEN GARDEN_PCT
---------- ----------- ---------- ------------ ---------- ----------- ---------- ----------
 1 14.2857143 3 42.8571429 1 14.2857143 2 28.5714286

С помощью MAX и DECODE для версии 10g и до:

SQL> SELECT MAX(DECODE(t.place,'kitchen',cnt)) Kitchen ,
 2 MAX(DECODE(t.place,'kitchen',pct)) Pct ,
 3 MAX(DECODE(t.place,'Washroom',cnt)) Washroom ,
 4 MAX(DECODE(t.place,'Washroom',pct)) Pct ,
 5 MAX(DECODE(t.place,'Rooftop',cnt)) Rooftop ,
 6 MAX(DECODE(t.place,'Rooftop',pct)) Pct ,
 7 MAX(DECODE(t.place,'Garden',cnt)) Garden ,
 8 MAX(DECODE(t.place,'Garden',pct)) Pct
 9 FROM
 10 (SELECT b.ID bid,
 11 b.NAME NAME,
 12 c.ID cid,
 13 c.place place,
 14 row_number() OVER(PARTITION BY c.place ORDER BY NULL) cnt,
 15 ROUND((row_number() OVER(PARTITION BY c.place ORDER BY NULL)/
 16 COUNT(place) OVER(ORDER BY NULL))*100, 2) pct
 17 FROM person_injuryPlace_map A
 18 JOIN person b
 19 ON(A.person_id = b.ID)
 20 JOIN injury_place c
 21 ON(A.injury_id = c.ID)
 22 ORDER BY c.place
 23 ) t;
 KITCHEN PCT WASHROOM PCT ROOFTOP PCT GARDEN PCT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 1 14.29 3 42.86 1 14.29 2 28.57


Если вы используете Oracle 11g или выше, вы можете использовать функцию pivot для вашего требуемого вывода.

SELECT *
FROM (
 SELECT id
 ,place
 ,round((
 cnt / sum(cnt) OVER (
 ORDER BY NULL
 )
 ) * 100, 2) AS pct
 FROM (
 SELECT a.id
 ,a.place
 ,count(a.id) AS cnt
 FROM injury_place a
 JOIN person_injuryPlace_map b ON a.id = b.injury_id
 GROUP BY a.id
 ,a.place
 )
 )
pivot(max(id) , max(pct) pct FOR place IN (
 'kitchen' AS kitchen
 ,'Washroom' Washroom
 ,'Rooftop' Rooftop
 ,'Garden' Garden
 ))


Вам нужно сначала взять счетчик и pct в подзапрос, затем использовать функцию max + decode, чтобы суммировать оба из них в требуемом порядке Проверьте, помогает ли ниже запрос:

SELECT Max(Decode(i.place,'Kitchen',cnt)) AS "Kitchecn"
 , Max(Decode(i.place,'Kitchen',pct)) AS "Pct"
 , Max(Decode(i.place,'Washroom',cnt)) AS "Washroom"
 , Max(Decode(i.place,'Washroom',pct)) AS "Pct"
 , Max(Decode(i.place,'Rooftop',cnt)) AS "Rooftop"
 , Max(Decode(i.place,'Rooftop',pct)) AS "Pct"
 , Max(Decode(i.place,'Garden',cnt)) AS "Garden"
 , Max(Decode(i.place,'Garden',pct)) AS "Pct"
 FROM (SELECT i.place
 , Count(pim.injury_id) AS cnt
 , (Count(pim.injury_id)*100/(SELECT Count(*) FROM person_injuryPlace_map)) AS pct 
 FROM person_injuryPlace_map pim
 INNER JOIN injury_place i ON i.id = pim.injury_id
 GROUP BY i.place)


Я сделал следующее:

select a."kitchen"
, round((100/"Total")*a."kitchen") "Pct"
, a."Garden"
, round((100/"Total")*a."Garden") "Pct"
, a."Washroom"
, round((100/"Total")*a."Washroom") "Pct"
, a."Rooftop"
, round((100/"Total")*a."Rooftop") "Pct"
from 
(
select 
sum(decode(ip.place, 'kitchen', 1, 0)) "kitchen"
, sum(decode(ip.place, 'Garden', 1, 0)) "Garden"
, sum(decode(ip.place, 'Washroom', 1, 0)) "Washroom"
, sum(decode(ip.place, 'Rooftop', 1, 0)) "Rooftop"
, sum(decode(ip.place, 'kitchen', 1, 0))
+ sum(decode(ip.place, 'Garden', 1, 0))
+ sum(decode(ip.place, 'Washroom', 1, 0)) 
+ sum(decode(ip.place, 'Rooftop', 1, 0)) "Total"
from 
person p
join person_injuryPlace_map pim on pim.person_id = p.id
join injury_place ip on ip.id = pim.injury_id
) a

licensed under cc by-sa 3.0 with attribution.