5

I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the true population.

Say, I have 3 records in my table. sampn ids a unique record and weight is the frequency weight. The un-expanded dataset looks like this:

sampn   weight  attrib1 attrib2 attrib3
    1       2       23      32      65
    2       1       32      56      75
    3       3       54      25      87

Once expanded, the dataset will be like this (note - I removed the weight field - but this is not essential):

sampn   attrib1 attrib2 attrib3
    1       23      32      65
    1       23      32      65
    2       32      56      75
    3       54      25      87
    3       54      25      87
    3       54      25      87

I have tried to do this using cursors but it is taking a really long time to execute. Is there a clever way to do this really fast? Any predefined T-SQL stored procedure that achieves this?

Update
All, Thanks for the answers! Really great learning experience! Performed the expansion operation on my dataset. Paul's auxiliary table of numbers had the best execution time.

  • Do you actually care about having a result set like that, or are you expanding it in order to do something else, like compute an aggregate of the data? – Jon Seigel Jul 25 '13 at 2:42
  • The purpose is to create the population set for use in micro-simulation. Once I get the table in this form I will start assigning unique ids for each record, perform some additional operations before feeding it into the micro-simulation framework. – sriramn Jul 25 '13 at 3:58
  • So there's some language here that confuses me because I'm not a statistician and it took me a while to figure it out. Just to help out anyone that might be looking for the same thing. By "weight is the 'frequency weight' " OP means the count of that combination of attributes. These answers are great! – Dan Apr 23 at 11:50
9

An efficient way to perform this task uses an auxiliary table of numbers. This is simply a table with integers from 1 to n, where 'n' is perhaps a million or so. A numbers table comes in handy for all sorts of regular tasks.

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1, 
    Attribute2, 
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);

SELECT
    D.SampleNumber,
    D.Attribute1,
    D.Attribute2,
    D.Attribute3
FROM dbo.Demo AS D
JOIN dbo.Numbers AS N
    ON N.n BETWEEN 1 AND D.SampleWeight;

DROP TABLE dbo.Demo;

Execution plan:

Plan

Output:

╔══════════════╦════════════╦════════════╦════════════╗
║ SampleNumber ║ Attribute1 ║ Attribute2 ║ Attribute3 ║
╠══════════════╬════════════╬════════════╬════════════╣
║            1 ║         23 ║         32 ║         65 ║
║            1 ║         23 ║         32 ║         65 ║
║            2 ║         32 ║         56 ║         75 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
╚══════════════╩════════════╩════════════╩════════════╝

SQLfiddle here.

| improve this answer | |
5

You can use CTE to do it.

here is a sql fiddle for it : http://sqlfiddle.com/#!3/0b172/8

create table t(sampn int, weight   int, attrib1 int, attrib2 int, attrib3 int);

insert into t values    (1 ,      2 ,      23 ,     32   ,   65)
 insert into t values   (2  ,     1  ,     32  ,    56    ,  75)
  insert into t values  (3   ,    3   ,    54   ,   25     , 87)

-- solution using cte.

;WITH cte (
    sampn
    ,[weight]
    ,attrib1
    ,attrib2
    ,attrib3
    ,RepeatedTime
    )
AS (
    SELECT sampn
        ,[weight]
        ,attrib1
        ,attrib2
        ,attrib3
        ,1
    FROM t
    WHERE [weight] <> 0 -- this will take care if the weight is ZERO

    UNION ALL

    SELECT sampn
        ,[weight]
        ,attrib1
        ,attrib2
        ,attrib3
        ,RepeatedTime + 1
    FROM cte a
    WHERE a.[weight] > a.RepeatedTime
    )
SELECT sampn
    ,attrib1
    ,attrib2
    ,attrib3
FROM cte  
ORDER BY sampn
OPTION (MAXRECURSION 100) -- change this if you have more recursions
| improve this answer | |
  • Here the MAXRECURSION() is equivalent to looping as many times as weight? Can you please point me to a good resource that explains how CTE works? – sriramn Jul 25 '13 at 17:38
  • @RazorXsr check the link that is embedded in CTE in my answer. I think performance wise, Paul's answer is what you want. I did not think about number table when answering. – Kin Shah Jul 25 '13 at 18:28
5

I agree with Paul that the numbers table is probably the best solution that you are going to come up with in terms of manageability and performance. With that said, you could approach this problem from a different angle, using XML.

This solution is fairly easy to understand. Performance on a small number of rows should be fine, but for a large number of rows, you will have to test.

Update: I have added the exact code that I am using that is working, in my test environment.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.Demo') IS NOT NULL
BEGIN
    DROP TABLE dbo.Demo;
END
GO

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1, 
    Attribute2, 
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);
GO

SELECT 
    y.SampleNumber,
    Att.vals.value('@Attribute1','int'),
    Att.vals.value('@Attribute2','int'),
    Att.vals.value('@Attribute3','int')
FROM(
    SELECT 
        SampleNumber,
        Attribute=(SELECT CAST(REPLICATE(CAST(Attribute.Data AS VARCHAR(MAX)),SampleWeight) AS XML))
    FROM dbo.Demo
    CROSS APPLY(   
        SELECT 
            [@Attribute1] = Attribute1,
            [@Attribute2] = Attribute2,
            [@Attribute3] = Attribute3
        FOR XML PATH('data'),ROOT('root'), TYPE
    ) AS Attribute (Data)
) AS y
CROSS APPLY y.Attribute.nodes('/root/data') as Att(vals)
| improve this answer | |
  • An interesting way to go about solving the problem. Will look into it and report the performance stats. – sriramn Jul 25 '13 at 14:59
  • The XML approach does not seem to be working - I am getting a blank table. Should I have something setup before I run your code sample apart from dbo.Demo? – sriramn Jul 25 '13 at 17:40
  • Are you using the sample data provided by Paul or your table? It should like the xml nodes method is not returning any rows. are your XML elements the same as the ones in the above code "/root/data". Keep in mind that XML IS case sensitive, so it has to match. – Adam Haines Jul 25 '13 at 18:49
  • I am using Paul's table... I appended your code below Paul's table definition for Demo. That should work right? – sriramn Jul 25 '13 at 18:55
  • 1
    Right, that should most definitely work. In fact that is what I did to test and come up with the solution. Maybe try to copy the code down again? Perhaps there is something that didn't copy right the first time? – Adam Haines Jul 25 '13 at 18:58
2

In SQL Server 2016 or up a concise way of doing this is

SELECT D.*
FROM dbo.Demo D
CROSS APPLY STRING_SPLIT(SPACE(D.SampleWeight - 1), ' ')

enter image description here

The above assumes that D.SampleWeight will never be greater than 8,001. It could be adapted to cope with larger numbers but I will leave that as an exercise for the reader.

I imagine the performance characteristics of this will likely be worse than using a numbers table but it can fulfil a quick and dirty adhoc requirement.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.