I am trying to create multiple calculated fields off of a dataset. These calculations generally run the basic formula of
- check if the room name is in a list
- if yes: divide the square footage by a productivity rate
- if no: null
As a more concrete example, the calculation for vacuuming an office looks like
CASE WHEN (room_name = 'OFFICE' OR room_name = 'CUBICLE' OR room_name = 'RECEPTION')
THEN usable_sq_ft / 52
ELSE null
END Vacuum
My problem is that there are many more room names that fall under office and many more tasks that need to be calculated and I don't want to type out that Boolean logic for each one. EDIT FOR CLARITY: In python I could just do something like
offices = ['office', 'cubicle', 'reception']
entrances = ['lobby', 'foyer', 'vestibule']
exercise = ['gym', 'weight_room']
if room_name in offices:
<calculation>
else:
null
Is there any way to write a list and test if a room name is in it in Oracle SQL?
CodePudding user response：
You can use a collection data type:
CREATE TYPE string_list IS TABLE OF VARCHAR2(30);
Then you can use the MEMBER OF operator:
CASE
WHEN room_name MEMBER OF string_list('OFFICE', 'CUBICLE', 'RECEPTION')
THEN usable_sq_ft / 52
END AS Vacuum
Or you can use IN with a table collection expression:
CASE
WHEN room_name IN (
SELECT COLUMN_VALUE FROM TABLE(string_list('OFFICE', 'CUBICLE', 'RECEPTION'))
)
THEN usable_sq_ft / 52
END AS Vacuum
Or you can use IN with a sub-query:
CASE
WHEN room_name IN (
SELECT 'OFFICE' FROM DUAL UNION ALL
SELECT 'CUBICLE' FROM DUAL UNION ALL
SELECT 'RECEPTION' FROM DUAL
)
THEN usable_sq_ft / 52
END AS Vacuum
Or you can create another table:
CREATE TABLE room_types (
room VARCHAR2(20),
type VARCHAR2(20),
vacuum_factor NUMBER
);
INSERT INTO room_types (room, type, vacuum_factor)
SELECT 'office', 'offices' 52 FROM DUAL UNION ALL
SELECT 'cubicle', 'offices' 52 FROM DUAL UNION ALL
SELECT 'reception', 'offices' 52 FROM DUAL UNION ALL
SELECT 'lobby', 'entrances' 10 FROM DUAL UNION ALL
SELECT 'foyer', 'entrances' 10 FROM DUAL UNION ALL
SELECT 'vestibule' 'entrances' 10 FROM DUAL UNION ALL
SELECT 'gym', 'exercise' 42 FROM DUAL UNION ALL
SELECT 'weight_room', 'exercise' 42 FROM DUAL;
Then use a sub-query:
usable_sq_ft / (SELECT vacuum_factor
FROM room_types
WHERE room = room_name
AND type = 'office') AS Vacuum
CodePudding user response：
The first cte (room_names) containes sample data as I understood them from your question (just a guess). You can then define your "lists" also as cte-s (room_classes) and (room_actions). Note that this is just a simulation of your python list related thingy. I also suggest you to create those two cte-s as individual values (not lists of values) so you could join them to your room_names table using IN function. Here join is created using INSTR function which is good only if all the values are complete and distinctive like in the code below. I don't know what your real data look like. In this code I added a few actions too - so you can get different possibilities to calculate the value from your question. Actions are defined list-like too and the note as well as the suggestion above should be cosidered in the same way. The code below gives you all the combinations of room classification and room actions as a result which can be selected, filtered and ordered as you wish.
WITH
room_names AS
(
Select 1 "ROOM_ID", 'OFFICE' "ROOM_NAME", 120 "USABLE_SQ_FT" From Dual UNION ALL
Select 2 "ROOM_ID", 'CUBICLE' "ROOM_NAME", 60 "USABLE_SQ_FT" From Dual UNION ALL
Select 3 "ROOM_ID", 'RECEPTION' "ROOM_NAME", 350 "USABLE_SQ_FT" From Dual UNION ALL
Select 4 "ROOM_ID", 'LOBBY' "ROOM_NAME", 890 "USABLE_SQ_FT" From Dual UNION ALL
Select 5 "ROOM_ID", 'FOYER' "ROOM_NAME", 650 "USABLE_SQ_FT" From Dual UNION ALL
Select 6 "ROOM_ID", 'VESTIBULE' "ROOM_NAME", 480 "USABLE_SQ_FT" From Dual UNION ALL
Select 7 "ROOM_ID", 'GYM' "ROOM_NAME", 995 "USABLE_SQ_FT" From Dual UNION ALL
Select 8 "ROOM_ID", 'WEIGHT_ROOM' "ROOM_NAME", 764 "USABLE_SQ_FT" From Dual
),
room_classes AS
(
Select 'Offices' "ROOM_CLASS", 'OFFICE, CUBICLE, RECEPTION' "MEMBERS", 'VACUUMING' "ACTIONS" From Dual UNION ALL
Select 'Entrances' "ROOM_CLASS", 'LOBBY, FOYER, VESTIBULE' "MEMBERS", 'VACUUMING, SHINING' "ACTIONS" From Dual UNION ALL
Select 'Excercises' "ROOM_CLASS", 'GYM, WEIGHT_ROOM' "MEMBERS", 'VACUUMING, WASHING, SHINING' "ACTIONS" From Dual
),
room_actions AS
(
Select 'VACUUMING' "ROOM_ACTION", 52 "PRODUCTIVITY_RATE" From Dual UNION ALL
Select 'SHINING' "ROOM_ACTION", 76 "PRODUCTIVITY_RATE" From Dual UNION ALL
Select 'WASHING' "ROOM_ACTION", 62 "PRODUCTIVITY_RATE" From Dual
)
SELECT
rc.ROOM_CLASS "ROOM_CLASS",
rn.ROOM_ID "ROOM_ID",
rn.ROOM_NAME "ROOM_NAME",
rn.USABLE_SQ_FT "USABLE_SQ_FT",
ra.ROOM_ACTION "ACTION",
ra.PRODUCTIVITY_RATE "PRODUCTIVITY_RATE",
CASE WHEN Nvl(ra.PRODUCTIVITY_RATE, 0) <> 0 THEN Round(rn.USABLE_SQ_FT / ra.PRODUCTIVITY_RATE, 2) ELSE 0 END "CALCULATED_VALUE"
FROM
room_names rn
LEFT JOIN
room_classes rc ON(INSTR(rc.MEMBERS, rn.ROOM_NAME) > 0)
LEFT JOIN
room_actions ra ON(INSTR(rc.ACTIONS, ra.ROOM_ACTION) > 0)
ORDER BY
rn.ROOM_ID
--
-- R e s u l t
--
-- ROOM_CLASS ROOM_ID ROOM_NAME USABLE_SQ_FT ACTION PRODUCTIVITY_RATE CALCULATED_VALUE
-- ---------- ---------- ----------- ------------ --------- ----------------- ----------------
-- Offices 1 OFFICE 120 VACUUMING 52 2.31
-- Offices 2 CUBICLE 60 VACUUMING 52 1.15
-- Offices 3 RECEPTION 350 VACUUMING 52 6.73
-- Entrances 4 LOBBY 890 VACUUMING 52 17.12
-- Entrances 4 LOBBY 890 SHINING 76 11.71
-- Entrances 5 FOYER 650 VACUUMING 52 12.5
-- Entrances 5 FOYER 650 SHINING 76 8.55
-- Entrances 6 VESTIBULE 480 VACUUMING 52 9.23
-- Entrances 6 VESTIBULE 480 SHINING 76 6.32
-- Excercises 7 GYM 995 VACUUMING 52 19.13
-- Excercises 7 GYM 995 SHINING 76 13.09
-- Excercises 7 GYM 995 WASHING 62 16.05
-- Excercises 8 WEIGHT_ROOM 764 VACUUMING 52 14.69
-- Excercises 8 WEIGHT_ROOM 764 SHINING 76 10.05
-- Excercises 8 WEIGHT_ROOM 764 WASHING 62 12.32