locked
Create groups of pairs RRS feed

  • Question

  • Hello,

    I need to create groups as follow:

    Groups = { 
      { (A, 20), (B, 10), (C, 40), (D, 60) },
      { (A, 15), (B, 12), (C, 35), (D, 55) }
    }

    Each item has a normalized type (A, B, C, ...) and an int value (20, 10, ..)

    create table dbo.Group (
      Id int identity not null
    )
    
    create table dbo.Type (
      Id int identity not null,
      Name nvarchar(2) not null
    )
    
    create table dbo.Rule (
      GroupId int not null,
      TypeId int not null,
      Value int not null
    )
    
    

    Does anyone suggest a better option?

    It feels strange to have a Group table with only one column.

    Thank You

    Tuesday, February 10, 2015 4:55 PM

Answers

  • So then it becomes:

    DECLARE @groups TABLE (groupID INT, type VARCHAR(1), midValue INT)
    INSERT INTO @groups (groupID, type, midValue) VALUES
    (1,'A',20),(1,'B',10),(1,'C',40),(1,'D',60),
    (2,'A',15),(2,'B',12),(2,'D',55),
    (3,'A',10),(3,'D',50),
    (4,'A',15),(4,'C',45),(4,'D',55)
    
    SELECT *, (.0+g.midValue/100.0)*90 , (.0+g.midValue/100.0)*110
      FROM @randomTable r
        INNER JOIN @groups g
    	  ON r.type = g.type
    	  AND g.groupID = ROUND(((5 - 1 -1) * RAND() + 1), 0)
    	  AND r.value between (.0+g.midValue/100.0)*90 AND (.0+g.midValue/100.0)*110

    The best way has the least number of moving parts, in this case, just one table.
    • Edited by Patrick Hurst Tuesday, February 10, 2015 8:09 PM
    • Proposed as answer by Eric__Zhang Wednesday, February 11, 2015 2:46 PM
    • Marked as answer by Eric__Zhang Tuesday, February 17, 2015 3:45 PM
    Tuesday, February 10, 2015 8:06 PM

All replies

  • Can you rephrase your question and explanation?

    Where do for example the values 15, 12 ,35 and 55 come from?

    Tuesday, February 10, 2015 5:00 PM
  • Where do for example the values 15, 12 ,35 and 55 come from?

    They are simply ints ... Basically each group is a distribution of weights (20, 10, ...) applied to each type (A, B, ...)

    So imagine I want to get a random distribution I pick one random group, for exemple:

        (A, 20), (B, 10), (C, 40), (D, 60) }

    Does this help?

    Tuesday, February 10, 2015 5:07 PM
  • Perhaps if you showed us an example of the end result of what you're trying to achieve?

    How are you going to use these groups with data?

    Tuesday, February 10, 2015 5:13 PM
  • Perhaps if you showed us an example of the end result of what you're trying to achieve?

    How are you going to use these groups with data?

    When I pick a group of data as follows:

       (A, 20), (B, 10), (C, 40), (D, 60) }

    I am going to a table with thousands of items and get one of Type A that has value between 18 and 22, one of type B that has value between 9 and 11 ...

    So first I pick a random group which gives me N types and a value for each type and then I go to another table to pick one item of each type that has a value on the range Given Value - 10% to Given Value + 10%

    Does this help?

    Tuesday, February 10, 2015 6:02 PM
  • I think I get what you want.

    DECLARE @randomTable TABLE (id INT IDENTITY, type VARCHAR(1),  value INT)
    DECLARE @i INT = 0, @x INT = 0, @rndFirstNameStr VARCHAR(1) = '', @rndInteger INT = 0
    SET NOCOUNT ON
    WHILE @i < 200
    BEGIN
     SET @x = ROUND(((1 - 1 -1) * RAND() + 1), 1)
     SET @rndFirstNameStr = ''
      WHILE @x < 20
      BEGIN
       SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((69 - 65 -1) * RAND() + 65), 0)) 
       SET @x = @x+1
      END
     SET @rndInteger = ROUND(((60 - 8 -1) * RAND() + 8), 0)
     INSERT INTO @randomTable (type, value)
     VALUES  (@rndFirstNameStr, @rndInteger)
     SET @i = @i + 1
    END
    
    SET NOCOUNT OFF
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    SELECT * 
      FROM @randomTable
     WHERE (type = 'A' AND value BETWEEN 18 AND 22)
        OR (type = 'B' AND value BETWEEN  9 AND 11)
        OR (type = 'C' AND value BETWEEN 36 AND 44)
        OR (type = 'D' AND value BETWEEN 54 AND 66)

    You want to do this, but with the values in a table, instead?

    DECLARE @groups TABLE (type VARCHAR(1), midValue INT)
    INSERT INTO @groups (type, midValue) VALUES
    ('A',20),('B',10),('C',40),('D',60)
    
    SELECT *, (.0+g.midValue/100.0)*90 , (.0+g.midValue/100.0)*110
      FROM @randomTable r
        INNER JOIN @groups g
    	  ON r.type = g.type
    	  AND r.value between (.0+g.midValue/100.0)*90 AND (.0+g.midValue/100.0)*110
    
    Looks like you just need one table to me.

    Tuesday, February 10, 2015 6:24 PM
  • Hello,

    No you didn't understand or maybe I am missing something.

    I need to have a table / tables to hold groups has follows:

    { (A, 20), (B, 10), (C, 40), (D, 60) }
    { (A, 15), (B, 12), (D, 55) } 
    { (A, 10), (D, 50) }
    { (A, 25), (C, 45), (D, 55) } 

    Each line is a group and it can have one or more pairs ...

    Not all groups have the same number of Type/Value pairs.

    Basically, this is just like "lookup table(s)" to hold this data.

    WHAT I DO WITH IT:

    I pick one group random and go to anther table to pick one record of each type as I described to you.

    But my question was: what is the best way to structure the groups data.

    Let me know if I missed something on your answer.

    Thank You,

    Miguel


    Tuesday, February 10, 2015 7:58 PM
  • So then it becomes:

    DECLARE @groups TABLE (groupID INT, type VARCHAR(1), midValue INT)
    INSERT INTO @groups (groupID, type, midValue) VALUES
    (1,'A',20),(1,'B',10),(1,'C',40),(1,'D',60),
    (2,'A',15),(2,'B',12),(2,'D',55),
    (3,'A',10),(3,'D',50),
    (4,'A',15),(4,'C',45),(4,'D',55)
    
    SELECT *, (.0+g.midValue/100.0)*90 , (.0+g.midValue/100.0)*110
      FROM @randomTable r
        INNER JOIN @groups g
    	  ON r.type = g.type
    	  AND g.groupID = ROUND(((5 - 1 -1) * RAND() + 1), 0)
    	  AND r.value between (.0+g.midValue/100.0)*90 AND (.0+g.midValue/100.0)*110

    The best way has the least number of moving parts, in this case, just one table.
    • Edited by Patrick Hurst Tuesday, February 10, 2015 8:09 PM
    • Proposed as answer by Eric__Zhang Wednesday, February 11, 2015 2:46 PM
    • Marked as answer by Eric__Zhang Tuesday, February 17, 2015 3:45 PM
    Tuesday, February 10, 2015 8:06 PM