#1
  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. #2
  3. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    120
    What exactly you are trying to achieve by this SQL ?
  4. #3
  5. 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

IMN logo majestic logo threadwatch logo seochat tools logo