### Thread: Decisions on dataset/table

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2012
Posts
2
Rep Power
0

#### Decisions on dataset/table

I've got a problem to solve and I don't know how to get this done in SQL.

Let me explain the problem first. I start with a dataset/table. I got like 150+ decisions (if-else statements) to make on it so I can ultimately get to a calculation of the productgroup for each record in the dataset.

For example I could do it like this which gets me the result I want just takes a hell of a lot of time. I however need the productgroup much faster

WITH B1 AS (
SELECT * FROM BASE
),
B2 AS (
SELECT * FROM B1
WHERE DECISION = 0
),
B3 AS (
SELECT * FROM B2
WHERE DECISION = 0
),
B4 AS (
SELECT * FROM B1
WHERE DECISION = 1
),
B5 AS (
SELECT * FROM B4
WHERE DECISION = 0
)

So each result of a decision might be the input for another decision. Now I see a possibility in where I often find myself asking up the same query just some different if else statement. So if I could somehow remember some query, so it doesn't need to get it multiple times, I would already save a lot of time. A stored procedure might be a way to go, but I don't yet grasp how to do that in this decision.

Basically its like going through a decision tree to get to the outcome you need. Any help with a possible example would be hugely helpfull.
2. What exactly you are trying to achieve by this SQL ?
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2012
Posts
2
Rep Power
0
I got it down to 1 second which is acceptable though I still wish to elaborate a bit more so I can learn if this is the right way to do it or if there is another way (I read somewhere about Decision Tree Technique when Data Mining, but don't understand it):

Pre-Steps:
I got several tables (e.g. about customers)
I've got all sorts of data like eg sale figures (eg bought an iphone, htc phone on date),birthdate etc. and all sorts of other numeric values about the customer. Which I need to do calculations (eg. Count(spendings) to get total spendings between date and date for that customer) on to use in the if-statements

Now I got a decision tree structure(basically consisting of 173 if-else statements) the if statements are always a boolean value. So it's either true or false. Depending if its true or false we go to a following if statement eg.
(If if-statement 1 is true then go to if-statement 2 else go to if-statement 12)

Depending on the route it takes a certain value is added to a variable that always starts on 0.

My solution atm:
Step 1:
The way I solved it atm is that I created a view with all the values I need to check on in the if-statements. It's a pretty big view table with 30 columns of which 29 are calculated values. Only the customerid is not a calculated value.

Simple Example (my queries need a bit more tables and joins, but I hope you'll get the picture):
SELECT C.c_id,TS.total_spendings,TR_total_returns,A.age
FROM customer C
LEFT JOIN
(SELECT c_id,COUNT(spendings) AS total_spendings
FROM sales
GROUP BY TS.c_id
)TS ON TS.c_id = C.c_id
LEFT JOIN
etc.......

Step 2:
Then I made a function like this

Example:

CREATE OR REPLACE calc_group(cid integer,total_spendings bigint, total_returns bigint, age integer) RETURN \$\$

DECLARE
Route integer;
Val integer;
BEGIN

Route := 1;
Group := 0;

WHILE Route <> 0 LOOP
IF Route = 1 THEN
IF total_spendings > 200 THEN
Route := 2;
Group := Group + 1;
ELSE
Route := 3;
Group := Group + 100;
END IF
END IF
IF Route = 2 THEN
IF total_spendings > 500 THEN
Route := 3;
Group := Group + 20;
ELSE
Route := 3;
Group := Group + 100;
END IF
END IF
IF Route = 3 THEN
IF total_spendings > 1000 THEN
Route := 0;
Group := Group + 1000;
ELSE
Route := 0;
Group := Group + 10;
END IF
END IF

etc..... more ifs

END LOOP

RETURN group;
END;
Step 3:
Created a second view to call the customer id and function to get the group the customer belongs too.

example:
SELECT c_id,calc_group(total_spendings,total_returns,age) from view